How do I create a multi-level bill of materials from a single level BOM table?

E

EinarG

New Member
Joined
Jul 3, 2014
Messages
8
Starting with this Single Level BOM table (Windows 7 Pro, Excel 2013). "Qty Per" refers to the quantity of the child needed in the Parent:

[TABLE="width: 258"]
<tbody>[TR]
[TD]Parent[/TD]
[TD]Child[/TD]
[TD]Qty Per[/TD]
[/TR]
[TR]
[TD]PART-A[/TD]
[TD]PART-A1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PART-A[/TD]
[TD]PART-A1121[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]PART-A[/TD]
[TD]PART-A2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]PART-A[/TD]
[TD]PART-A222[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]PART-A1[/TD]
[TD]PART-A11[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]PART-A11[/TD]
[TD]PART-A111[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PART-A11[/TD]
[TD]PART-A112[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]PART-A112[/TD]
[TD]PART-A1121[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PART-A112[/TD]
[TD]PART-A1122[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]PART-A2[/TD]
[TD]PART-A21[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PART-A2[/TD]
[TD]PART-A22[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]PART-A2[/TD]
[TD]PART-A221[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]PART-A22[/TD]
[TD]PART-A112[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]PART-A22[/TD]
[TD]PART-A221[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PART-A22[/TD]
[TD]PART-A222[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]PART-B[/TD]
[TD]PART-A1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]PART-B[/TD]
[TD]PART-A21[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I need (a Macro) to generate the exploded (or stacked) table below. As an example doing this manually for the Grand Child, the process went like this:

1. For each single level BOM Parent/Child row (R), find # grand children rows (N)
2. Copy entire row (R) and insert N-1 copies below row R (Note: if N=1, no copy/insert is required)
3. Find grand child(ren) for each child and corresponding Qty Per in the single level BOM Table and copy/paste into Grand Child / Grand Child Qty per columns
4. Repeat steps 1-3 for Great Grand Children and Great Great Grand Children


[TABLE="width: 774"]
<tbody>[TR]
[TD]Parent
[/TD]
[TD]Child
[/TD]
[TD]Child Qty Per
[/TD]
[TD]Grand Child
[/TD]
[TD]Grand Child Qty per
[/TD]
[TD]Great Grand Child
[/TD]
[TD]Great Grand Child Qty Per
[/TD]
[TD]Great Great Grand Child
[/TD]
[TD]Great Great Grand Child Qty Per
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A1
[/TD]
[TD]1
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A111
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A1
[/TD]
[TD]1
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]2
[/TD]
[TD]PART-A1121
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A1
[/TD]
[TD]1
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]2
[/TD]
[TD]PART-A1122
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A1121
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A2
[/TD]
[TD]2
[/TD]
[TD]PART-A21
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A2
[/TD]
[TD]2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A2
[/TD]
[TD]2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]4
[/TD]
[TD]PART-A1121
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A2
[/TD]
[TD]2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]4
[/TD]
[TD]PART-A1122
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A2
[/TD]
[TD]2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]PART-A221
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A2
[/TD]
[TD]2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]PART-A222
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A2
[/TD]
[TD]2
[/TD]
[TD]PART-A221
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A
[/TD]
[TD]PART-A222
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A1
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A111
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A1
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]2
[/TD]
[TD]PART-A1121
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A1
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]2
[/TD]
[TD]PART-A1122
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A11
[/TD]
[TD]PART-A111
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A11
[/TD]
[TD]PART-A112
[/TD]
[TD]2
[/TD]
[TD]PART-A1121
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A11
[/TD]
[TD]PART-A112
[/TD]
[TD]2
[/TD]
[TD]PART-A1122
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A112
[/TD]
[TD]PART-A1121
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A112
[/TD]
[TD]PART-A1122
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A2
[/TD]
[TD]PART-A21
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]4
[/TD]
[TD]PART-A1121
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]4
[/TD]
[TD]PART-A1122
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]PART-A221
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A2
[/TD]
[TD]PART-A22
[/TD]
[TD]2
[/TD]
[TD]PART-A222
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A2
[/TD]
[TD]PART-A221
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A22
[/TD]
[TD]PART-A112
[/TD]
[TD]4
[/TD]
[TD]PART-A1121
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A22
[/TD]
[TD]PART-A112
[/TD]
[TD]4
[/TD]
[TD]PART-A1122
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A22
[/TD]
[TD]PART-A221
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-A22
[/TD]
[TD]PART-A222
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-B
[/TD]
[TD]PART-A1
[/TD]
[TD]2
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A111
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]PART-B
[/TD]
[TD]PART-A1
[/TD]
[TD]2
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]2
[/TD]
[TD]PART-A1121
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]PART-B
[/TD]
[TD]PART-A1
[/TD]
[TD]2
[/TD]
[TD]PART-A11
[/TD]
[TD]2
[/TD]
[TD]PART-A112
[/TD]
[TD]2
[/TD]
[TD]PART-A1122
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]PART-B
[/TD]
[TD]PART-A21
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
You must log in or register to reply here.

Similar threads

K
  • Solved
Need help consolidating data from 3 columns. I have a VBA code for QTY but the last column will now be data instead, how do I change code?
2
Replies
10
Views
836
rlv01
R
S
  • Solved
Modify cell from "parent" row if "child" cell has negative value
Replies
5
Views
467
Peter_SSs
Peter_SSs
E
  • Solved
How do I get sum product to ignore empty cells to calculate the cost basis of shares?
Replies
5
Views
332
Peter_SSs
Peter_SSs
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
A
  • Solved
error generated in complete code but when run on its own.
Replies
9
Views
739
Alex Blakenburg
A

Forum statistics

Threads
1,223,723
Messages
6,174,113
Members
452,544
Latest member
aush

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