Author: Oscar Cronquist Article last 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.

Find the longest consecutive sequence of a value

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

  1. Select cell
  2. Copy/Paste the formula to the formula bar
  3. Press and hold CTRL + SHIFT simultaneously
  4. Press Enter once
  5. 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

Read more

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.

Related articles