Google Sheets Split Transpose Join

1
votes

All, I am attempting to parse a column, transpose its contents and join it to another column. I have seen a few variations of how to accomplish with a mix of ARRAYFORMULA(), TRANSPOSE(), SPLIT() which almost works but most of them filter out blanks. In my case, the column being parsed may have rows with blanks which is perfectly normal. Sample input and desired output below and shared sheet. Thanks in advance.

enter image description here https://docs.google.com/spreadsheets/d/1OCJU5BGAWMt99cuufWlsua0oNCZCYjrEEFfH-YWbj4Q/edit?usp=sharing

2
arraysgoogle-sheetstransposearray-formulasgoogle-sheets-query

2 Answers

1
votes

try:

=ARRAYFORMULA(SORT(UNIQUE(IFERROR(SPLIT({IFERROR(FILTER(A2:A, B2:B="")); 
 TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF((
 IFERROR(SPLIT(B2:B, ";"))<>""),"♦"&A2:A&"♣"&
 IFERROR(SPLIT(B2:B, ";")), )),,99^99)),,99^99), "♦"))}, "♣")))))

or:

=ARRAYFORMULA(ARRAY_CONSTRAIN(SORT(UNIQUE(IFERROR(SPLIT({
 IFERROR(FILTER(A2:A&"♣ ♣"&ROW(A2:A), B2:B="", A2:A<>"")); 
 TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF((
 IFERROR(SPLIT(B2:B, ";"))<>""),"♦"&A2:A&"♣"&
 IFERROR(SPLIT(B2:B, ";"))&"♣"&ROW(A2:A), ))
 ,,99^99)),,99^99), "♦"))}, "♣"))), 3, 1), 99^99, 2))

enter image description here

0
votes

This might also work:

=ARRAYFORMULA(SUBSTITUTE(QUERY(SPLIT(FLATTEN(A2:A23&"|"&SPLIT(IF(B2:B23="","#",B2:B23),";")),"|",0,0),"where Col2<>''"),"#",""))