Find the longest/shortest consecutive sequence of a value
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell D4 returns the value of the longest consecutive sequence in column A.
Cell F3 returns the the length of the shortest consecutive sequence of a value in column A and cell F4 returns the value of the smallest consecutive sequence in column A.
Length of longest consecutive sequence of a value
Array formula in cell D3:
How to enter an array formula
- Select cell
- Copy/Paste the formula to the formula bar
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys
Value of the longest consecutive sequence
Array formula in cell D4:
I wish I could present a smaller formula.
Length of shortest consecutive sequence of a value
Array formula in cell F3:
Value of the shortest consecutive sequence
Array formula in cell F4:
Length of second longest consecutive sequence of a value
Array formula in cell D7:
Value of the second longest consecutive sequence
Array formula in cell D8:
If you are interested in learning and constructing array formulas, check out my Advanced excel course.
Download *.xlsx file
Longest consecutive sequence.xlsx
Read more
- Find a sequence
- Find a sequence of values – wildcard search
- Repeat values
- What is the length of longest winning streak?
Functions in this post
FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
SMALL(array,k)
Returns the k-th smallest number in this data set.
ROW(reference)
Returns the rownumber of a reference.
MATCH(lookup_value, lookup_array, [match_type]
Returns the relative position of an item in an array that matches a specified value.
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
How to use the AGGREGATE function
The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature […]
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
How to count unique distinct values based on a date
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
Find the longest/smallest consecutive sequence of a value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]
6 Responses to “Find the longest/shortest consecutive sequence of a value”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
This reminded me of a article I wrote a while back, only I took the perspective of using this methodology to calculate win/loss streaks for a sports team. There's a lot of cool stuff you can do with this consecutive sequence formula!
https://www.thespreadsheetguru.com/blog/2014/6/29/formulas-to-calculate-longest-current-win-streaks?rq=streak
Chris Macro,
Yes, there is a lot of cool stuff you can do I only wish I could come up with shorter formulas.
The FREQUENCY function really saved me, I thought quite a while before figuring out how to solve this problem.
Thanks for sharing your post.
I got an email from David, his formulas are a lot smaller.
Formula in D4:
=INDEX($A$2:$A$25,AGGREGATE(14, 6, ROW($A$3:$A$26)-2/(FREQUENCY(IF($A$2:$A$25=$A$3:$A$26, ROW($A$2:$A$25)), ($A$2:$A$25<>$A$3:$A$26)*ROW($A$2:$A$25))=(D3-1)), 1), 0)
Download his workbook:
https://www.get-digital-help.com/wp-content/uploads/2014/11/longest-consecutive-sequence-1.xlsx
I have this sequence of C2: O2,
2 1 2 X X X 2 X 1 X 2 X 2
I need you to return the largest consecutive sequence:
X should be 3
2 should be 2
1 should be 1
x and 2 should be 4.
Another example:
X 2 X 1 1 X 1 1 X 1 1 1 2
1 X must be
2 must be 1
1 must be 5
x and 2 should be 2.
Tenho esta sequencia de C2:O2,
2 X X X 1 2 2 X 1 2 X X 2
Preciso que retorne a maior sequencia consecutiva de:
X deve ser 3
2 deve ser 2
1 deve ser 1
x e 2 deve ser 4 .
Outro exemplo:
X 2 1 X 1 X 1 1 1 1 1 2 X
X deve ser 1
2 deve ser 1
1 deve ser 5
x e 2 deve ser 2 .
Thank you for all your information, I appreciate it. If I'm copying the formula of D3 into Google Sheets, it's not working. It gives the number 2, while using the same values in column A as you. The formula of D4 turns in the right value (B). Do you know if the formula needs some change to work in google?