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 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?
Share Improve this question edited Feb 18 at 5:43 James Z 12.3k10 gold badges27 silver badges47 bronze badges asked Feb 17 at 2:02 Sari DeviSari Devi 231 silver badge2 bronze badges New contributor Sari Devi is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.2 Answers
Reset to default 1Edit, 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, "")), ",")) <> "")))))
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))
)
)
)
)