Splitting hierarchical codes into parent items (Google Sheets

user_19179763 0
Viewed 47

source cell: CCA, BF result cell: C, CC, CCA, B, BF

CCA is a complex code relating to a hierarchy. It means, CCA is child of CC, which is child of C. So, the formula should split into all parents (C and CC) and keep the child (CCA) -- and do this for a concatenated list of strings. The current maximum of levels is four (ABCD) but might be more in the future.

I have no clue how to create a formula for this, but I got this formula: =arrayformula( join( ", ", unique( flatten( transpose( left( split(A2, ", ", true, true), sequence( max( len( split(A2, ", ", true, true) ) ) ) ) ) ) ) ) )

See post here.

I'd need however a array formula in the sense that it can be applied to an entire columns

It's connected to the research data being part of https://raramagnetica.vercel.app/vikus/vismag/

flatten google-sheets concatenation arrays split
1 Answers

try:

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3, ", ")),,TEXT(ROW(A1:A3), "♥00000"))), "×")), 
 SEQUENCE(COLUMNS(SPLIT(A1:A3, ", "))*MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))), 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3, ", ")),,REPT(SPLIT(A1:A3, ", ")&"×", LEN(SPLIT(A1:A3, ", "))))), "×"), 
 {"(×)", "("&REPT(".", SEQUENCE(1, MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))-1))&")$"}, )&","), 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))), 0), "^,$", )),,9^9)))}, 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"), "offset 1", 0),,9^9))), ",$", ))

enter image description here

for non-english sheets:

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3; ", "));;TEXT(ROW(A1:A3); "♥00000"))); "×"))\ 
 SEQUENCE(COLUMNS(SPLIT(A1:A3; ", "))*MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", ")))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3; ", "));;REPT(SPLIT(A1:A3; ", ")&"×"; LEN(SPLIT(A1:A3; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; ))

update:

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;TEXT(ROW(A2:A); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;TEXT(ROW(A2:A); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;REPT(SPLIT(A2:A; ", ")&"×"; LEN(SPLIT(A2:A; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A2:A&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A2:A&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; ))

enter image description here

but if you got large dataset its better (faster) to split it into chunks like:

=ARRAYFORMULA({REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;TEXT(ROW(A2:A600); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;TEXT(ROW(A2:A600); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;REPT(SPLIT(A2:A600; ", ")&"×"; LEN(SPLIT(A2:A600; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A2:A600&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A2:A600&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; ); 
 REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;TEXT(ROW(A601:A); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;TEXT(ROW(A601:A); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;REPT(SPLIT(A601:A; ", ")&"×"; LEN(SPLIT(A601:A; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A601:A&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A601:A&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; )})
user_56326290
Related