Group and split results from one row to multiple rows

Question

I have rows with information in columns that I need to split and group.

For now I have the following:

ID | A | A1 | A2 | A3

with the following output:

ID | A | A1
ID | A | A2
ID | A | A3

With the following code:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A5:A7&"|"&B5:B7&"|"&C5:E7);"|";0;0);"where Col3<>''"))

But the problem is when I want multiple group paramters as the following:

ID | A | A1 | A2 | A3 | B | B1 | B2 | B3

With the following output:

ID | A | A1
ID | A | A2
ID | A | A1
ID | B | B1
ID | B | B2
ID | B | B3

Please see the attached sheet:

https://docs.google.com/spreadsheets/d/1VAORtWGRwAnu51_yD_nS7eNY6fuQxzDubz_y4Gof_78/edit?usp=sharing

I havent find out how to manage that.

Thanks for your help.

Answer 1

try:

=ARRAYFORMULA(QUERY(SPLIT({
 FLATTEN(IF(C23:E26="";;A23:A26&"♦"&B23:B26&"♦"&C23:E26));
 FLATTEN(IF(G23:I26="";;A23:A26&"♦"&F23:F26&"♦"&G23:I26))}; "♦"); 
 "where Col1 is not null order by Col1"; 0))