Search code examples
if-statementgoogle-sheetstransposearray-formulasgoogle-sheets-query

Transform matrix into column on google sheets

I have a number of matrixes such as:

Input Matrix

I would like to transform this data in such way, that it is resembled in one column, such as:

Output Rows

Here is the link to a testsheet with exactly this data: https://docs.google.com/spreadsheets/d/1kn6yYL3dsTZ7IL5Z8a5j0itfwZK9TkRQsBCzXdCG2zo/edit#gid=0

What I have done so far: I know, that I can do this using "transpose" or "mtrans" (same thing). But unfortunately for this I will have to do it manually. I'm looking for just ONE formular to solve this problem, so I don't have to always do it manually for every row.

Solution

  • use:

    =ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(MONATLICH!R3:Y<>""; "♠"&MONTH(MONATLICH!B3:B)&"♦"&MONATLICH!B3:B&"♦"&
 MONATLICH!C3:C&"♦"&MONATLICH!R2:Y2&"♦"&MONATLICH!R3:Y&"♦"&MONATLICH!Z3:AG; ))
 ;;999^99));;999^99); "♠")); "♦"))

    0

    spreadsheet demo