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

Thank you JeteMc.
Hierarchy is fine.. The data I shared are the actual data.
Also, in the Output - Parts are the lowest level parts for which I need to populate the Top level assemblies that the part is reporting to.
Can you pls help on Output - Part sheet. It should show all its unique assemblies that it is reporting to.

Ex:
Parts Where Used
70071078 -> 109494602,109494702,109494802,109494902,109495002,109495102,120007555,120007557,120007558
70071823 -> 109494602,109494702,109494802
70071827 -> 109494602,109494702,109494802,109494902,109495002,109495102
70072535 -> 109494602,109494902
60002313 -> 109494602,109494702,109494802
60002080 -> 109494602,109494702,109494802
70070671 -> 109494602,109494702,109494802,109494902,109495002,109495102,120007555,120007557,120007558
70211123 -> 109494602,109494702,109494802,109494902,109495002,109495102,109582202,109582302,109582402,109582802,109582902,109583002,120001431,120001432,120001433,120001434,120001435,120001436

and so on

Hi svijikumar,
version with vba. Procedures are triggered by buttons.
m.s.

Hi JeteMC
Thank you, this works

HI MS,
Thank you, This works.
Can you pls help in adding the unique component Numbers and map only the Top level assemblies column wise

Hi,
could it be like this?

Hi MS,
I cleared the hierarchy column (Output->Parts) sheet and rerun the generate part macro, the output files are not coming as expected. It has duplicate Finished goods for the parts, which should not be the case.
Also if I have to re-run with new set of date in the levels, should i delete in all the other sheets and rerun. If i have to do it manually, can a clear option be given.
If I have to add more levels (Level 4, 5, 6, etc), how can that be incorporated?

Hi,

"Output - Part"

After pressing the "generate part" button, the code tests cell "E2":
- if it is not empty, the code ends (there is a report in the sheet),
- if it is empty, the code continues,
After testing "E2", the code makes a copy of the source data from the sheet "Output - Part" to the sheet "Old_1".
And here I see:
- you deleted column "E" in the sheet "Output - Part", thereby fooling my test on "E2".
- you ran the macro on the old report, where for example the first number is listed 9 times. The macro then makes the same report 9 times.

To clarify: the code I wrote was mainly to verify its functionality.
It does not solve anything else.
It does not solve, for example, the situation described above = the code simply assumes that there are source numbers in column "A" that it should process.
If you write your suggestions on how it should work, I can add it.
For example. source data will be in one sheet, report in another - macro can be run repeatedly without limitation.

(Level 4, 5, 6, etc)

,
The code cannot be easily adapted to more, or an indefinite number of levels.
It is certainly possible, but the code will be more complicated.
It's a shame you didn't mention it at the beginning.
m.s.

Hi MS,
Thanks you..
I couldn't mention on the levels in the beginning because I did not encounter any BoM > 3 levels, but found couple of BoMs recently. Hence asked.

Thank you for all the help/support.

hi svijikumar,
version 5 is ready for any number of levels.
I added a check for duplicate data (sheet Double). I added speed.
I edited the section for "Part" - source data will be in the sheet "Input Part",
output will be in the "Output Part". Everything you wrote should be there.
But you need to check it properly.
m.s

Hi MS,
Thank you for the updated macro.
I just downloaded and ran the macro. I am getting the attached error.

Error.jpg

I have modified the formula. Try it.
m.s.

Thank you MS.

I ran the Output part macro and i get an error message after sometime (123 parts where-used only created)
For hierarchy and Where Used Tracking e5 Comma - After Run.xlsm
error.jpg

While generating Hierarchy, getting the error "Double rows: 27"
Hierarchy_Error.jpg

fixed.
info about the message at the end of the process: if the process runs normally, the message will be printed:
"The process <Output Part> has ended normally. Time: 00:00:08"
or
"The process <Output Hierarchy> has ended normally. Time: 00:00:12"
(Time is the duration of the process.)
If the program finds duplicate rows, it will also print:
""Double rows: 24"

Thank you MS.
It works now...