2

enter image description here

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:

enter image description here

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?

player0
  • 124,011
  • 12
  • 67
  • 124
zzzgoo
  • 1,974
  • 2
  • 17
  • 34
  • Do NOT share [spreadsheets](//meta.stackoverflow.com/a/260455)/[images](//meta.stackoverflow.com/q/285551) as the only source of data. Make sure to add input and expected output as **plain text table** to the question. [Click here](//webapps.stackexchange.com/a/161855) to create a table easily. Adding such tables makes **it is easier to copy/paste**. Your question may be closed, if it isn't self contained. Your table should be a minimal example.[Your email address can also be accessed by the public](//meta.stackoverflow.com/q/394304), if you share Google files. – TheMaster Oct 31 '22 at 05:21

2 Answers2

4

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    thank you very much, I have never heard of query language in sheets before. It's amazing, I learned SQL 10 years ago, now I can use it in sheets. – zzzgoo Mar 08 '22 at 14:28
  • I'm trying to use this formula but I get an #ERROR. I can't really tell what is wrong. I tried just subsituting the cell-ranges with my own but it just gives an #ERROR no matter what I put in. Any idea what might be wrong? Picture: https://imgur.com/a/FUNUatX – mTv Sep 12 '22 at 20:18
  • @mTv can you share a copy of your sheet? – player0 Sep 12 '22 at 20:47
  • Sure, https://docs.google.com/spreadsheets/d/1OMxOGU1fMjjZ-RqpdeUopV0RCsWvNuDSY8Pgc3-iEtI/edit?usp=sharing. Trying to make a formula to calculate streaks into a format like the one you posted based on the name in the "Owner" column. First 10 turns are invalid – mTv Sep 13 '22 at 19:11
  • 1
    @mTv try in your sheet: `=ARRAYFORMULA(QUERY(REGEXREPLACE(QUERY({TEXT( VLOOKUP(ROW(B2:B1000); QUERY((B2:B1000<>B1:B999)*ROW(B2:B1000); "where Col1 <>0"); 1; 1); "000000")&"×"&B2:B1000}; "select Col1,count(Col1) group by Col1 label count(Col1)''")&""; "(.+×)"; ); "where Col1 is not null"; ))` – player0 Sep 13 '22 at 19:56
  • Great thank you! What did you change? – mTv Sep 13 '22 at 20:58
  • @mTv pretty much nothing, just adapted it to your non-english locale – player0 Sep 13 '22 at 21:03
  • 1
    Seems like the only thing you did was replace ',' with ';'. I didn't know that mattered. Well thanks a lot! – mTv Sep 14 '22 at 08:23
  • 1
    @mTv see: https://stackoverflow.com/q/73767719/5632629 – player0 Sep 19 '22 at 14:14
1

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. enter image description here

bn_ln
  • 1,648
  • 1
  • 6
  • 13
  • Thank you very much, even though it has so many steps to achieve, I applied this method to my work and finished it. – zzzgoo Mar 07 '22 at 07:18