Home > Net >  How to customize order of header values that are joined when answer is yes in Google Sheets
How to customize order of header values that are joined when answer is yes in Google Sheets

Time:04-05

How can I modify the equation in this enter image description here

Specify order The headers are labeled with a number in the desired order. The original formula will look for a "yes" and replace that with the header value for any questions in a range and outputs as an index

The second formula will then split and sort the values in alphabetical order and then REGEXREPLACE the numbers.

Any assitance to combine these two formulas is much appreciated!

SET #1:

=index(substitute(substitute(trim(transpose(query(transpose(if(B2:E<>"yes",,substitute(B1:E1," ","❄️"))),,9^9)))," ",", "),"❄️"," "))

AND

=TRIM(REGEXREPLACE(JOIN(",",SORT(TRANSPOSE(SPLIT(E2,",")))),"\b\d_",""))

ALTERNATIVE SET #2

INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE( IF(B2:E="yes", B1:E1&",", )),,9^9))), ",$", ))

AND

=TRIM(REGEXREPLACE(JOIN(",",SORT(TRANSPOSE(SPLIT(E2,",")))),"\b\d_",""))

Thank you in advance for your help!

The shared google sheet is enter image description here

  • Related