Flattening a Hierarchical Account Structure in Excel with Multiple Top-Level Parents and Varying levels of Depth
I have an account hierarchy with multiple top-level parent nodes, and each parent has varying levels of child nodes (up to 8 layers deep). I want to flatten this hierarchy into a table where each level of the hierarchy is displayed in adjacent columns.
For example:
- Column 1 (Level 1) should show all the top-level parent nodes.
- Column 2 (Level 2) should show the direct child nodes of each Level 1 parent.
- Column 3 (Level 3) should show the children of each Level 2 parent, and so on.
The depth of the hierarchy is determined by the indentation of the nodes in the list, and I need to display each parent node in the correct column to show where each child rolls up to.
How can I achieve this in Excel? The hierarchy is quite big and dynamic in terms of its layering so I'm hoping to find a solution that scales well.
Below is an example. Column A shows the hiearchy. Column B is a formula that identifies the level of indentation of each node. L1-L2 columns shows an example of what I am trying to generate.
