# Extract the most repeated adjacent values in a column

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

#### Table of Contents

## 1. Length of the most repeated grouped value

The array formula in cell D3 calculates how many cells contain the same given value repeated. It returns four in cell D3 because value "B" is repeated four times and is adjacent to each other in cell range A11:A14.

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

**Update 3-18-2021**, Excel 365 formula:

### Explaining formula in cell D3

#### Step 1 - Compare values in cell ranges

The less than and the greater than characters combined checks if a value is not equal to another value, it returns TRUE if not equal and FALSE if equal.

The second cell range is offset by 1, this makes the comparison check the following value which makes it easy to calculate the number of repeated values in a column.

$A$2:$A$25<>$A$3:$A$26

becomes

({"B"; "B"; "C"; "C"; "B"; "B"; "C"; "B"; "C"; "B"; "B"; "B"; "B"; "C"; "B"; "A"; "C"; "A"; "C"; "C"; "A"; "C"; "C"; "C"}<>{"B"; "C"; "C"; "B"; "B"; "C"; "B"; "C"; "B"; "B"; "B"; "B"; "C"; "B"; "A"; "C"; "A"; "C"; "C"; "A"; "C"; "C"; "C"; 0}

and returns

{FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}

#### Step 2 - Replace TRUE with corresponding row number

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)))

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)))

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, "", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24})

and returns

{1; ""; 3; ""; 5; ""; ""; ""; ""; 10; 11; 12; ""; ""; ""; ""; ""; ""; 19; ""; ""; 22; 23; ""}

#### Step 3 - Calculate frequency

The FREQUENCY function calculates how often values occur within a range of values and returns a vertical array of numbers. It returns an array that is one more item larger than the bins_array.

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)) , ""))

becomes

FREQUENCY({1; ""; 3; ""; 5; ""; ""; ""; ""; 10; 11; 12; ""; ""; ""; ""; ""; ""; 19; ""; ""; 22; 23; ""}, {""; 2; ""; 4; ""; 6; 7; 8; 9; ""; ""; ""; 13; 14; 15; 16; 17; 18; ""; 20; 21; ""; ""; 24})

and returns

{1; 1; 1; 0; 0; 0; 3; 0; 0; 0; 0; 0; 1; 0; 2; 0}

#### Step 4 - Calculate max number in the array

The MAX function returns the largest number in a cell range or array.

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)

becomes

MAX({1; 1; 1; 0; 0; 0; 3; 0; 0; 0; 0; 0; 1; 0; 2; 0}+1)

becomes

MAX({2; 2; 2; 1; 1; 1; 4; 1; 1; 1; 1; 1; 2; 1; 3; 1})

and returns 4.

## 2. Extract most repeated grouped value

The image above demonstrates a formula in cell D4 that extracts the most repeated adjacent value in column A.

Array formula in cell D4:

**Update 3-18-2021**, Excel 365 formula in cell D4:

### Explaining formula in cell D4

#### Step 1 - Compare cell ranges and return corresponding row number if TRUE

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), "")

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), "")

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24}, "")

and returns

{""; 2; ""; 4; ""; 6; 7; 8; 9; ""; ""; ""; 13; 14; 15; 16; 17; 18; ""; 20; 21; ""; ""; 24}

#### Step 2 - Compare cell ranges and return corresponding row number if FALSE

IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)))

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)))

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, "", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24})

and returns

{1; ""; 3; ""; 5; ""; ""; ""; ""; 10; 11; 12; ""; ""; ""; ""; ""; ""; 19; ""; ""; 22; 23; ""}

#### Step 4 - Calculate the frequency of repeated values in column A

The FREQUENCY function calculates how often values occur within a range of values and returns a vertical array of numbers. It returns an array that is one more item larger than the bins_array.

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

becomes

FREQUENCY({1; ""; 3; ""; 5; ""; ""; ""; ""; 10; 11; 12; ""; ""; ""; ""; ""; ""; 19; ""; ""; 22; 23; ""}, {""; 2; ""; 4; ""; 6; 7; 8; 9; ""; ""; ""; 13; 14; 15; 16; 17; 18; ""; 20; 21; ""; ""; 24})+1

becomes

{1; 1; 1; 0; 0; 0; 3; 0; 0; 0; 0; 0; 1; 0; 2; 0} + 1

and returns

{2; 2; 2; 1; 1; 1; 4; 1; 1; 1; 1; 1; 2; 1; 3; 1}

#### Step 5 - Calculate largest value in array

The MAX function returns the largest number in a cell range or array.

MAX(FREQUENCY(y, x)+1)

becomes

MAX({2; 2; 2; 1; 1; 1; 4; 1; 1; 1; 1; 1; 2; 1; 3; 1})

and returns 4.

#### Step 6 - Find the position in array

The MATCH function returns a number representing the relative position af a given value in a cell range or array.

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)

becomes

MATCH(4, {2; 2; 2; 1; 1; 1; 4; 1; 1; 1; 1; 1; 2; 1; 3; 1}, 0)

and returns 7.

#### Step 7 - Calculate k-th smallest value

The SMALL function returns the k-th smallest number from a cell range or array.

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))

becomes

SMALL({""; 2; ""; 4; ""; 6; 7; 8; 9; ""; ""; ""; 13; 14; 15; 16; 17; 18; ""; 20; 21; ""; ""; 24}, 7)

and returns 13.

#### Step 8 - Return value

The INDEX function returns a value from a given cell range or array based on a row and column number.

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)))

becomes

INDEX($A$2:$A$25, 13)

and returns "B" in cell D4.

## 3. Length of the shortest consecutive sequence of a value

Array formula in cell F3:

## 4. Value of the shortest consecutive sequence

Array formula in cell F4:

## 5. Length of second longest consecutive sequence of a value

Array formula in cell D7:

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

### Read more

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 most/least consecutive repeated 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 […]

### 8 Responses to “Extract the most repeated adjacent values in a column”

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

**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)

Get 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?

do u have version for google sheets

Thanks for this. It works. However, what do I do if I have blank cells in my array, that I do not wish to be counted?

To use your example: Imagine cells A11:A14 did not contain "B" in them, but rather were left blank. Then your formula would still say the longest consecutive sequence is 4, with a value "" (blank).

Obviously that is not a desired outcome. In this case the longest consecutive sequence would be A23:A25. A sequence of 3 Cs.

Can you please suggest a solution?

Thank you in advance!

Kind regards,

Deyan