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]

Here a recursive VBA approach. Paste this code in a new module. I used below dataset to test.
Paste the data in A1 first.

Dim j As Long, aData, xp, aBom

Sub jec()
 Dim it As Variant
 j = 0
 aData = Sheets("Data").Cells(1).CurrentRegion
 Set dic = CreateObject("scripting.dictionary")
 For t = 2 To UBound(aData)
    dic(aData(t, 1)) = Empty
 Next
 ReDim aBom(1 To 50000, 1 To 9)
 For Each it In dic.keys
    xp = it
    GetBom it, 1
 Next
 Sheets("Data").Range("m1").Resize(, 9) = Array("Lvl", "Item", "Seq", "Stepname", "LineNr", "CodeType", "Description", "Unit", "Qty")
 Sheets("Data").Range("m2").Resize(UBound(aBom), 9) = aBom
End Sub

Private Sub GetBom(sPart As Variant, iLvl As Integer)
 Dim i As Long, c As Long
 For i = 2 To UBound(aData)
    If aData(i, 1) = sPart Then
      j = j + 1
      If aData(i, 1) = xp Then
        aBom(j, 1) = iLvl
        aBom(j, 2) = xp
        aBom(j + 1, 1) = iLvl + 1
        aBom(j + 1, 2) = Space(iLvl * 10) & aData(i, 6)
        j = j + 1
        xp = ""
      Else
        aBom(j, 2) = Space(iLvl * 10) & aData(i, 6)
        aBom(j, 1) = iLvl + 1
      End If
      For c = 2 To 8
        aBom(j, c + 1) = aData(i, IIf(c > 5, c + 1, c))
      Next
        GetBom aData(i, 6), iLvl + 1
    End If
 Next
End Sub

ITEMCODE	STEPSEQUENCE	STEPNAME	LINENUMBER	CODETYPE	LINECODE	LINEDESCRIPTION	LINEUNIT	PERQTY
CLM0022SBA-SM	900​	Assembly	85​	Item Code	CLM0337CB	255mm Pitch Footing Channel Assembly	Each	1​
CLM0022SBA-SM	190​	PACK LIST	20​	Item Code	FST013	NUT NYLOC M10 Zinc (2000 QTY)	Each	4​
CLM0022SBA-SM	900​	Assembly	40​	Item Code	PNL1688SBA	PS Hand Grip Assy (Pair) (AS4685:2021)	Each	1​
CLM0022SBA-SM	190​	PACK LIST	10​	Item Code	CAP0023CM-BLACK	NUT COVER & CAP PLASTIC - 10MM - Black	Each	4​
CLM0022SBA-SM	900​	Assembly	90​	Item Code	SHT0738CBC	Pfm Support Plate 5mm SS - for Cable Rope Climbers Pcoated	Each	1​
CLM0022SBA-SM	190​	PACK LIST	90​	Labour Code	PACKING	Packing	Minutes	20​
CLM0022SBA-SM	190​	PACK LIST	50​	Item Code	FST2000	M14 Square Washer Galv - 50x50x3.0	Each	2​
CLM0022SBA-SM	190​	PACK LIST	30​	Item Code	FST142	Excalibur Screwbolt 12 x 100 DSA 12-100-G Incl. Gal Washer (Dome Head)	Each	2​
CLM0022SBA-SM	200​	DISPATCH ASSY	90​	Labour Code	DISPATCH ASSY	Assemble as per Picklist	Minutes	30​
CLM0022SBA-SM	190​	PACK LIST	25​	Item Code	FST0034CM	WASHER FLAT S/S 3/8 X 3/4 X 18" (5000 QTY)	Each	4​
CLM0022SBA-SM	900​	Assembly	80​	Item Code	ROP0389CB	Cable Rope for Climber Net 1200 - Chain Footing	Each	1​
CLM0337CB	120​	DISPATCH ASSY	40​	Item Code	FST2001	M10 Large Washer S/S 304 - Ø30OD	Each	4​
CLM0337CB	120​	DISPATCH ASSY	10​	Item Code	CLM0337CM	255mm Pitch Footing Channel	Each	1​
CLM0337CB	120​	DISPATCH ASSY	20​	Item Code	KRP5040	Chain Boss Flat Surface M10 316 SS	Each	4​
CLM0337CB	120​	DISPATCH ASSY	30​	Item Code	FST0320	SCREW BUTTON TORX S/S M8 X 25 (QTY1000)	Each	4​
CLM0337CB	120​	DISPATCH ASSY	50​	Item Code	FST0002CM	SCREW BUTTON TORX S/S M10 X 16 (TW 111051) (800 QTY)	Each	4​
CLM0337CB	120​	DISPATCH ASSY	60​	Labour Code	DISPATCH ASSY	Assemble as per Picklist	Minutes	10​
CLM0337CM	910​	FABRICATION	20​	Labour Code	FABRICATION ASSY	Assembly Fabrication	Minutes	10​
CLM0337CM	910​	FABRICATION	50​	Item Code	CLM0331CM	Universal Footing Channel Type 1	Each	1​
CST1001SB	20​	EXT MACHINE	30​	Labour Code	HANDLING	Handling	Minutes	1​
CST1001SB	20​	EXT MACHINE	40​	Item Code	CST0001SM	PS 27 Single Hole Flange (Flat)(AP-005)	Each	1​
CST1001SB	20​	EXT MACHINE	50​	Descriptor Code	CST1001SE	Machine CST0001SM PS 27 Single Hole Flange (Flat)	Each	1​
FST0001CB	10​	PACK LIST	10​	Item Code	FST0005CM	SCREW BUTTON TORX S/S M10 X 40 (TW - 18999) (2000 QTY)	Each	1​
FST0001CB	190​	DISPATCH ASSY	20​	Item Code	FST009	NUTSERT M10 SPLINED ZP (TW-17893) - Grip Range .78mm to 3.8mm	Each	1​
FST0001CB	190​	DISPATCH ASSY	30​	Labour Code	UPRIGHT ASSY	Nutsert and Cap Fitment	Minutes	0.5
FST0001CB	10​	PACK LIST	60​	Labour Code	PACKING	Packing	Minutes	0.5
FST0001CB	20​	CUT LIST	70​	Labour Code	CUT AND DRILL	Cut and Drill Metal	Minutes	1​
FST0223-L25	130​	FABRICATION	10​	Item Code	FST0223CM	Allthread M10 x 1000 SS 316	Each	0.03
FST0223-L25	130​	FABRICATION	20​	Labour Code	FABRICATION ASSY	Assembly Fabrication	Minutes	5​
PNL0686SB	100​	FABRICATION	10​	Item Code	PBN0395CM	27 OD Hand Grip Inner Bend (AS4685:2021)	Each	1​
PNL0686SB	100​	FABRICATION	20​	Item Code	FST0009CM	SCREW BUTTON TORX S/S M8 X 20	Each	1​
PNL0686SB	100​	FABRICATION	30​	Item Code	CST0001SM	PS 27 Single Hole Flange (Flat)(AP-005)	Each	1​
PNL0686SB	100​	FABRICATION	50​	Labour Code	FABRICATION ASSY	Fabricated Assembly	Minutes	15​
PNL0686SBC	20​	POWDERCOAT	10​	Item Code	PNL0686SB	PS Hand Grip Inner (AS4685:2021)	Each	1​
PNL0686SBC	20​	POWDERCOAT	20​	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	4​
PNL0687SB	100​	FABRICATION	60​	Item Code	FST0009CM	SCREW BUTTON TORX S/S M8 X 20	Each	2​
PNL0687SB	100​	FABRICATION	10​	Item Code	PBN0396CM	27 OD Hand Grip Outer Bend (AS4685:2021)	Each	1​
PNL0687SB	100​	FABRICATION	30​	Item Code	CST1001SB	PS 27 Single Hole Flange (Flat) Machined for 3 screws	Each	1​
PNL0687SB	100​	FABRICATION	50​	Labour Code	FABRICATION ASSY	Fabricated Assembly	Minutes	15​
PNL0687SB	100​	FABRICATION	70​	Item Code	FST165	M8 X 16 RTOX (1000 QTY)	Each	1​
PNL0687SBC	20​	POWDERCOAT	10​	Item Code	PNL0687SB	PS Hand Grip Outer (AS4685:2021)	Each	1​
PNL0687SBC	20​	POWDERCOAT	20​	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	6​
PNL1688SBA	900​	Assembly	10​	Item Code	PNL0686SBC	PS Hand Grip Inner (AS4685:2021) Pcoated	Each	2​
PNL1688SBA	190​	PACK LIST	20​	Item Code	FST0034CM	WASHER FLAT S/S 3/8 X 3/4 X 18" (5000 QTY)	Each	4​
PNL1688SBA	900​	Assembly	50​	Item Code	FST0001CB	PR/PS Nutsert M10 x 40 Assy	Each	4​
PNL1688SBA	190​	PACK LIST	60​	Item Code	FST0004CM	SCREW BUTTON TORX S/S M10 X 25 (800 QTY)	Each	4​
PNL1688SBA	190​	PACK LIST	30​	Labour Code	PACKING	Packing	Minutes	15​
PNL1688SBA	900​	Assembly	70​	Item Code	FST0019CM	Tube Closure - Plastic 27 x M10 (N782110MMB)	Each	4​
PNL1688SBA	900​	Assembly	80​	Item Code	PNL0687SBC	PS Hand Grip Outer (AS4685:2021) Pcoated	Each	2​
ROP0389CB	70​	ROPE LIST	140​	Item Code	CHN0100CM	Chain 6mm Short Link ST/STEEL	M	2.4
ROP0389CB	70​	ROPE LIST	10​	Item Code	ROP0012CM	Cross Joint Ferrule (GC16-C)	Each	6​
ROP0389CB	70​	ROPE LIST	20​	Item Code	ROP0014CM	Short Eye Ferrule (GC16-EK - 24mm)	Each	4​
ROP0389CB	70​	ROPE LIST	30​	Item Code	ROP0018CM	Short T-Cross Joint Ferrule (GC16-TCK)	Each	6​
ROP0389CB	70​	ROPE LIST	50​	Item Code	FST0219CM	10G x 2" Csunk Square Drive Quad Screw SS	Each	12​
ROP0389CB	70​	ROPE LIST	60​	Item Code	ROP0001CM-BLACK	Combi Rope 16 (GC16-RMU) (Black)	M	7.3
ROP0389CB	70​	ROPE LIST	150​	Labour Code	ROPE	Rope Assembly	Minutes	115​
ROP0389CB	70​	ROPE LIST	80​	Item Code	ROP0022CM	Thimble 14mm to Suit 16mm Rope SS	Each	4​
ROP0389CB	70​	ROPE LIST	90​	Item Code	FST0346	M10 x 25 EYE BOLT 23MM EYE DIA SS-580-10	Each	4​
ROP0389CB	70​	ROPE LIST	100​	Item Code	ROP0010CM	Bolt Joint Ferrule Long 55mm (GC16-B)	Each	4​
ROP0389CB	70​	ROPE LIST	110​	Item Code	FST0223-L25	Allthread M10 SS 316 - Cut to Length 25mm	Each	4​
ROP0389CB	70​	ROPE LIST	120​	Item Code	KRP5040	Chain Boss Flat Surface M10 316 SS	Each	4​
ROP0389CB	70​	ROPE LIST	130​	Item Code	FST0320	SCREW BUTTON TORX S/S M8 X 25 (QTY1000)	Each	4​
SHT0738CBC	180​	POWDERCOAT	20​	Item Code	SHT0738CM	Pfm Support Plate 5mm SS - for Cable Rope Climbers	Each	1​
SHT0738CBC	180​	POWDERCOAT	20​	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	5​
SHT0738CBC	910​	EXT PREP	30​	Descriptor Code	FACTORY - EXTERNAL PREP	External Preperation for Powdercoating	$	1​

Wow, this works perfectly!!! The only issue is that I can only get it to work to 17,000 lines (which results in 40,000+ multi-level lines). I have managed to reduce our report to 73,000 lines so I'm expecting approx. 150,000-200,000 lines. I get the below error when I increase the lines to 18,000 or more. Is there anything that can be done? Would more CPU help or is it a limitation with the VBA..? I sincerely hope there is something that can be done because this completely resolves the issue. Thanks heaps for getting it to this stage!

 

[JEC]

First thing to try. Change these lines

ReDim aBom(1 To 300000, 1 To 9)

and

Private Sub GetBom(sPart As Variant, iLvl As Long)

 

[Andy Howcroft]

First thing to try. Change these lines

ReDim aBom(1 To 300000, 1 To 9)

and

Private Sub GetBom(sPart As Variant, iLvl As Long)

Same issue I'm afraid, "Out of stack space" - I tried reducing the 300000 to 200000 but no change

 

[JEC]

It probably has too many nested functions. This is something you have with recursion… maybe this weekend I have time to test a little.

 

[Akuini]

6. Yes, assumption correct - Note: a product is always a parent.

So, if an item exists in both col ItemCode & LineCode then it's not a product, right?
For example: CLM0337CB in the table in post #8, it's not a product, right?
However, when I tried @JEC code in post #18, CLM0337CB is considered as a product.:

Andy Howcroft - Multi-level Bill Of Materials explosion.xlsm
	M	N	O	P	Q	R	S	T	U
68	1	CLM0337CB
69	2	FST2001	120	DISPATCH ASSY	40	Item Code	M10 Large Washer S/S 304 - Ø30OD	Each	4
70	2	CLM0337CM	120	DISPATCH ASSY	10	Item Code	255mm Pitch Footing Channel	Each	1
71	3	FABRICATION ASSY	910	FABRICATION	20	Labour Code	Assembly Fabrication	Minutes	10
72	3	CLM0331CM	910	FABRICATION	50	Item Code	Universal Footing Channel Type 1	Each	1
73	2	KRP5040	120	DISPATCH ASSY	20	Item Code	Chain Boss Flat Surface M10 316 SS	Each	4
74	2	FST0320	120	DISPATCH ASSY	30	Item Code	SCREW BUTTON TORX S/S M8 X 25 (QTY1000)	Each	4
75	2	FST0002CM	120	DISPATCH ASSY	50	Item Code	SCREW BUTTON TORX S/S M10 X 16 (TW 111051) (800 QTY)	Each	4
76	2	DISPATCH ASSY	120	DISPATCH ASSY	60	Labour Code	Assemble as per Picklist	Minutes	10
Sheet5

 

[Andy Howcroft]

So, if an item exists in both col ItemCode & LineCode then it's not a product, right?
For example: CLM0337CB in the table in post #8, it's not a product, right?
However, when I tried @JEC code in post #18, CLM0337CB is considered as a product.:

Andy Howcroft - Multi-level Bill Of Materials explosion.xlsm
	M	N	O	P	Q	R	S	T	U
68	1	CLM0337CB
69	2	FST2001	120	DISPATCH ASSY	40	Item Code	M10 Large Washer S/S 304 - Ø30OD	Each	4
70	2	CLM0337CM	120	DISPATCH ASSY	10	Item Code	255mm Pitch Footing Channel	Each	1
71	3	FABRICATION ASSY	910	FABRICATION	20	Labour Code	Assembly Fabrication	Minutes	10
72	3	CLM0331CM	910	FABRICATION	50	Item Code	Universal Footing Channel Type 1	Each	1
73	2	KRP5040	120	DISPATCH ASSY	20	Item Code	Chain Boss Flat Surface M10 316 SS	Each	4
74	2	FST0320	120	DISPATCH ASSY	30	Item Code	SCREW BUTTON TORX S/S M8 X 25 (QTY1000)	Each	4
75	2	FST0002CM	120	DISPATCH ASSY	50	Item Code	SCREW BUTTON TORX S/S M10 X 16 (TW 111051) (800 QTY)	Each	4
76	2	DISPATCH ASSY	120	DISPATCH ASSY	60	Labour Code	Assemble as per Picklist	Minutes	10
Sheet5

Apologies, the Product, parent thing may have confused you. The essential thing is to retrieve all the BOMs as multi-level - some will be Products but must will be assemblies etc

 

[Akuini]

Apologies, the Product, parent thing may have confused you. The essential thing is to retrieve all the BOMs as multi-level - some will be Products but must will be assemblies etc

Sorry, I don't understand your wording: "some will be Products but must will be assemblies etc"
It's indeed confusing because your wording differs from the example you provided.
1. In post #1, the result only shows 'Product 1' and 'Product 2' as Level 1. However, there are no 'Assembly 1' to 'Assembly 4' displayed as Level 1. Are you suggesting that the result was actually incomplete, because there should be 'Assembly 1' to 'Assembly 4' displayed as Level 1 (not just as Level 2)?

2. The error you described in post #23, what was the value of iLv1 when the code stopped?

 

[Akuini]

2. The error you described in post #23, what was the value of iLv1 when the code stopped?

Your data probably have 2-way hierarchy, something like:
A > B
B > A

 

[Andy Howcroft]

It probably has too many nested functions. This is something you have with recursion… maybe this weekend I have time to test a little.

Hi - Just wondering if you managed to get chance to have a look at this one again...?

 

[JEC]

I don’t have much time at the moment, unfortunately