Google sheets count consecutive occurrences

JCO 0
Viewed 71

I have a complex year-long schedule for 25 people in google sheets and I need to quantify how often each individual has to work multiple nights in a row over the course of the entire year. I need to bin these into 2 nights in a row, 3 in a row, etc. Here is a simplified view of 1 week, in which I need to count consecutive occurrences of "Back Up IV Call". I have found some similar tasks using query function and vlookup but am having trouble adapting this to my data. Is anyone able to give me a hand with this?

schedule week 1

google-sheets
1 Answers

Use this formula

=IFNA(TEXTJOIN(", ", 1, 
 FILTER(BYROW(SPLIT(FLATTEN(SPLIT(REGEXREPLACE (TEXTJOIN(",",1,IF(B3:3="",, REGEXMATCH(B3:3, "Back Up IV Call" ))),"FALSE,","0"),"0",)),","), LAMBDA(vv, COUNTA(vv))), 
        BYROW(SPLIT(FLATTEN(SPLIT(REGEXREPLACE (TEXTJOIN(",",1,IF(B3:3="",, REGEXMATCH(B3:3, "Back Up IV Call" ))),"FALSE,","0"),"0",)),","), LAMBDA(vv, COUNTA(vv)))>1)),"No streak")

enter image description here

Get the result in a table

Rename your input sheet to Sheet1
And In another sheet, paste this formula.

=ArrayFormula(IF(Sheet1!B3:B="",, { Sheet1!B3:B,SPLIT(Sheet1!A3:A, ", ", 0)}))

enter image description here

Explanation

01 - REGEXMATCH(B3:3, "Back Up IV Call" )
To get array of TRUE / FALSE

02 - IF(B3:3="",,
IF B3:3 cells is empty "" then do nothing ,, IF not excute the REGEXMATCH formula.

03 - TEXTJOIN(",",1,IF(B3:3="",, REGEXMATCH(B3:3, "Back Up IV Call" )))
To TEXTJOIN the TRUE / FALSE array with ",".

04 - REGEXREPLACE(TEXTJOIN(...)
To Replace "FALSE," with a 0.

05 - SPLIT(REGEXREPLACE(TEXTJOIN(...)
To Split the jointed arrar "cell" with 0 set as a delimiter.

06 - FLATTEN(...)
To Flatten the streaks in one column for example TRUE | TRUE,TRUE in one column

TRUE
+-----------+
TRUE,TRUE

07 - BYROW(SPLIT(FLATTEN(...), LAMBDA(vv, COUNTA(vv))
To COUNTA BYROW the splited cells of the Array from this:

TRUE
+-----------+
TRUE,TRUE

To this

TRUE
+-----------+
TRUE | TRUE

08 - FILTER(BYROW(...), BYROW(...)>1)
To Flatten the streaks in one column for example TRUE | TRUE,TRUE in one column
To filter the counted list and accept only values > 1 Greater than 1.

09 - TEXTJOIN(", ", 1, FILTER(...)
To join the array in one cell with ", " as a delimiter.

I hope you find this helpful.

Osm0
Related