Split Rows with Multiple Prod Cols to Multiple Rows with single Prod Col

Question

I have a series of single rows where the product information is held in four different columns. I want to turn this into multiple rows, with a single product column.

Here's a screenshot of the sheet - with the data at the top and what I am wanting to achieve, below that:

This sheet is Shared with anyone who has the link.

Any help would be hugely appreciated.

Answer 1

yse:

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN(IF(ISBLANK(G2:J6),, 
 A2:A6&"♦"&B2:B6&"♦"&C2:C6&"♦"&D2:D6&"♦"&G2:J6&"♦"&G1:J1)), "♦")), 
 "where Col5 <> 0", 0))