Excel Hierarchy

[aetedford]

I have some data found here: https://1drv.ms/x/s!AktAOe8JUNECiKkS30iC2jH4jX5GdQ?e=eYGYBa

Column A is the PARENT with 00000000 the TOP value.

Looking at the data in column B, we see 00000000 has many 2nd level values. Looking at the first 2nd level, 00000004, we then look that up in COLUMN A. No more values, so that one ends.

00000000 -> 00000004

The next one in Column B is 00000412. Looking that up in COLUMN A, we see it has 52 2nd level values. Then looking up those values, we get 3rd level and so on.

We start seeing how it builds.

[TABLE="width: 823"]
<tbody>[TR]
[TD]LEVEL 1[/TD]
[TD][/TD]
[TD]LEVEL 2[/TD]
[TD][/TD]
[TD]LEVEL 3[/TD]
[TD][/TD]
[TD]LEVEL 4[/TD]
[TD][/TD]
[TD]LEVEL 5[/TD]
[TD][/TD]
[TD]LEVEL 6[/TD]
[TD][/TD]
[TD]LEVEL 7[/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000026[/TD]
[TD][/TD]
[TD]00014616[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000034[/TD]
[TD][/TD]
[TD]00000029[/TD]
[TD][/TD]
[TD]00000009[/TD]
[TD][/TD]
[TD]00003415[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000034[/TD]
[TD][/TD]
[TD]00000029[/TD]
[TD][/TD]
[TD]00000012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000034[/TD]
[TD][/TD]
[TD]00000029[/TD]
[TD][/TD]
[TD]00004491[/TD]
[TD][/TD]
[TD]00000010[/TD]
[TD][/TD]
[TD]00003416[/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000034[/TD]
[TD][/TD]
[TD]00000029[/TD]
[TD][/TD]
[TD]00004491[/TD]
[TD][/TD]
[TD]00000010[/TD]
[TD][/TD]
[TD]00014419[/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000034[/TD]
[TD][/TD]
[TD]00000029[/TD]
[TD][/TD]
[TD]00004491[/TD]
[TD][/TD]
[TD]00000011[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000034[/TD]
[TD][/TD]
[TD]00000031[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000034[/TD]
[TD][/TD]
[TD]00013412[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00000024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00000025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00000028[/TD]
[TD][/TD]
[TD]00007142[/TD]
[TD][/TD]
[TD]00014412[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00000033[/TD]
[TD][/TD]
[TD]00000012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00000033[/TD]
[TD][/TD]
[TD]00000014[/TD]
[TD][/TD]
[TD]00000015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00000033[/TD]
[TD][/TD]
[TD]00000020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00000035[/TD]
[TD][/TD]
[TD]00000036[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00000035[/TD]
[TD][/TD]
[TD]00014412[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00003368[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00004615[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00004732[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000037[/TD]
[TD][/TD]
[TD]00013604[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000074[/TD]
[TD][/TD]
[TD]…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000154[/TD]
[TD][/TD]
[TD]…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00000000[/TD]
[TD][/TD]
[TD]00000412[/TD]
[TD][/TD]
[TD]00000214[/TD]
[TD][/TD]
[TD]…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How can I automate this? Is it possible? Should it be done in something else, such as PowerBI?

 

[sandy666]

maybe: https://www.youtube.com/watch?v=oEOaqyaZTZw

 

[aetedford]

Updated link for file. Above did not work:
https://1drv.ms/x/s!AktAOe8JUNECiKkS30iC2jH4jX5GdQ?e=oAduhD

 

[Kyle123]

xposted: https://www.excelforum.com/excel-programming-vba-macros/1290913-excel-hierarchy.html