google-sheetsgoogle-sheets-formulaunpivot

How can I transpose some specific columns, while repeating first 4 columns using a formula in Google Sheets?


I would need the first 4 columns to have their data repeated, while the others are stacked up, like the example below and whose link can be found here: enter image description here


Solution

  • Here's one approach you may test out:

    =reduce(tocol(;1);E2:G6;lambda(a;c;vstack(a;hstack(index(A1:D6;row(c));index(A1:G1;column(c));c))))
    

    enter image description here

    UPDATED FORMULA:

    =reduce(tocol(;1);E2:index(G:G;match(;0/(A:A<>"")));lambda(a;c;vstack(a;hstack(index(A:D;row(c));index(A1:G1;column(c));c))))