[SOLVED] Combine data in a single row

Combine data in a single row

Let's go with my new headache to convert recipes data to a more friendly format (I edited my original question thanks to adminstrators comments):

Example:

Raw data
Tiramisu
double cream
mascarpone
marsala
golden caster sugar
coffee
sponge fingers
dark chocolate
cocoa powder
400 ml
250g
75ml
5 tbsp
300ml
175g
25g
2tsp
Put the double cream, mascarpone, marsala and golden caster sugar in a large bowl.Whisk until the cream and mascarpone have completely combined and have the consistency of thickly whipped cream. ...
Sponge fingers
lemon zest
granulated sugar
eggs
vanilla bean paste or extract
flour
salt
cornstarch
powdered sugar for dusting
1 teaspoon
136 g
3
1 teaspoon
130 g
pinch of
2 tablespoon
tsp
Preheat the oven to 350°F. Prepare a piping bag with a 7/16-inch opening round piping tip. Prepare two baking pans with parchment paper. Mark out 3-inch lines as a guide on the underside of the parchment. Ensure the lines are spaced out evenly, with about 1 ½ inches of space between them. Set aside....
...

The expected output:

Transformed data
Tiramisu

Ingredients
400 ml double cream
250g mascarpone
75ml marsala
5 tbsp golden caster sugar
300ml coffee
175g sponge fingers
25g dark chocolate
2tsp cocoa powder

Process
Put the double cream, mascarpone, marsala and golden caster sugar in a large bowl.Whisk until the cream and mascarpone have completely combined and have the consistency of thickly whipped cream. ...



Sponge fingers

Ingredients
1 teaspoon lemon zest
136 g granulated sugar
3 eggs
1 teaspoon vanilla bean paste or extract
130 g flour
pinch of salt
2 tablespoon cornstarch
tsp powdered sugar for dusting

Process
Preheat the oven to 350°F. Prepare a piping bag with a 7/16-inch opening round piping tip. Prepare two baking pans with parchment paper. Mark out 3-inch lines as a guide on the underside of the parchment. Ensure the lines are spaced out evenly, with about 1 ½ inches of space between them. Set aside....
...

Overall, the raw format is like this:

Raw Data
Recipe title 1
List of ingredients
List of weights
Process
Recipe title 2
List of ingredients
List of weights
Process
...

Number of recipes is variable, one title per recipe (maybe I can add a manual tag to identify it ?). Number of ingredients is variable and just below the same number of weights. At the end one cell contains the process to realize the recipe.

The wished transformed data:

Transformed Data
Recipe title 1
Ingredients
List of weights + ingredients

Process
Description of the process' recipe1



Recipe title 2
Ingredients
List of weights + ingredients

Process
Description of the process' recipe 2
...

Any idea how to achieve this?

Solution

Here's one approach you could test & improvise on. goes along with your idea to manually mark the title row.

=reduce(tocol(,1),A:A,lambda(a,c,vstack(a,if(offset(c,,1)="T",let(Λ,xmatch("T",offset(c,1,1,rows(A:A),)), Σ,tocol(offset(c,1,,ifna(Λ-2,rows(A:A))),1),
 vstack(c,,"Ingredients",let(x,wrapcols(Σ,rows(Σ)/2),index(choosecols(x,2)&" "&choosecols(x,1))),,"Process",ifna(offset(c,Λ-1,),index(A:A,match(,0/(A:A<>"")))),,,)),tocol(,1)))))