Creating multilevel level Bill of Materials (BOM) from table with only parent and child data

[Andy Howcroft]

Hi,

I am trying to convert a report with BOM line data that shows the parent assembly in column A and the child component in column F (see below screenshot). All the data for the child component is contained in each row, with the parent assembly listed for each BOM line. However, the child components are used in multiple parent assemblies and the child component can also contain further subassemblies and eventually last level components, therefore they are parent items in column A also.

What I require is a method of converting the data into an exploded multilevel BOM (see bottom screenshot), but preferably in a regular Excel table. There are currently 86,000 lines and once the further levels are applied this will vastly increase the number of lines (but hopefully Excel should be able to handle this).

I have seen a coulpe of methods online using Power Query but I'm hoping this can be done using either Power Pivot or VBA.

Any help would be vastly appreciated

Report in Excel format

Hierarchy BOM required

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Creating multi level Bill of Materials (BOM) from single level table
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Andy Howcroft]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Creating multi level Bill of Materials (BOM) from single level table
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hi,

Many apologies for the cross posting, I now fully understand the reasoning behind the issue, it won't happen again.

I would like to edit my original post as it has been brought to my attention that it is far too complicated and requires better/simpler visual information. However, when I try to edit I am unable to remove the attachments/images or include new ones.

Is there something I am missing here or is this how the system works? If this is how the system works then I believe my original post will not gain any replies and I may as well start over, but it seems from the Forum rules that I shouldn't create duplicate queries. I have composed my new post and it is considerably different to the first. Are you able to advise of what my options are please?

Many thanks in advance and apologies again for the cross posting.

 

[Fluff]

I will close this thread so that you can start new one.