Create Where-Used Report Hierarchially

Need help with a macro or suggestions on how to get the hierarchical BoM using which I can generate the lowest level part mapped with their sub-assemblies and till the top assembly level

Level 1 - Top Level Assemblies (Column A) with their parts containing Sub-Assemblies
Level 2 - Sub Assemblies from Level 1 and their parts containing Sub-Assemblies
Level 3 - Sub Assemblies from Level 2 and their parts - No sub-assemblies

Levels (Sheets) could have more level if the sub assemblies are found till the lowest level parts are extracted without further sub-assemblies.

Looking for a 2 report
1. Lowest level parts in Level 3 mapped with hierarchy till Level 1

Ex: 70072267 > 70073609 > 70073959 > 109582202

2. Build a hierarchical BoM using the level 1 BoM, find the sub-assemblies in level 2 and have it child parts and so on till only lowest level parts

Last Level is the lowest level parts of the last Sub-Assemblies

Thank you