Table to 9^9 query header to table again

Osm 0
Viewed 32

I have this sheet with a table

I want to go from the table to nested header and back to table again from the nested ranges header.

I tried using split transpose to expand the table, but it didn't work.

enter image description here

formula substitution transpose google-sheets-formula google-sheets
1 Answers

looking at your dataset it is not possible to go from A12 to A16. you will need to attach some unique symbols first and only then you can cut it with split fx

in A12 use:

=ARRAYFORMULA(QUERY(SUBSTITUTE(TRIM(A1:E9), " ", "♦"),,9^9))

in A16 use:

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(FLATTEN(A12:E12), " ")), "♦", " "))

or full:

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(FLATTEN(QUERY(SUBSTITUTE(
 TRIM(A1:E9), " ", "♦"),,9^9)), " ")), "♦", " "))
player00
Related