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
- Find a sequence
- Find a sequence of values – wildcard search
- Repeat values
- What is the length of longest winning streak?
Sequence category
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, […]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]
Excel categories
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.
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