Concatenate input from columns into rows based on dates

Question

I'm working with a dataset in Google Sheets and need to transform it based on specific conditions. Given the input below, I aim to break down the rows into multiple rows in another sheet if "Yes" is found in the "Break Down Further?" column. Additionally, if the "Date, if different" column is blank, it should use the date from the "Timestamp" column. Finally, the output should be ordered by date.

Note the list will grow:

Timestamp	Date, if different	Total Cost	Purchase Type?	Shop Name	Description	Receipt	Break Down Further?	Cost	Purchase Type?	Description	Break Down Further?	Cost	Purchase Type?	Description
08/04/2024 21:05:14		2054	Cattle	Farmers First	17 Cattle		Yes	893.65	Sheep	3 sheep	Yes	630	Goats	7 goats
08/04/2024 21:06:15		893	Cattle	Outbacks	4 cattle	example.com
08/04/2024 21:08:17	06/04/2024	832	Sheep	Western Out	9 sheep	example.com	Yes	9302	Goats	1000 goat	No

Making this output:

| Date       | Cost   | Category | Company      | Receipt     | Description |
|------------|--------|----------|--------------|-------------|-------------|
| 06/04/2024 | 832    | Sheep    | Western Out  | example.com | 9 sheep     |
| 06/04/2024 | 9302   | Goats    | Western Out  | example.com | 1000 goat   |
| 08/04/2024 | 893    | Cattle   | Outbacks     | example.com | 4 cattle    |
| 08/04/2024 | 2054   | Cattle   | Farmers First|             | 17 Cattle   |
| 08/04/2024 | 893.65 | Sheep    | Farmers First|             | 3 sheep     |
| 08/04/2024 | 630    | Goats    | Farmers First|             | 7 goats     |

I made a EXAMPLE_SHEET so it may be easier to understand

Answer 1

You may try:

=sort(ifna(let(Σ,if(len('Form responses 1'!B2:B),'Form responses 1'!B2:B,int('Form responses 1'!A2:A)), Λ,vstack(hstack(Σ,'Form responses 1'!C2:D,'Form responses 1'!F2:F,'Form responses 1'!E2:E,'Form responses 1'!G2:G), hstack(Σ,'Form responses 1'!I2:K,'Form responses 1'!E2:E,'Form responses 1'!G2:G), hstack(Σ,'Form responses 1'!M2:O,'Form responses 1'!E2:E,'Form responses 1'!G2:G)), filter(choosecols(Λ,1,2,3,5,6,4),index(Λ,,2)<>""))),1,1,5,1,4,1)