1

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

player0
  • 124,011
  • 12
  • 67
  • 124
Vince
  • 31
  • 5

1 Answers1

1

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?

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks a lot @player0, it works perfectly. It is impressive to do this kind of formula...Could you please explain me how this formula works, please ? Again a BIGggg thank you – Vince Nov 23 '22 at 19:43
  • 1
    @Vince sure, added step-by-step walkthrough – player0 Nov 23 '22 at 20:49
  • 1
    Thanks it was helpfull to have all explanations....but I will read again them during the incoming days, to try to understand definitively the final formula – Vince Nov 24 '22 at 03:53
  • @Vince maybe this could help: https://stackoverflow.com/a/74393500/5632629 if you scroll down on "why lambda?" part – player0 Nov 24 '22 at 11:44