Search code examples
google-sheetsgoogle-sheets-formulaformulatransposesubstitution

Table to 9^9 query header to table again

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

Solution

  • 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)), " ")), "♦", " "))