Create Where-Used Report Hierarchially

Dear JeteMc,
Thank you for the help.

Looks like it is not capturing the unique values.

Ex:
70087670 (Output - Part) reports its parents 70098247, 70098245, 70098241, 70098211
- Each of the parents reports to its parents
- 70098247 -> 70073958 -> 109582202,109582302,109582402,120001431,120001432,120001433,120007559,120007561,120007562,120007569,120007570,120007571
- 70098245 -> 70073958 -> 109582202,109582302,109582402,120001431,120001432,120001433,120007559,120007561,120007562,120007569,120007570,120007571
- 70098241 -> 70073958 -> 109582202,109582302,109582402,120001431,120001432,120001433,120007559,120007561,120007562,120007569,120007570,120007571
- 70098211 -> 70073958 -> 109582202,109582302,109582402,120001431,120001432,120001433,120007559,120007561,120007562,120007569,120007570,120007571

So, the hierarchy will be 70087670 -> 70098247, 70098245, 70098241, 70098211 -> 70073958 -> 109582202,109582302,109582402,120001431,120001432,120001433,120007559,120007561,120007562,120007569,120007570,120007571 (Should be unique of all their parents)

Ex: 2
3 X 3.5 SHIPPER LABEL reports 109582202,109582302,109582402,109582802,109582902,109583002,120001431,120001432,120001433,120001434,120001435,120001436,120007559,120007561,120007562,120007563,120007564,120007565,120007566,120007567,120007568,120007569,120007570,120007571

Output - Hierarchy - is the BOM explosion with all the levels in 1 sheet
Output - Part - is the where-used mapping to the Level 1 parts (Top Assemblies)


Output should be populated in the 2 sheets (Output - Part & Output - Hierarchy)

** Will this approach (Formula based) be feasible if the number of child parts for the parent is >100 parts.

Macro would help is my thought, if other use pls advise and request help