arraysgoogle-sheetstransposearray-formulasgoogle-sheets-query

Google Sheets Split Transpose Join

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

Solution

  • 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