## Find the longest/shortest consecutive sequence of a value

*Article updated on January 20, 2018*

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.

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]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 […]### 5 Responses to “Find the longest/shortest consecutive sequence of a value”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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 .