Google Sheets - Transpose 2D table to 1D List-CodePudding

I'll make an example with a Pokémon table type that corresponds exactly to my data structure.

On the 18 types present line 1 (representing the attacker single type), I have to convert the duos of types ( representing the attacked ) So I have to get rid of the attacker line and transform it into a column.

This will transform :

Normal none 1 1 1 1 1 1 2 1....

(total of 18duos of types squared I have 324 rows 1 row of attackers)

Into :

Normal none Normal 1
Normal none Fire 1
Normal none Water 1
Normal none Electric 1
Normal none Grass 1
Normal none Ice 1
Normal none Fighting 2

(Here we do trios of types, a total of 18 cubed : 5832 lines )

Here is an overview of the starting point.

Thanks a lot to those who want to look into this :)!

Table Screenshot

CodePudding user response：

=ArrayFormula(SPLIT(QUERY(FLATTEN(IF(A2:A="",,A2:A&"x"&B2:B&"<-"&C1:T1&":"&C2:T)),"select Col1 where Col1 is not null",0),":"))

CodePudding user response：

use:

=INDEX(QUERY(SPLIT(FLATTEN(A2:A&"×"&B2:B&"×"&C1:T1&"×"&C2:T), "×"), 
 "where Col4 is not null", ))