Search code examples
concatenationgoogle-sheets-formulaflattengoogle-query-languagetextjoin

How to add condition to Array to concatenate Columns A&B only if Column B value is not blank?

Trying to display output of columns A & B when value in column B is present in a single cell.

I can join all of the non blank value in column B by using textjoin, combining a filter & an array I can get the output in two steps.

But I am wondering if there is a way to do this in one step such as combining the array & filter function or in some other way?

enter image description here

here is a link to spreadsheet.

I think maybe this can be accomplished by substituting any of the "subjects" that are not followed by an answer. Possibly using a Regex replace.

Solution

  • use:

    =TEXTJOIN(CHAR(10), 1, FLATTEN(QUERY(TRANSPOSE(FILTER(A:B, B:B<>"")),,9^9)))

    enter image description here

    or:

    =TRIM(JOIN(, FILTER(A:A&" "&B:B&CHAR(10), B:B<>"")))

    enter image description here

    update

    =INDEX(TRIM(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
 IF(A2:E6="",,"♦"&A1:E1&" "&A2:E6)),,9^9)), "♦", CHAR(10))))

    enter image description here