Dynamically split a row depending on cell results & copying the beginning of the rows each time

Question

I'm trying to dynamically split a row in columns depending on the results of my cells with copying the beginning of my rows each time.

What I have :

What I need :

The Google Doc example

I've tried it over & over but I can't figure it out and it seems I need a macro for it. Problem is : I might change the rows/layers later, so I would need to use a formula for it in order to understand what is happening.

What I did:

• I tried to change the layout of my data to get around the pb by regrouping all the "HD" "HF" & "CBS" columns together & next to each other to use "transpose" easily, but (1) I don't manage to make it dynamical at all (2) didn't work
• Then I kind of tried to do the simplest thing : even if rows are empty just copy & transpose all of them and I would manually or try to find a way to suppress empty rows, but that didn't work either.
• Transposing depending on count(a). Obviously didn't work. Main problem is that I just don't get how to make the desired results dynamically linked to the initiated data.
• With IF function, not flexible enough

Based on the sheet example, what I want the page to do :

• If Count(A) = 15, then copy the first 4 cells of the row + E3:G3
• If Count(A) = 12, then copy the first 4 cells of the row + E3:G3, AND go in next row copy the first 4 cells of the row + H3:J3
• If Count(A) = 9, then copy the first 4 cells of the row + E3:G3, AND go in next row copy the first 4 cells of the row + H3:J3 AND go in next row copy the first 4 cells of the row + K3:M3

.....

I know there is not a lot to work from, but I hope at least it was clear.

Answer 1

If the upper-left cell of the first table (where you have 'Initiate Format' is in A1), then the following (entered on a new tab or to the right of the first table) should work:

=query({{A2:D,E2:G};{A2:D,H2:I};{A2:D,J2:L};{A2:D,M2:O};{A2:D,P2:R};{A2:D,S2:U}},"where Col5 is not null order by Col4",1)

We are using array literals to 'glue' the first four columns to each of the next set of three in turn, then using query to remove all the blanks. Your second table appears to be sorted by name so the 'order by' in the query select string ensures this is the case after removing the blanks.