Search code examples
google-sheetslambdaflattengoogle-sheets-formula

Google Sheet Special Pivot Table

I don't find the solution to create the Output table from the Input one.

Could you please help me to find a solution to create this output table with pivot table or with a special formula which are not in my competence?

Here the link to the file where you will find the result that I would like (output table) https://docs.google.com/spreadsheets/d/1tEFlJisRlCy6RoJuGREuZV4hH_eUcIq6tZETy7RT_IM/edit?usp=sharing

Thanks a lot

Input

enter image description here

Output

enter image description here

Solution

  • use:

    =INDEX({{B3\0\1\2\3\4\"NA"\"[3]"\"[4]"}; B4:B13\IFNA(BYCOL({0\1\2\3\4\"NA"\"[3]"\"[4]"}; 
 LAMBDA(b; BYROW(B4:B13; LAMBDA(a; TEXTJOIN(CHAR(10); 1; 
 LAMBDA(x; FILTER(INDEX(x;;3); INDEX(x;;2)=b; INDEX(x;;1)=a))
 (SPLIT(FLATTEN(B4:B13&"​"&C4:G13&"​"&C3:G3); "​"))))))))})

    enter image description here

    step-by-step formula walkthrough

    why LAMBDA is used?