Create Where-Used Report Hierarchially

**svijikumar** · 03-06-2025, 09:40 AM

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

**JeteMc** · 03-08-2025, 02:06 PM

Here is a formula-based solution that involves a lot of different formulas.
1. Columns C:W are populated using: =INDEX('Level 2'!$B$2:$B$88,AGGREGATE(15,6,(ROW('Level 2'!$A$2:$A$88)-ROW('Level 2'!$A$1))/('Level 2'!$A$2:$A$88=$B2),COLUMNS($C2:C2)))
2. Column X is populated using: =MAX(1,COUNT(C2:W2))
3. Column Y is populated using: =SUM(X$2:X2)
4. Columns AA:AB are populated using: =INDEX(A$2:A$637,AGGREGATE(15,6,(ROW(A$2:A$637)-ROW(A$1))/(ROWS(AA$2:AA2)<=$Y$2:$Y$637),1))
5. Column AC is populated using: =IFERROR(INDEX(C$2:V$637,MATCH(AB2,B$2:B$637,0),COUNTIFS(AB$2:AB2,AB2)),"")
6. Columns AD:AR are populated using formulas similar to columns X:AC
7. Column AS displays the hierarchy using: =TEXTJOIN(" > ",,AR2,AQ2,AP2,AO2)
Let us know if you have any questions.

**svijikumar** · 03-10-2025, 12:59 PM

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

**JeteMc** · 03-10-2025, 10:41 PM

A pivot table based on columns AO:AR on the Level 1 sheet comes close to what is shown on the Output - Hierarchy sheet.
I feel that the best way to know if a formula/pivot table based approach will be feasible is to test it on the actual data.
I don't know enough about VBA to say whether or not a macro would help with replicating the Output - Part sheet.
I had seen that this was a VBA question, but decided to reply to the thread when I saw it had gone two days without one.

**svijikumar** · 03-11-2025, 10:33 AM

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

**JeteMc** · 03-12-2025, 10:25 AM

I don't feel that this is what you want, however perhaps it will be of some help.
Columns H:I display level 2 parts that are used in level 1.
Column H is populated using: =UNIQUE('Level 1'!B2:B637)
Column I is populated using: =TEXTJOIN(",",,UNIQUE(FILTER('Level 1'!A2:A637,('Level 1'!B2:B637=H2))))
Similarly columns K:L display level 3 parts that are used in level 2 and columns N:O display level 4 parts that are used in level 3.

**svijikumar** · 03-17-2025, 12:54 AM

Hi JeteMC
Thank you, this works

**MSDols** · 03-16-2025, 08:24 AM

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

**svijikumar** · 03-17-2025, 01:06 AM

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

**MSDols** · 03-17-2025, 05:37 AM

Hi,
could it be like this?

**svijikumar** · 03-18-2025, 03:46 AM

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?

**MSDols** · 03-18-2025, 06:01 AM

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.

**svijikumar** · Today, 12:08 AM

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.

Create Where-Used Report Hierarchially

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Re: Create Where-Used Report Hierarchially

Thread Information

Users Browsing this Thread

Similar Threads

How to create automatically new sheets and the in each one of them create a report

[SOLVED] Vba to create report

Create a running total - create a "pretty" report

[SOLVED] How to create this report

Link data from a report card to create a progress report with chart. HELP Excel 2007

Create a Report

trying to create a report

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions