CODEWITHSUNDEEP
arraysgoogle-sheetstransposearray-formulasgoogle-sheets-query
jbm
jbm

Reputation: 35

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

Upvotes: 1

Views: 1640

Answers (2)

MattKing
MattKing

Reputation: 7773

This might also work:

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

Upvotes: 1

player0
player0

Reputation: 1

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

Upvotes: 1

Related Questions