Search code examples
google-sheetsspreadsheetarray-formulastime-and-attendance

Getting a list of header columns where the value is true

I apologise if this question appears simple, but I'm having trouble making it work. problem

I just want to know what days were each employee absent in the column G (the last column), for example I want it like: expected solution

I tried to apply some MATCH/FILTER and ARRAYFORMULA formulas, but did not crack the puzzle. Please, help.

Solution

  • Try TEXTJOIN() and FILTER().

    =IFERROR(TEXTJOIN(", ",1,FILTER($B$1:$F$1,B2:F2=1)),"")

    enter image description here

    For dynamic spill array, use-

    =BYROW(B2:INDEX(F2:F,COUNTA(A2:A)),
 LAMBDA(x,IFERROR(TEXTJOIN(", ",1,FILTER($B$1:$F$1,x=1)),"")))

    enter image description here

    Divide the results into cells.

    =ArrayFormula(IFERROR(SPLIT(
 BYROW(B2:INDEX(F2:F,COUNTA(A2:A)),
 LAMBDA(x,IFERROR(TEXTJOIN(", ",1,FILTER($B$1:$F$1,x=1)),""))), ", ", 0),""))

    enter image description here

    Used formulas help
    ARRAYFORMULA - IFERROR - SPLIT - BYROW - COUNTA - LAMBDA - TEXTJOIN - FILTER