How to layer out balance sheet data

[iliab]

I want to transform the data from a balance sheet. The problem is that the data is layered, where the level of an item is given in the vertical order. However, for analysis purposes it is better to have the order of levels on the horizontal side (see pictures). To add more difficulty, the code of the first layer starts with AH and ends with TT, the second layer with only T, and the third layer starts with AH and ends in a number (thus without the T). This is just a small part of the data so how can I make an order on the horizontal level based on the code of each level?

[CoG]

Not very complicated although it is very hard coded. Assuming the structure you gave is always consistent, this workflow should have the desired effect:

[iliab]

Thanks a lot Andrew,, helped a lot!. However, when I tried it for example with a larger dataset for the code field, it does not give me the desired output. How should I adjust the workflow you have sent accordingly? The example I used is in the excel file

[CoG · Accepted solution]

Ah... I see. As I mentioned, the solution was very hard coded (only built for the data set provided). You can fairly easily adjust the workflow to handle one more tier, but if adding 'T's is the way that you know which tier you are in, then maybe a more dynamic approach can be taken with an iterative macro.