Hierarchical data combined into list for Google Sheets

[hro5e]

Good day.

I have a sheet of data, columns D:G

Column D is the "parent" of E, E is the parent of F and F is the parent of G.

example data below:

Column D	Column E	Column F	Column G
Bedroom 1	Building	Internal Partitions	Framework
Bedroom 1	Building	Internal Partitions	Insulation
Bedroom 2	Building	Screed	Self level
Bedroom 2	Decoration	Painting	Undercoat

Now I need a formula that will combine the data into a list:

the results should be as so: (each line is its own ROW)

Bedroom 1
> Building
>> Internal Partitions
>>> Framework
>>> Insulation
Bedroom 2
> Building
>> Screed
>>> Self level
> Decoration
>> Painting
>>> Undercoat

The ">" are just for explanation purposes.

Essentially the child data is grouped into the direct parent and so on.

This is my current formula which works fine for one parent and one child scenario. with a helper column

=ARRAYFORMULA(FLATTEN(IF('04'!A3:A = 1, '04'!D3:D, '04'!G3:G))).

Any assistance would be great

 

[hro5e]

I just used pivot table... works fine although they are separated by columns

 

[hro5e]

OK, so I used a pivot table, to format the data,

then used this formula to combine the data including blanks:
=ARRAYFORMULA(FLATTEN(IF(ROW('04-1'!A:A) <= MAX(ARRAYFORMULA(IF('04-1'!A:A<>"", ROW('04-1'!A:A)))), '04-1'!A:D, "")))

then in another column use this to get rid of the blanks:

=FILTER(E:E, E:E <> "")

neat work around, but a single formula would be useful (without using googlescript)