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

user_17835120 0
Viewed 47

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.

textjoin google-query-language google-sheets-formula flatten concatenation
1 Answers

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

user_56326290
Related