Change Vertical Rolling Hierarchy To Horizontal

The accounts listed on this report change on a monthly basis. I am attempting to create a macro or write a formula that can but used each month to fill in this list automatically. On Sheet 1 is a short example of what the report looks like when exported into excel. The numbers on the left are the numerical hierarchy. The long set of numbers are the account numbers and the others are the categories that each are located under.

Sheet 2 is what I need the end result to look like. One of the problems I am running into is how to handle the fact that the hierarchy re-uses numbers. The hierarchy always runs vertical, therefore "5" always falls under the "4" that is directly above but when there is another "4" it should be ignored and continue on up the chart until it finds the first "3" and place that in the cell to the right then find the first "2" and so on.