Concatenate columns inside ArrayFormula, excluding certain values

0
votes

Please see the Google Sheet at the link below. In column 'F', I need to use an ARRAYFORMULA to combine the values from columns 'B' through 'E', separating the values with commas, and excluding the values that read "No". Column G shows the desired output of the ARRAYFORMULA.

https://docs.google.com/spreadsheets/d/11GMOU0I4v1TcyBAnTDKPfccGWO9LYoM8_wz6hx_JXg0/edit?usp=sharing

Edit: Both answers below solve the problem. Thanks to you both!

2
google-sheetsconcatenationgoogle-sheets-formulaarray-formulasgoogle-sheets-query

2 Answers

1
votes

You can use

=ArrayFormula(IF(LEN(B2:B)>0, REGEXREPLACE(transpose( 
             query(transpose({B2:C, E2:E, G2:G})&",",,9^9)),"(No, )|(, No)|(,$)",""),""))

enter image description here

1
votes

use:

=ARRAYFORMULA(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF((B2:E="")+(B2:E="no"),,B2:E&",")),,9^9))), ",$", ))

enter image description here

update:

=ARRAYFORMULA(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(({B2:C, E2:E, G2:G}="")+
    ({B2:C, E2:E, G2:G}="no"),,
     {B2:C, E2:E, G2:G}&",")),,9^9))), ",$", ))

enter image description here