Join Column Name in Excel For Value Number is One

Question

I have dataset like this:

Name	A1	A2	A3	A4	A5
Maria	0	0	0	1	1
Budi	0	1	0	0	0
Maya	0	0	0	0	0

i want change to like this:

Name	A1	A2	A3	A4	A5	Anomali
Maria	0	0	0	1	1	A4, A5
Budi	0	1	0	0	0	A2
Maya	0	0	0	0	0	NULL

i try this but error

=IF(TEXTJOIN(", ", TRUE, IF(A2:G2=1, A$1:G$1, ""))="", "NULL", TEXTJOIN(", ", TRUE, IF(A2:G2=1, A$1:G$1, "")))

how i can do this with google sheet?

Answer 1

The only "anomalie" with your initial formula is that it needed to be wrapped with ARRAYFORMULA() to work, so your updated formula would be:

=ARRAYFORMULA(IF(TEXTJOIN(", ", TRUE, IF(A2:G2=1, A$1:G$1, ""))="", "NULL", TEXTJOIN(", ", TRUE, IF(A2:G2=1, A$1:G$1, ""))))

Also, If you prefer a formula that is more straightforward and easier to understand, you can use and drag the following one:

=IF(SUM(B2:F2)=0, "NULL", TEXTJOIN(", ", TRUE, FILTER(B$1:F$1, B2:F2=1)))

If you want a single formula to process the whole table, but relies on more advanced functions, you can use:

=LET(
  data, FILTER(B2:F, LEN(A2:A)),
  BYROW(
    data,
    LAMBDA(r,
      IF(
        SUM(r)=0,
        "NULL",
        TEXTJOIN(", ", TRUE, FILTER(B$1:F$1, r=1))
      )
    )
  )
)

Answer 2

Edit, found a simpler way of doing it:

=ARRAYFORMULA(
 IF(LEN(JOIN(", ", FILTER(B$1:F$1, B2:F2=1))) = 0,
 "NULL",
 JOIN(", ", FILTER(B$1:F$1, B2:F2=1))))

Another way:

=ARRAYFORMULA(IF(
 LEN(TEXTJOIN(", ", TRUE, IF(B2:F2=1, A$1:F$1, ""))) = 0,
 "NULL",
 TEXTJOIN(", ", TRUE, IF(B2:F2=1, A$1:F$1, ""))))

My version is a bit more convoluted, but it seems to work:

=ARRAYFORMULA(IF(JOIN(", ", IFERROR(FILTER(TRANSPOSE(SPLIT(JOIN(",", IF(B2:F2=1, A$1:F$1, "")), ",")), TRANSPOSE(SPLIT(JOIN(",", IF(B2:F2=1, A$1:F$1, "")), ",")) <> ""))) = "", "NULL", JOIN(", ", IFERROR(FILTER(TRANSPOSE(SPLIT(JOIN(",", IF(B2:F2=1, B$1:F$1, "")), ",")), TRANSPOSE(SPLIT(JOIN(",", IF(B2:F2=1, B$1:F$1, "")), ",")) <> "")))))