Search code examples
google-sheetsgoogle-sheets-formula

How can I combine ranges of data while transposing them using a formula, in Google Sheets?

Currently, the sheet looks like this: enter image description here

and we need it to look like this: enter image description here

How can I achieve that using a formula? I have tried with separate formulas in each row, but it does not look right - feasible. Here is the sample for tests, if you feel like giving it a shot.

Solution

  • this is a very common problem called "unpivoting".

    In Google sheets formulas, this is one of the ways to do it:

    You will find the following formula in cell A28 on the tab called mk_Help:

    =ARRAYFORMULA(QUERY(SPLIT({FLATTEN(A3:A24&"|"&E1:G1&" "&E2:G2&"|"&E2:G2&"|"&B3:B24&"|"&C3:C24&"|"&E3:G24);FLATTEN(H3:H24&"|"&L1:N1&" "&L2:N2&"|"&L2:N2&"|"&I3:I24&"|"&J3:J24&"|"&L3:N24)};"|";0;0);"where Col1<>'' and Col6 is not null";0))