Search code examples
google-sheetsgoogle-sheets-formula

How can I transpose part of the data, while repeating some other column values in Sheets?

The dataset looks like this, as it gets form submissions: enter image description here

Then, the requirement is that the columns after column B get transposed, while splitting the cell values by the comma and also transposing and repeating the column headers, so that it looks like this: enter image description here

I have this file for tests, in case you feel like helping out. My attempts fail, as I cannot wrap my head around more advanced functions like lambda, map, etc. https://docs.google.com/spreadsheets/d/1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU/edit?usp=sharing

Solution

  • You may try:

    =let(Λ;tocol(;1); reduce(Λ;C2:index(D:D;match(;0/(A:A<>"")));lambda(a;c;vstack(if(iserror(a&"");Λ;a);let(Σ;tocol(split(c;", ";));if(c="";Λ;
 hstack(chooserows({index(A:B;row(c))\index(1:1;column(c))};sequence(rows(Σ);1;1;0));Σ)))))))

    enter image description here