Creating a BOM Heirarchy using VBA

B

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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You must log in or register to reply here.

Similar threads

K
  • Question Question
Bill of Material Cost Roll
Replies
3
Views
244
ksigota
K
J
  • Question Question
VBA to extract row data to sheet and update data set
Replies
0
Views
953
justatroy
J
J
  • Solved
Macro or VLookup? - Current VLookup Not Working
Replies
4
Views
634
RoryA
RoryA
O
  • Question Question
Sum all rows containing text string VBA
Replies
3
Views
578
DanteAmor
DanteAmor
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
437
johnnyL
J

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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