CODEWITHSUNDEEP
google-sheets
Tom
Tom

Reputation: 87

Using filter with offset

I have a google sheet with a number of columns and tick boxes. I want the Table header to be displayed and joined together when the boxes are ticked in each row. For example looking at the screenshot below:- In the result for John in row 2, I would want to have a Red and Green joined in Cell M2.

Currently have got this formula to pull filter the boxes that are TRUE =IFERROR(JOIN(" ",FILTER(B2:L2,B2:L2=TRUE)),"") but cant seem to work out the rest. I'm fairly sure the offset function is probably needed or a query may be easier?

enter image description here

Upvotes: 1

Views: 1070

Answers (2)

AEF
AEF

Reputation: 1

=ARRAYFORMULA(TEXTJOIN(", ",TRUE,If(B2:L2,OFFSET(B2:L2,-row()+1,),)))

Upvotes: 0

rockinfreakshow
rockinfreakshow

Reputation: 30289

formula:

=BYROW(B2:L,LAMBDA(bx,INDEX(TEXTJOIN(", ",1,IF(bx=TRUE,B$1:L$1,)))))

enter image description here

Upvotes: 3

Related Questions