Creating a BOM Heirarchy using VBA

blakecw3

New Member
Joined
Nov 15, 2017
Messages
1
Hello,

I'm trying to take a list of items and components and create a heirarchical Bill of Materials from the data sample shown here. The total list will be for 12000+ rows.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Item Number[/TD]
[TD]UOM[/TD]
[TD]Component[/TD]
[TD]Component UOM[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CPT001A[/TD]
[TD]EA[/TD]
[TD]FPM1010R[/TD]
[TD]MS[/TD]
[TD]0.19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CPT0002A[/TD]
[TD]EA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CPT005A[/TD]
[TD]EA[/TD]
[TD]FPM15XR[/TD]
[TD]MS[/TD]
[TD]0.17[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CPT010A[/TD]
[TD]EA[/TD]
[TD]FPM3010R[/TD]
[TD]MS[/TD]
[TD]0.19[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]CPT00122CYFN[/TD]
[TD]EA[/TD]
[TD]CPT001[/TD]
[TD]EA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]CRE00562NNNN[/TD]
[TD]EA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]MME3S01613VP[/TD]
[TD]EA[/TD]
[TD]21334[/TD]
[TD]EA[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MME3S01613VP[/TD]
[TD]EA[/TD]
[TD]10105[/TD]
[TD]EA[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MME3S01613VP[/TD]
[TD]EA[/TD]
[TD]992[/TD]
[TD]EA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]MME3S01613VP[/TD]
[TD]EA[/TD]
[TD]10114[/TD]
[TD]EA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]MME3S01613VP[/TD]
[TD]EA[/TD]
[TD]985[/TD]
[TD]EA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]MME3S01613VP[/TD]
[TD]EA[/TD]
[TD]984[/TD]
[TD]EA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]MME3S01613VP[/TD]
[TD]EA[/TD]
[TD]982[/TD]
[TD]EA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]MME3S01613VP[/TD]
[TD]EA[/TD]
[TD]980[/TD]
[TD]EA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]982[/TD]
[TD]EA[/TD]
[TD]323[/TD]
[TD]LB[/TD]
[TD]1.75[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]984[/TD]
[TD]EA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]985[/TD]
[TD]EA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In column A, an item number may or may not have a corresponding component in column C. I want to create a new array in a separate worksheet where the component is matched with the Item Number and the quantity is shown for each level in the BOM in a table of conversions. If there is no component value, then it should be skipped in the list. Note that there can be multiple components for a given Item Number and the list should repeat with each item number and component pairing on a separate line. If the Item Number does have a component, the component value will have to be searched in the item number column to find its subcomponent in the BOM. The BOM can go as high as 8 levels of subcomponents and can have as many as 8 components for each Item Number. The example results would look something like the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item Number[/TD]
[TD]Component 1[/TD]
[TD]Component 2[/TD]
[TD]Component 3[/TD]
[TD]Quantity Item --> Component 1[/TD]
[TD]Quantity Component 1 --> Component 2[/TD]
[TD]Quantity Component 2--> Component 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CPT001A[/TD]
[TD]FPM1010R[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CPT005A[/TD]
[TD]FPM15XR[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CPT010A[/TD]
[TD]FPM3010R[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CPT00122CYFN[/TD]
[TD]CPT001[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]MME3S01613VP[/TD]
[TD]21334[/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]MME3S01613VP[/TD]
[TD]10105[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]MME3S01613VP[/TD]
[TD]992[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MME3S01613VP[/TD]
[TD]10114[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MME3S01613VP[/TD]
[TD]985[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]MME3S01613VP[/TD]
[TD]984[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]MME3S01613VP[/TD]
[TD]982[/TD]
[TD]323[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]MME3S01613VP[/TD]
[TD]980[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you for your review.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,226,114
Messages
6,189,052
Members
453,522
Latest member
Seeker2025

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top