Google Sheets Count Specific Sequences

JCO 0
Viewed 71

I have a complex employee schedule spanning one year with 25 individuals in a Google Sheets format. Each individual may have more than one duty on a given day and these are delimited by commas currently. Here is a shortened and simplified sample as I cannot attach the original sheet to respect anonymity of coworkers:

1/1/2022 1/2/2022 1/3/2022 1/4/2022 1/5/2022 1/6/2022 1/7/2022 1/8/2022 1/9/2022 1/10/2022 1/11/2022 1/12/2022 1/13/2022
Person 1 Office, , Lab, , Office, , Rounder, , Rounder, Night Call , Back Up Call, Rounder, Back Up Call, Rounder, Rounder, , Rounder, , Rounder, , Office, ,
Person 2 Rounder, , Rounder, , Rounder, , Rounder, , Rounder, , Office, , Office, , , , , , Office, , Office, , Office, , Office, ,
Person 3 Back Up Call Night Call, Rounder, Back Up Call, Rounder, Office,, Office, , Lab, , Lab, , , , , , Office, , Lab, , Office, , Rounder, ,
Person 4 , , Vacation, Vacation, Office, Rounder, Rounder, Rounder, Night Call, Rounder Rounder, Rounder, Rounder, Office,
Person 5 , , Vacation, Back Up Call, Night Call, , Back Up Call, Vacation, , , Vacation, Vacation, Vacation, Vacation,

To ensure fairness, I need to quantify how often certain events occur. I was helped greatly by member Osm with a solution to Count the same event occurring multiple days in a row. I have worked through that solution and understand it now, but have hit another snag. I need to count the frequency of the following sequences:

-Back Up Call | Back Up Call | Night Call

-Back Up Call | Night Call | Back Up Call

-Night Call | Back Up Call | Back Up Call

I would like the output to look something like this:

Backup/Backup/Call Backup/Call/Backup Call/Backup/Backup
Person 1 0 0 0
Person 2 2 0 0
Person 3 1 1 0
Person 4 0 2 1
Person 5 3 0 0

So far I have tried variations of IF function but I am new to array formulas and the difference in syntax that is allowed is tripping me up. I have also begun working through using REGEXREPLACE and representing each of these various text strings as a different number and getting sums, but this does not allow me to determine the order the shifts occurred in.

Does anyone have a solution which might work for this? Thank you very much, in advance.

byrow array-formulas count google-sheets
1 Answers

Get the count

Use this formula to get the count

=ArrayFormula(IF($E$3:$E$509="",, 
{BYROW($F3:$509, 
       LAMBDA(v, COUNTIF(SPLIT(REGEXREPLACE(TEXTJOIN(",", 1, TRIM(REGEXREPLACE(
       IFNA(REGEXEXTRACT(
              TRANSPOSE(QUERY(TRANSPOSE(LAMBDA(x,y, IF(COLUMN(x)<=MAX(IF(y="",,COLUMN(x))),IF(y="","Empty", y),""))(v,TRIM(REGEXREPLACE(v, ", ,|(,)[^,]*$", "")))), " Where Col1 <> '' ")), 
              REGEXREPLACE(TRIM(A$2), " \| ", "|")),"NaError"), "[[:punct:]]", ""))), 
                               REGEXREPLACE(A$2, " \| ", ","), "♥"), ","), "=♥"))),
  BYROW($F3:$509, 
       LAMBDA(v, COUNTIF(SPLIT(REGEXREPLACE(TEXTJOIN(",", 1, TRIM(REGEXREPLACE(
       IFNA(REGEXEXTRACT(
              TRANSPOSE(QUERY(TRANSPOSE(LAMBDA(x,y, IF(COLUMN(x)<=MAX(IF(y="",,COLUMN(x))),IF(y="","Empty", y),""))(v,TRIM(REGEXREPLACE(v, ", ,|(,)[^,]*$", "")))), " Where Col1 <> '' ")), 
              REGEXREPLACE(TRIM($B$2), " \| ", "|")),"NaError"), "[[:punct:]]", ""))), 
                               REGEXREPLACE(B$2, " \| ", ","), "♥"), ","), "=♥"))),
  BYROW($F3:$509, 
       LAMBDA(v, COUNTIF(SPLIT(REGEXREPLACE(TEXTJOIN(",", 1, TRIM(REGEXREPLACE(
       IFNA(REGEXEXTRACT(
              TRANSPOSE(QUERY(TRANSPOSE(LAMBDA(x,y, IF(COLUMN(x)<=MAX(IF(y="",,COLUMN(x))),IF(y="","Empty", y),""))(v,TRIM(REGEXREPLACE(v, ", ,|(,)[^,]*$", "")))), " Where Col1 <> '' ")), 
              REGEXREPLACE(TRIM(C$2), " \| ", "|")),"NaError"), "[[:punct:]]", ""))), 
                               REGEXREPLACE(C$2, " \| ", ","), "♥"), ","), "=♥")))}))

enter image description here

Display table

Assuming the first tab is named Sheet1, paste this formula in another sheet

=ArrayFormula({ "Persons", REGEXREPLACE(Sheet1!A2:C2, " \| ", CHAR(10)); Sheet1!E3:E, Sheet1!A3:C})

enter image description here

Named function

See here how you can create a named function to make the workflow easier. or make a copy of this sheet example.

and this demo on how to use it.

enter image description here

Notes

Keep an eye on the range $F3:$500 and $E$3:$E$500 If you have less or more than 500, adjust accordingly. I set it to 500 to avoid missing references.

Resources

|               **Streaks of**               |                                            |                                            |   |          |              |                      |                        |               |               |               |                       |                        |                        |            |            |            |            |              |            |              |   |              |            |              |
|:------------------------------------------:|:------------------------------------------:|:------------------------------------------:|:-:|----------|--------------|----------------------|------------------------|---------------|---------------|---------------|-----------------------|------------------------|------------------------|------------|------------|------------|------------|--------------|------------|--------------|---|--------------|------------|--------------|
| Back Up Call \| Back Up Call \| Night Call | Back Up Call \| Night Call \| Back Up Call | Night Call \| Back Up Call \| Back Up Call |   | 1/1/2022 |   1/2/2022   |       1/3/2022       |        1/4/2022        |    1/5/2022   |    1/6/2022   |    1/7/2022   |        1/8/2022       |        1/9/2022        |        1/10/2022       |  1/11/2022 |  1/12/2022 |  1/13/2022 |            |              |            |              |   |              |            |              |
|             **0[Formula Here]**            |                      0                     |                      1                     |   | Person 1 |              |                      | Office, ,              | Lab, ,        | Office, ,     | Rounder, ,    | Rounder, Night Call , | Back Up Call, Rounder, | Back Up Call, Rounder, | Rounder, , | Rounder, , | Rounder, , | Office, ,  |              |            |              |   |              |            |              |
|                      0                     |                      0                     |                      0                     |   | Person 2 | Rounder, ,   | Rounder, ,           | Rounder, ,             | Rounder, ,    | Rounder, ,    | Office, ,     | Office, ,             | , ,                    | , ,                    | Office, ,  | Office, ,  | Office, ,  | Office, ,  |              |            |              |   |              |            |              |
|                      0                     |                      1                     |                      0                     |   | Person 3 | Back Up Call | Night Call, Rounder, | Back Up Call, Rounder, | Office,,      | Office, ,     | Lab, ,        | Lab, ,                | , ,                    | , ,                    | Office, ,  | Lab, ,     | Office, ,  | Rounder, , |              |            |              |   |              |            |              |
|                      0                     |                      0                     |                      0                     |   | Person 4 | ,            | ,                    | Vacation,              | Vacation,     | Office,       | Rounder,      | Rounder,              | Rounder,               | Night Call, Rounder    | Rounder,   | Rounder,   | Rounder,   | Office,    |              |            |              |   |              |            |              |
|                      0                     |                      3                     |                      0                     |   | Person 5 | ,            | ,                    | Vacation,              | Back Up Call, | Night Call, , | Back Up Call, | Vacation,             | ,                      | ,                      | Vacation,  | Vacation,  | Vacation,  | Vacation,  | Back Up Call | Night Call | Back Up Call |   | Back Up Call | Night Call | Back Up Call |
Osm0
Related