Count Number of Consecutive Occurrence of values in Google sheets-CodePudding

This is the original data, all the data are of two kinds: red and black. And then, I want to study the occurrence of all the blocks. The result will be like this:

It means the first streak of red(from index 1 to 3) has a length of 3, and the second streak which is black(from index 4 to 5) has a length of 2...

I want to find out an elegant way to calculate it but in sheets, it's very hard. COUNTIF and ROWS all can't perfectly resolve this problem.

Do you have an elegant way?

CodePudding user response：

Not sure it's elegant, but you could add two helper columns, the first column checks if the record has changed, and the second counts until the next change using a MATCH. Note you'd need an extra "TRUE" below the last record to catch the last streak. Then you can use FILTER to show the blocks and occurances.

CodePudding user response：

try:

=ARRAYFORMULA(QUERY(REGEXREPLACE(QUERY({TEXT(
 VLOOKUP(ROW(B2:B20), QUERY((B2:B20<>B1:B19)*ROW(B2:B20), 
 "where Col1 <>0"), 1, 1), "000000")&"×"&B2:B20}, 
 "select Col1,count(Col1) group by Col1 label count(Col1)''")&"", "(. ×)", ), 
 "where Col1 is not null"))