Search code examples
google-sheetsgoogle-sheets-formula

Counting 3 or more consecutive weeks in a horizontal dataset

I have a horizontal dataset which with employee names. HR should put on notes to them. This is the sample dataset:

1st dataset

I have a formula that lists the employees that hasn't been touched or never had comments from HR for 3 or more consecutive weeks. And the formula starts counting from the right most part to the left.

Now, the dataset has been changed and the latest week will always be insert after the gender column and will look like this:

revised dataset

the weeks are in reversed in descending order horizontally.

now my problem is that I am having a hard time modifying the formula to adapt the new dataset.

This is the formula that I used in the first dataset:

=let(range_,A3:12,row_,A3:B12,headr_,A2:2, col_,filter(headr_,right(headr_,5)="Notes"),data_,filter(range_,right(headr_,5)="Notes"), Σ,choosecols(makearray(rows(row_),columns(col_),lambda(r,c,if(left(index(col_,,c),3)="HR1",if(or(len(index(data_,r,c)),len(index(data_,r,c+1))),1,),))),sequence(counta(col_)/2,1,1,2)), let(Λ,index((counta(col_)/2)-byrow(Σ,lambda(z_,ifna(xmatch(1,z_,,-1))))),filter({row_,Λ,vlookup(index(row_,,1),{let(Σ,xmatch("HR1*",headr_,2,-1),choosecols(range_,1,Σ-2,Σ-1))},{2,3},)},Λ>2)))

and I am having a hard time modifying this to adapt the new dataset.

here is the sample sheet: https://docs.google.com/spreadsheets/d/192sSrwxGR-ltuhWO3j30Rw4r9C7BvANNQlGNMlqNVCE/edit#gid=0

The Data tab contains the new format of dataset and the 1st tab contains the old and the 1st formula that I used. I have also included some notes inside the sheet and sample desired results.

Solution

  • Slightly modifying the original formula posted in this thread here

    =let(range_,A3:12,row_,A3:B12,headr_,A2:2,
     col_,filter(headr_,right(headr_,5)="Notes"),data_,filter(range_,right(headr_,5)="Notes"),
     Σ,choosecols(makearray(rows(row_),columns(col_),lambda(r,c,if(left(index(col_,,c),3)="HR1",if(or(len(index(data_,r,c)),len(index(data_,r,c+1))),1,),))),sequence(counta(col_)/2,1,1,2)),
   let(Λ,index(byrow(Σ,lambda(z_,ifna(xmatch(1,z_),columns(Σ)+1)-1))),filter({row_,Λ,vlookup(index(row_,,1),{let(Σ,xmatch("HR1*",headr_,2),choosecols(range_,1,Σ-2,Σ-1))},{2,3},)},Λ>2)))

    enter image description here