I need to create an array out of google sheets columns:
| product | feature_a | feature_b | feature_c | Array |
|---|---|---|---|---|
| p1 | 1 | 1 | 1 | ["feature_a","feature_b","feature_c",] |
| p2 | 0 | 1 | 0 | ["feature_b",] |
| p3 | 1 | 0 | 0 | ["feature_a",] |
| p4 | 0 | 0 | 0 | [] |
Currently my formula is
=ArrayFormula(if(isblank(A2:A);"";"[" & if(B2:B = 1; concatenate(char(34);$B$1; char(34);char(44));"") & if(C2:C = 1; concatenate(char(34);$C$1; char(34);char(44));"") & if(D2:D = 1; concatenate(char(34);$D$1; char(34);char(44));"") & "]"))
Is there a more elegant way to achieve this, esp. without referencing each column individually? My original sheet has much more columns.
Google sheet:
CodePudding user response：
try:
=INDEX(IF(A2:A="";;"["&SUBSTITUTE(TRIM(FLATTEN(QUERY(TRANSPOSE(
IF(B2:D=1; """"&B1:D1&""""; ));;9^9))); " "; ",")&"]"))
