Multi-level Bill Of Materials explosion

[Andy Howcroft]

Hi,

I have a single level Bill Of Materials (BOM) report that shows all parent assemblies with their subsequent child parts. I need to create a complete record of multi-level BOMs for all our products with the BOM level shown for each assembly/part.

Please note:

• The child part can also be an assembly and therefore appears in the ‘Parent’ column.
• The child parts/assemblies can be used in multiple assemblies.
• The BOM level can be 20+.
• We have 2,500 products and 10,000+ parts/assemblies and so the resulting report will be rather large.

Current report showing two Products and all subsequent assemblies and parts:

Required multi-level report with BOM levels shown for each assembly/part:

I am led to believe this could be achieved with either VBA or Power Pivot, but any other method will of course be appreciated.

Many thanks in advance.

 

[Andy Howcroft]

I don't know how to find that out; is it just easier if I send you the single BOM file and you can test it yourself, happy to do so....

PLease know that all you help is much appreciate, we're getting a little desperate here