Formula or VBA to fill down a hierarchy to create a flat file

Hi, I am working with a 20k+ line, 9tier hierarchy in excel, in order to create a flat file that is filterable by any of the nine tiers, I am currently having to manually fill down each node of the hierarchy, this creating a flat file.

Example - Original File

Tier 1 Tier 2 Tier 3 Tier 4 Tier 5 ....
a
b
c
e

Manually created flat file

Tier 1 Tier 2 Tier 3 Tier 4 Tier 5 ....
a
a b
b c
b c d
d e

The main issue is that the tiers have varying depths so could be for example 1 or 25 'e's in Tier 5, so creating a formula very difficult as has to cope with varying size of tiers.

I have added an attachments that shows more clearly what I mean.

Any help appreciated!

Attachment that shows more clearly what I mean Sample.xlsx