Search code examples
google-sheets-formula

Generate a 3 column table by flattening the given multi column table using google sheets formula

I want to generate a 3 column table by flattening the given multi column table using google sheets formula. The formula should scale to any sized input table.

The sample file is here https://docs.google.com/spreadsheets/d/1qTvQ58hh1jJEMVWqhQ833jjDBpuudJambmvdJemESPI/edit?usp=sharing

enter image description here

I have tried with

={ARRAYFORMULA(B2:B6),ARRAYFORMULA(D2:D6)}
Solution

  • Disaggregating is not a simple process. You can do it in different ways. Here is one with REDUCE that doesn't involve joining and splitting process. I've moved your input chart some columns to the right so the formula is "scalable" to as many companies you may have:

    =QUERY(
  REDUCE({"","",""},SEQUENCE(COUNTA(D2:D)),LAMBDA(a,c,{a;
  REDUCE({"","",""},SEQUENCE(COUNTA(F1:1)),LAMBDA(d,e,{d;INDEX(D2:D,c),INDEX(F1:1,1,e),INDEX(F2:1000,c,e)}))})),
 "Where Col1 is not null",)

    enter image description here