Repeat a transpose formula that returns an array every n rows, but increment the formula by one row only

Question

Context: I need to import this in a tool that only accepts a specific format. My table contains rows for invoices and columns for the products in the invoice. Products 1 and 2 come in from my export - Product 3 in the example is a copy of Product 2 and is required as a line item in the invoice.

--> I need to transpose each Invoice row (3 columns into 3 rows), and when pulling down the formula I want to increment it by 1 row only, so as to move to the next array of 3 transposed rows. Repeat n times for n invoice rows.

I've tried using transpose functions and think this could be solved with an array formula but am not familiar enough with them to find a solution. I've colour-coded the expected result in the screenshot below.

Thank you!

Answer 1

Try:

=arrayformula({"Invoice","Product","Amount payable";
split(flatten(A4:A5&"??"&regexreplace(B3:D3,"\s-.*",)&"??"&B4:D5),"??")})

Learn more about this technique: Unpivot In Google Sheets With Formulas