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:

=MAX(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$25, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)) ,""))+1)

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:

=INDEX($A$2:$A$25, SMALL(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""), MATCH(MAX(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1), FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1, 0)))

I wish I could present a smaller formula.

Length of shortest consecutive sequence of a value

Array formula in cell F3:

=MIN(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$25, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)) ,""))+1)

Value of the shortest consecutive sequence

Array formula in cell F4:

=INDEX($A$2:$A$25, SMALL(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""), MATCH(MIN(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1), FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1, 0)))

Length of second longest consecutive sequence of a value

Array formula in cell D7:

=LARGE(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$25, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1, 2)

Value of the second longest consecutive sequence

Array formula in cell D8:

=INDEX($A$2:$A$25, SMALL(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""), MATCH(LARGE(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1, 2), FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1, 0)))

If you are interested in learning and constructing array formulas, check out my Advanced excel course.

Download *.xlsx file

Longest consecutive sequence.xlsx

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 ignore error values using the SMALL function

The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]

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

Repeat values across cells

This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

Create number series

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

Comments (6)

6 Responses to “Find the longest/shortest consecutive sequence of a value”

Chris Macro says:

November 11, 2014 at 10:44 pm

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

Reply
- Oscar says:
  
  November 12, 2014 at 10:20 am
  
  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.
Oscar says:

March 11, 2015 at 10:45 am

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

Reply
Rogério says:

May 23, 2016 at 2:01 am

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.

Reply
Rogério says:

May 23, 2016 at 2:06 am

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 .

Reply
Marlies says:

March 29, 2020 at 5:23 pm

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?

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.

Contact Oscar

You can contact me through this contact form