Search code examples
google-sheetsgoogle-sheets-formula

Formula to return header data for group of columns where sum is greater than 0

I have a Google Sheet where the following data is on the Operator Qty tab (it continues indefinitely horizontally and vertically in this fashion):

OPERATOR: Sam L Bob R Sandra S Tom E Becky W
SHIFT: Weekend Day Night Day Day
DATE CH LD RQ AQ PQ CH LD RQ AQ PQ CH LD RQ AQ PQ CH LD RQ AQ PQ CH LD RQ AQ PQ
Apr 30, 2023 2 96 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
May 1, 2023 0 0 0 0 0 5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
May 2, 2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 155 0
May 9, 2023 0 0 0 0 0 0 7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
May 11, 2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 122 0 0 0 0 0
Jun 15, 2023 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

On the Dashboard tab, I am displaying a month and year in cell A1 ('May 2023' in this case) which is being calculated by this formula: =TEXT(EDATE(today(),-11),"MMM YYY")

Using this month and year to narrow down which rows to search for using the dates in column A on the Operator Qty tab, I am trying to create a formula for cell A2 of the Dashboard tab which will list all the operators in range A2:A12 who appear in row 1 of the Operator Sheet and meet these conditions:

  1. They are on the Day shift (as specified in row 2).
  2. They have a number in any one of the cells in the five columns assigned to them which is greater than 0 (so Bob R's range is G5:K8).

So as per my example above, this should be my output:

May 2023
Bob R
Tom E
Becky W

However, the best I have been able to do is to use the following formula, but it only returns the operator name who has a number in the applicable range larger than 0 in the column directly under the name in row 1: =FLATTEN(FILTER('Operator Qty'!$B$1:$1,('Operator Qty'!$B$2:$2="Day")*TRANSPOSE(MMULT(TRANSPOSE(('Operator Qty'!$A$4:$A>=EOMONTH(C$1,-1)+1)*('Operator Qty'!$A$4:$A<=EOMONTH(C$1,0))*('Operator Qty'!$B$4:$9>0)),SEQUENCE(ROWS('Operator Qty'!$A$4:$A),1,1,0))>0)))

How would I search all groups of 5 columns and display the name of the first column header of each group?

Solution

  • You may try:

    =let(Σ,reduce(,'Operator Qty'!B4:index('Operator Qty'!Z:Z,match(,0/('Operator Qty'!A:A<>""))),lambda(a,c,vstack(a,let(〱,lambda(x,scan(,x,lambda(a,c,if(c="",a,c)))),hstack(index('Operator Qty'!A:A,row(c)),index(〱('Operator Qty'!1:1),column(c)),index(〱('Operator Qty'!2:2),column(c)),c))))),
     unique(ifna(filter(index(Σ,,2),eomonth(A1,)=eomonth(index(Σ,,1),),index(Σ,,3)="Day",index(Σ,,4)),"-_-")))

    enter image description here

    Updated formula:

    =let(Σ,index(match(,0/('Operator Qty'!A4:A<>""))),Λ,tocol(,1),Ξ,reduce(Λ,'Operator Qty'!B1:1,lambda(a,c,vstack(a,if(c="",Λ,hstack(tocol(map(indirect("'Operator Qty'!A4:A"&Σ),lambda(Σ,wraprows(Σ,5,Σ)))),wrapcols(c,Σ*5,c),wrapcols(offset(c,1,),Σ*5,offset(c,1,)),tocol(offset(c,3,,Σ,5))))))),
     unique(ifna(filter(index(Ξ,,2),eomonth(A1,)=eomonth(index(Ξ,,1),),index(Ξ,,3)="Day",index(Ξ,,4)),"-_-")))