## 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.

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

Convert array formula to a regular formula

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

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 […]

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 […]

Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]

Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right […]

Excel has a great built-in versatile tool for creating number sequences. In some situations, however, you need to rely on […]

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 to Chris Macro

### 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.

**Contact Oscar**

You can contact me through this contact form

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 .