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.

 

[Herakles]

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:
View attachment 99811


Required multi-level report with BOM levels shown for each assembly/part:
View attachment 99812

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.

Are you able to send us some real data using XL2BB?

Do you have any other columns in your data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kevin9999]

This is very embryonic, but I thought I'd throw it out there as a starting point. I would very much like to see your actual data via the XL2BB add in, or perhaps you could share your file via Google Drive, dropbox or similar file sharing platform to test the below with a more realistic data set. Obviously a lot of tidying up to do, this is just a start.

Option Explicit
Sub Parent_Child()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")       '<-- *** Change to actual sheet names ***
    
    Dim a, b, z
    Dim i As Long, j As Long, k As Long, n As Long
    Dim c As Range, x As String, y As String
    Dim LRow As Long
    LRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    
    ws1.Range("J:Z").Clear
    ws1.Range("K2").Formula2R1C1 = _
    "=UNIQUE(FILTER(R2C1:R" & LRow & "C1,COUNTIF(R2C2:R" & LRow & "C2,R2C1:R" & LRow & "C1)<1))"
    With ws1.Range("K2#")
        .Value = .Value
        .Offset(, -1) = 1
        .Offset(, 1) = 1
    End With
    
    n = 1
    a = ws1.Range("A2", ws1.Cells(Rows.Count, "C").End(xlUp))
    For Each c In ws1.Range("K2:K" & LRow)
        x = c
        For i = 1 To UBound(a, 1)
            If a(i, 1) = x Then
                With c
                    .Offset(n, -4).Resize(, 6).Insert xlShiftDown
                    .Offset(n).Value = a(i, 2)
                    .Offset(n, 1).Value = a(i, 3)
                End With
                n = n + 1
            End If
        Next i
        n = 1
    Next c
    
    Dim child
    child = ws1.Range("J2", ws1.Cells(Rows.Count, "K").End(xlUp))
    
    For i = 1 To UBound(a, 1)
        x = a(i, 2)
        y = a(i, 1)
        For j = 1 To UBound(child, 1)
            If child(j, 2) = y Then
                n = child(j, 1)
                Exit For
            End If
        Next j
        For k = 1 To UBound(child, 1)
            If child(k, 2) = x And IsEmpty(child(k, 1)) Then
                child(k, 1) = n + 1
                Exit For
            End If
        Next k
    Next i
    
    For i = UBound(child, 1) To 1 Step -1
        If IsEmpty(child(i, 1)) Then
            x = child(i, 2)
            For j = UBound(a, 1) To 1 Step -1
                If a(j, 2) = x Then
                    y = a(j, 1)
                    For k = UBound(child, 1) To 1 Step -1
                        If child(k, 2) = y Then
                            n = child(k, 1)
                            child(i, 1) = n + 1
                            Exit For
                        End If
                    Next k
                End If
            Next j
        End If
    Next i
    
    ws1.Range("J2").Resize(UBound(child, 1), 2).Value = child
    n = Application.Max(ws1.Range("J:J")) - 1
    ws1.Range("K1").Resize(1, 2).Value = Array("ITEM CODE", "QTY")
    ws1.Range("K1").EntireColumn.Resize(, n).Insert
    
    z = ws1.Range("J2").Resize(UBound(child, 1), n + 1)
    For i = 1 To UBound(z, 1)
        n = z(i, 1)
        z(i, 1) = ""
        z(i, n) = n
    Next i
    
    ws1.Range("J2").Resize(UBound(z, 1), UBound(z, 2)).Value = z
    ws1.Range("J1").Resize(, 25).EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

Before:

Parent Child.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Parent	Child	Qty
2	Product 1	Assembly 1	2
3	Product 1	Assembly 3	1
4	Assembly 1	Part 1	4
5	Assembly 1	Part 2	1
6	Assembly 1	Assembly 2	6
7	Assembly 2	Part 4	1
8	Assembly 2	Part 5	3
9	Assembly 3	Part 6	1
10	Assembly 3	Part 7	1
11	Assembly 3	Part 8	1
12	Assembly 3	Part 9	2
13	Product 2	Assembly 2	1
14	Product 2	Assembly 4	5
15	Product 2	Part 10	2
16	Assembly 4	Part 4	4
17	Assembly 4	Part 8	1
18
Sheet1

After:

Parent Child.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Parent	Child	Qty											ITEM CODE	QTY
2	Product 1	Assembly 1	2							1				Product 1	1
3	Product 1	Assembly 3	1								2			Assembly 1	2
4	Assembly 1	Part 1	4									3		Part 1	4
5	Assembly 1	Part 2	1									3		Part 2	1
6	Assembly 1	Assembly 2	6									3		Assembly 2	6
7	Assembly 2	Part 4	1										4	Part 4	1
8	Assembly 2	Part 5	3										4	Part 5	3
9	Assembly 3	Part 6	1								2			Assembly 3	1
10	Assembly 3	Part 7	1									3		Part 6	1
11	Assembly 3	Part 8	1									3		Part 7	1
12	Assembly 3	Part 9	2									3		Part 8	1
13	Product 2	Assembly 2	1									3		Part 9	2
14	Product 2	Assembly 4	5							1				Product 2	1
15	Product 2	Part 10	2								2			Assembly 2	1
16	Assembly 4	Part 4	4									3		Part 4	1
17	Assembly 4	Part 8	1									3		Part 5	3
18											2			Assembly 4	5
19												3		Part 4	4
20												3		Part 8	1
21											2			Part 10	2
22
Sheet1

 

[Andy Howcroft]

This is very embryonic, but I thought I'd throw it out there as a starting point. I would very much like to see your actual data via the XL2BB add in, or perhaps you could share your file via Google Drive, dropbox or similar file sharing platform to test the below with a more realistic data set. Obviously a lot of tidying up to do, this is just a start.

Option Explicit
Sub Parent_Child()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")       '<-- *** Change to actual sheet names ***
   
    Dim a, b, z
    Dim i As Long, j As Long, k As Long, n As Long
    Dim c As Range, x As String, y As String
    Dim LRow As Long
    LRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
   
    ws1.Range("J:Z").Clear
    ws1.Range("K2").Formula2R1C1 = _
    "=UNIQUE(FILTER(R2C1:R" & LRow & "C1,COUNTIF(R2C2:R" & LRow & "C2,R2C1:R" & LRow & "C1)<1))"
    With ws1.Range("K2#")
        .Value = .Value
        .Offset(, -1) = 1
        .Offset(, 1) = 1
    End With
   
    n = 1
    a = ws1.Range("A2", ws1.Cells(Rows.Count, "C").End(xlUp))
    For Each c In ws1.Range("K2:K" & LRow)
        x = c
        For i = 1 To UBound(a, 1)
            If a(i, 1) = x Then
                With c
                    .Offset(n, -4).Resize(, 6).Insert xlShiftDown
                    .Offset(n).Value = a(i, 2)
                    .Offset(n, 1).Value = a(i, 3)
                End With
                n = n + 1
            End If
        Next i
        n = 1
    Next c
   
    Dim child
    child = ws1.Range("J2", ws1.Cells(Rows.Count, "K").End(xlUp))
   
    For i = 1 To UBound(a, 1)
        x = a(i, 2)
        y = a(i, 1)
        For j = 1 To UBound(child, 1)
            If child(j, 2) = y Then
                n = child(j, 1)
                Exit For
            End If
        Next j
        For k = 1 To UBound(child, 1)
            If child(k, 2) = x And IsEmpty(child(k, 1)) Then
                child(k, 1) = n + 1
                Exit For
            End If
        Next k
    Next i
   
    For i = UBound(child, 1) To 1 Step -1
        If IsEmpty(child(i, 1)) Then
            x = child(i, 2)
            For j = UBound(a, 1) To 1 Step -1
                If a(j, 2) = x Then
                    y = a(j, 1)
                    For k = UBound(child, 1) To 1 Step -1
                        If child(k, 2) = y Then
                            n = child(k, 1)
                            child(i, 1) = n + 1
                            Exit For
                        End If
                    Next k
                End If
            Next j
        End If
    Next i
   
    ws1.Range("J2").Resize(UBound(child, 1), 2).Value = child
    n = Application.Max(ws1.Range("J:J")) - 1
    ws1.Range("K1").Resize(1, 2).Value = Array("ITEM CODE", "QTY")
    ws1.Range("K1").EntireColumn.Resize(, n).Insert
   
    z = ws1.Range("J2").Resize(UBound(child, 1), n + 1)
    For i = 1 To UBound(z, 1)
        n = z(i, 1)
        z(i, 1) = ""
        z(i, n) = n
    Next i
   
    ws1.Range("J2").Resize(UBound(z, 1), UBound(z, 2)).Value = z
    ws1.Range("J1").Resize(, 25).EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

Before:

Parent Child.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Parent	Child	Qty
2	Product 1	Assembly 1	2
3	Product 1	Assembly 3	1
4	Assembly 1	Part 1	4
5	Assembly 1	Part 2	1
6	Assembly 1	Assembly 2	6
7	Assembly 2	Part 4	1
8	Assembly 2	Part 5	3
9	Assembly 3	Part 6	1
10	Assembly 3	Part 7	1
11	Assembly 3	Part 8	1
12	Assembly 3	Part 9	2
13	Product 2	Assembly 2	1
14	Product 2	Assembly 4	5
15	Product 2	Part 10	2
16	Assembly 4	Part 4	4
17	Assembly 4	Part 8	1
18
Sheet1

After:

Parent Child.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Parent	Child	Qty											ITEM CODE	QTY
2	Product 1	Assembly 1	2							1				Product 1	1
3	Product 1	Assembly 3	1								2			Assembly 1	2
4	Assembly 1	Part 1	4									3		Part 1	4
5	Assembly 1	Part 2	1									3		Part 2	1
6	Assembly 1	Assembly 2	6									3		Assembly 2	6
7	Assembly 2	Part 4	1										4	Part 4	1
8	Assembly 2	Part 5	3										4	Part 5	3
9	Assembly 3	Part 6	1								2			Assembly 3	1
10	Assembly 3	Part 7	1									3		Part 6	1
11	Assembly 3	Part 8	1									3		Part 7	1
12	Assembly 3	Part 9	2									3		Part 8	1
13	Product 2	Assembly 2	1									3		Part 9	2
14	Product 2	Assembly 4	5							1				Product 2	1
15	Product 2	Part 10	2								2			Assembly 2	1
16	Assembly 4	Part 4	4									3		Part 4	1
17	Assembly 4	Part 8	1									3		Part 5	3
18											2			Assembly 4	5
19												3		Part 4	4
20												3		Part 8	1
21											2			Part 10	2
22
Sheet1

Hi kevin9999,

This is an amazing start, thank you so much!! The code works perfectly on the small set of sample data and the fact that the BOM level increases whenever required is fantastic - very clever!

Unfortunately, the code doesn't work perfectly on larger examples, which I assume you expected as you mentioned "very embryonic" in your reply . I have applied the macro to one of our medium sized BOMs and more than half of the lines were correct, great start . You can find the file via the link below. I have shown the actual report data for one of our products (tab "CLM0022SBA-SM SINGLE LEVEL"), and all the subsequent subassemblies and parts that are required for the BOM. I have then manually created the multi-level/exploded BOM (tab "CLM0022SBA-SM EXPLODED") using the original data.

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

After running the macro against the original data I have shown the results to the right of the manually exploded BOM (column R onwards) and compared the two. All lines with the correct BOM level have the BOM level highlighted green, incorrect lines in red and the correct level in yellow. This is a fantastic first test that I'm hoping you will be able to improve on given the extra info. Also, as you can see there is more line data, specific to the items - if we could pull these across as well that would be amazing. There isn't any info on the level 1 item (i.e. CLM0022SBA-SM), but just to bring across the code and a quantity of "1" will suffice.

Let me know if you have any issues with the link or require any further info/data.

Thanks heaps in advance!

 

[Andy Howcroft]

Are you able to send us some real data using XL2BB?

Do you have any other columns in your data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Herakles,

Thanks for your reply. I have replied to kevin9999's post with further information and a link to more data .

 

[kevin9999]

Hi Andy,
I knew that the method I applied in post #3 would prove to be impractical on large data sets. I was hoping to convert the principle (at least) to an in-memory approach via arrays/collections/dictionaries etc. but unfortunately I've hit a brick wall getting the flow of logic right. I'm not certain I will ever be able to get a workable solution for you, and sincerely hope that those on the forum more advanced than me will come to the rescue. In the meantime, I'd still like to look at the actual data - but you need to make shared file available to anyone with the link (the link you provided is 'restricted'.
Good luck!

 

[Herakles]

Hi Herakles,

Thanks for your reply. I have replied to kevin9999's post with further information and a link to more data .

I've been looking into this and have started to developed a recursive approach to report on the multiple levels.

Questions.

1. Do you want the assemblies to be on the report in the order in whcih they appear in the BOM or the order in which they are used? Your example report shows the latter.

2.. Will an assembly always have the same parts in it with the same quantities? If this is the case then a table of assemblies and parts can be put together
and then referred to when necessary instead of gleaning this repeatedly from the BOM.

3. Can you please use XL2BB to provide some real codes? I don't know if there us anything in the code which identifies it as a product, assembly or a part.

4. I have assumed that a Child is a Part if it is not a Parent. Is this a correct asumption?

5. I have assumed that a Child is an Assembly is if it is a Parent. Is this a correct asumption?

6. I have assumed that a Product is a Parent if it is not a Child. Is this a correct asumption?

 

[Andy Howcroft]

I've been looking into this and have started to developed a recursive approach to report on the multiple levels.

Questions.

1. Do you want the assemblies to be on the report in the order in whcih they appear in the BOM or the order in which they are used? Your example report shows the latter.

2.. Will an assembly always have the same parts in it with the same quantities? If this is the case then a table of assemblies and parts can be put together
and then referred to when necessary instead of gleaning this repeatedly from the BOM.

3. Can you please use XL2BB to provide some real codes? I don't know if there us anything in the code which identifies it as a product, assembly or a part.

4. I have assumed that a Child is a Part if it is not a Parent. Is this a correct asumption?

5. I have assumed that a Child is an Assembly is if it is a Parent. Is this a correct asumption?

6. I have assumed that a Product is a Parent if it is not a Child. Is this a correct asumption?

Hi Herakles,

Many thanks for looking into this one, any help is greatly appreciated. In response to your queries:

1. Please see my multi-level BOM example in XL2BB format at the foot of this message - this should answer this one.

2. Yes, an assembly will always have the same parts with the same quantities. We have a report from our ERP system that shows all BOMs in the system and their subsequent children. See the example below for which I have manually included all the required subassemblies and child parts for our product "CLM0022SBA-SM".

3. Please see the below XL2BB examples:



Below is the original single level BOM report for one of our medium sized BOMs that we can export from our current system. The Parent, Child and Qty columns are headed in blue.

MrExcel VBA Test.xlsm
	A	B	C	D	E	F	G	H	I
1	PARENT					CHILD			QTY
2	ITEMCODE	STEPSEQUENCE	STEPNAME	LINENUMBER	CODETYPE	LINECODE	LINEDESCRIPTION	LINEUNIT	PERQTY
3	CLM0022SBA-SM	900	Assembly	85	Item Code	CLM0337CB	255mm Pitch Footing Channel Assembly	Each	1
4	CLM0022SBA-SM	190	PACK LIST	20	Item Code	FST013	NUT NYLOC M10 Zinc (2000 QTY)	Each	4
5	CLM0022SBA-SM	900	Assembly	40	Item Code	PNL1688SBA	PS Hand Grip Assy (Pair) (AS4685:2021)	Each	1
6	CLM0022SBA-SM	190	PACK LIST	10	Item Code	CAP0023CM-BLACK	NUT COVER & CAP PLASTIC - 10MM - Black	Each	4
7	CLM0022SBA-SM	900	Assembly	90	Item Code	SHT0738CBC	Pfm Support Plate 5mm SS - for Cable Rope Climbers Pcoated	Each	1
8	CLM0022SBA-SM	190	PACK LIST	90	Labour Code	PACKING	Packing	Minutes	20
9	CLM0022SBA-SM	190	PACK LIST	50	Item Code	FST2000	M14 Square Washer Galv - 50x50x3.0	Each	2
10	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
11	CLM0022SBA-SM	200	DISPATCH ASSY	90	Labour Code	DISPATCH ASSY	Assemble as per Picklist	Minutes	30
12	CLM0022SBA-SM	190	PACK LIST	25	Item Code	FST0034CM	WASHER FLAT S/S 3/8 X 3/4 X 18" (5000 QTY)	Each	4
13	CLM0022SBA-SM	900	Assembly	80	Item Code	ROP0389CB	Cable Rope for Climber Net 1200 - Chain Footing	Each	1
14	CLM0337CB	120	DISPATCH ASSY	40	Item Code	FST2001	M10 Large Washer S/S 304 - Ø30OD	Each	4
15	CLM0337CB	120	DISPATCH ASSY	10	Item Code	CLM0337CM	255mm Pitch Footing Channel	Each	1
16	CLM0337CB	120	DISPATCH ASSY	20	Item Code	KRP5040	Chain Boss Flat Surface M10 316 SS	Each	4
17	CLM0337CB	120	DISPATCH ASSY	30	Item Code	FST0320	SCREW BUTTON TORX S/S M8 X 25 (QTY1000)	Each	4
18	CLM0337CB	120	DISPATCH ASSY	50	Item Code	FST0002CM	SCREW BUTTON TORX S/S M10 X 16 (TW 111051) (800 QTY)	Each	4
19	CLM0337CB	120	DISPATCH ASSY	60	Labour Code	DISPATCH ASSY	Assemble as per Picklist	Minutes	10
20	CLM0337CM	910	FABRICATION	20	Labour Code	FABRICATION ASSY	Assembly Fabrication	Minutes	10
21	CLM0337CM	910	FABRICATION	50	Item Code	CLM0331CM	Universal Footing Channel Type 1	Each	1
22	CST1001SB	20	EXT MACHINE	30	Labour Code	HANDLING	Handling	Minutes	1
23	CST1001SB	20	EXT MACHINE	40	Item Code	CST0001SM	PS 27 Single Hole Flange (Flat)(AP-005)	Each	1
24	CST1001SB	20	EXT MACHINE	50	Descriptor Code	CST1001SE	Machine CST0001SM PS 27 Single Hole Flange (Flat)	Each	1
25	FST0001CB	10	PACK LIST	10	Item Code	FST0005CM	SCREW BUTTON TORX S/S M10 X 40 (TW - 18999) (2000 QTY)	Each	1
26	FST0001CB	190	DISPATCH ASSY	20	Item Code	FST009	NUTSERT M10 SPLINED ZP (TW-17893) - Grip Range .78mm to 3.8mm	Each	1
27	FST0001CB	190	DISPATCH ASSY	30	Labour Code	UPRIGHT ASSY	Nutsert and Cap Fitment	Minutes	0.5
28	FST0001CB	10	PACK LIST	60	Labour Code	PACKING	Packing	Minutes	0.5
29	FST0001CB	20	CUT LIST	70	Labour Code	CUT AND DRILL	Cut and Drill Metal	Minutes	1
30	FST0223-L25	130	FABRICATION	10	Item Code	FST0223CM	Allthread M10 x 1000 SS 316	Each	0.03
31	FST0223-L25	130	FABRICATION	20	Labour Code	FABRICATION ASSY	Assembly Fabrication	Minutes	5
32	PNL0686SB	100	FABRICATION	10	Item Code	PBN0395CM	27 OD Hand Grip Inner Bend (AS4685:2021)	Each	1
33	PNL0686SB	100	FABRICATION	20	Item Code	FST0009CM	SCREW BUTTON TORX S/S M8 X 20	Each	1
34	PNL0686SB	100	FABRICATION	30	Item Code	CST0001SM	PS 27 Single Hole Flange (Flat)(AP-005)	Each	1
35	PNL0686SB	100	FABRICATION	50	Labour Code	FABRICATION ASSY	Fabricated Assembly	Minutes	15
36	PNL0686SBC	20	POWDERCOAT	10	Item Code	PNL0686SB	PS Hand Grip Inner (AS4685:2021)	Each	1
37	PNL0686SBC	20	POWDERCOAT	20	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	4
38	PNL0687SB	100	FABRICATION	60	Item Code	FST0009CM	SCREW BUTTON TORX S/S M8 X 20	Each	2
39	PNL0687SB	100	FABRICATION	10	Item Code	PBN0396CM	27 OD Hand Grip Outer Bend (AS4685:2021)	Each	1
40	PNL0687SB	100	FABRICATION	30	Item Code	CST1001SB	PS 27 Single Hole Flange (Flat) Machined for 3 screws	Each	1
41	PNL0687SB	100	FABRICATION	50	Labour Code	FABRICATION ASSY	Fabricated Assembly	Minutes	15
42	PNL0687SB	100	FABRICATION	70	Item Code	FST165	M8 X 16 RTOX (1000 QTY)	Each	1
43	PNL0687SBC	20	POWDERCOAT	10	Item Code	PNL0687SB	PS Hand Grip Outer (AS4685:2021)	Each	1
44	PNL0687SBC	20	POWDERCOAT	20	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	6
45	PNL1688SBA	900	Assembly	10	Item Code	PNL0686SBC	PS Hand Grip Inner (AS4685:2021) Pcoated	Each	2
46	PNL1688SBA	190	PACK LIST	20	Item Code	FST0034CM	WASHER FLAT S/S 3/8 X 3/4 X 18" (5000 QTY)	Each	4
47	PNL1688SBA	900	Assembly	50	Item Code	FST0001CB	PR/PS Nutsert M10 x 40 Assy	Each	4
48	PNL1688SBA	190	PACK LIST	60	Item Code	FST0004CM	SCREW BUTTON TORX S/S M10 X 25 (800 QTY)	Each	4
49	PNL1688SBA	190	PACK LIST	30	Labour Code	PACKING	Packing	Minutes	15
50	PNL1688SBA	900	Assembly	70	Item Code	FST0019CM	Tube Closure - Plastic 27 x M10 (N782110MMB)	Each	4
51	PNL1688SBA	900	Assembly	80	Item Code	PNL0687SBC	PS Hand Grip Outer (AS4685:2021) Pcoated	Each	2
52	ROP0389CB	70	ROPE LIST	140	Item Code	CHN0100CM	Chain 6mm Short Link ST/STEEL	M	2.4
53	ROP0389CB	70	ROPE LIST	10	Item Code	ROP0012CM	Cross Joint Ferrule (GC16-C)	Each	6
54	ROP0389CB	70	ROPE LIST	20	Item Code	ROP0014CM	Short Eye Ferrule (GC16-EK - 24mm)	Each	4
55	ROP0389CB	70	ROPE LIST	30	Item Code	ROP0018CM	Short T-Cross Joint Ferrule (GC16-TCK)	Each	6
56	ROP0389CB	70	ROPE LIST	50	Item Code	FST0219CM	10G x 2" Csunk Square Drive Quad Screw SS	Each	12
57	ROP0389CB	70	ROPE LIST	60	Item Code	ROP0001CM-BLACK	Combi Rope 16 (GC16-RMU) (Black)	M	7.3
58	ROP0389CB	70	ROPE LIST	150	Labour Code	ROPE	Rope Assembly	Minutes	115
59	ROP0389CB	70	ROPE LIST	80	Item Code	ROP0022CM	Thimble 14mm to Suit 16mm Rope SS	Each	4
60	ROP0389CB	70	ROPE LIST	90	Item Code	FST0346	M10 x 25 EYE BOLT 23MM EYE DIA SS-580-10	Each	4
61	ROP0389CB	70	ROPE LIST	100	Item Code	ROP0010CM	Bolt Joint Ferrule Long 55mm (GC16-B)	Each	4
62	ROP0389CB	70	ROPE LIST	110	Item Code	FST0223-L25	Allthread M10 SS 316 - Cut to Length 25mm	Each	4
63	ROP0389CB	70	ROPE LIST	120	Item Code	KRP5040	Chain Boss Flat Surface M10 316 SS	Each	4
64	ROP0389CB	70	ROPE LIST	130	Item Code	FST0320	SCREW BUTTON TORX S/S M8 X 25 (QTY1000)	Each	4
65	SHT0738CBC	180	POWDERCOAT	20	Item Code	SHT0738CM	Pfm Support Plate 5mm SS - for Cable Rope Climbers	Each	1
66	SHT0738CBC	180	POWDERCOAT	20	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	5
67	SHT0738CBC	910	EXT PREP	30	Descriptor Code	FACTORY - EXTERNAL PREP	External Preperation for Powdercoating	$	1
CLM0022SBA-SM SINGLE LEVEL

Here is the multi-level/exploded BOM that I have manually created showing all BOM levels. The fields discussed so far are highlighted blue. Obviously there is more line data that would be useful but one thing at a time!

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

 

[Andy Howcroft]

Hi Herakles,

Many thanks for looking into this one, any help is greatly appreciated. In response to your queries:

1. Please see my multi-level BOM example in XL2BB format at the foot of this message - this should answer this one.

2. Yes, an assembly will always have the same parts with the same quantities. We have a report from our ERP system that shows all BOMs in the system and their subsequent children. See the example below for which I have manually included all the required subassemblies and child parts for our product "CLM0022SBA-SM".

3. Please see the below XL2BB examples:



Below is the original single level BOM report for one of our medium sized BOMs that we can export from our current system. The Parent, Child and Qty columns are headed in blue.

MrExcel VBA Test.xlsm
	A	B	C	D	E	F	G	H	I
1	PARENT					CHILD			QTY
2	ITEMCODE	STEPSEQUENCE	STEPNAME	LINENUMBER	CODETYPE	LINECODE	LINEDESCRIPTION	LINEUNIT	PERQTY
3	CLM0022SBA-SM	900	Assembly	85	Item Code	CLM0337CB	255mm Pitch Footing Channel Assembly	Each	1
4	CLM0022SBA-SM	190	PACK LIST	20	Item Code	FST013	NUT NYLOC M10 Zinc (2000 QTY)	Each	4
5	CLM0022SBA-SM	900	Assembly	40	Item Code	PNL1688SBA	PS Hand Grip Assy (Pair) (AS4685:2021)	Each	1
6	CLM0022SBA-SM	190	PACK LIST	10	Item Code	CAP0023CM-BLACK	NUT COVER & CAP PLASTIC - 10MM - Black	Each	4
7	CLM0022SBA-SM	900	Assembly	90	Item Code	SHT0738CBC	Pfm Support Plate 5mm SS - for Cable Rope Climbers Pcoated	Each	1
8	CLM0022SBA-SM	190	PACK LIST	90	Labour Code	PACKING	Packing	Minutes	20
9	CLM0022SBA-SM	190	PACK LIST	50	Item Code	FST2000	M14 Square Washer Galv - 50x50x3.0	Each	2
10	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
11	CLM0022SBA-SM	200	DISPATCH ASSY	90	Labour Code	DISPATCH ASSY	Assemble as per Picklist	Minutes	30
12	CLM0022SBA-SM	190	PACK LIST	25	Item Code	FST0034CM	WASHER FLAT S/S 3/8 X 3/4 X 18" (5000 QTY)	Each	4
13	CLM0022SBA-SM	900	Assembly	80	Item Code	ROP0389CB	Cable Rope for Climber Net 1200 - Chain Footing	Each	1
14	CLM0337CB	120	DISPATCH ASSY	40	Item Code	FST2001	M10 Large Washer S/S 304 - Ø30OD	Each	4
15	CLM0337CB	120	DISPATCH ASSY	10	Item Code	CLM0337CM	255mm Pitch Footing Channel	Each	1
16	CLM0337CB	120	DISPATCH ASSY	20	Item Code	KRP5040	Chain Boss Flat Surface M10 316 SS	Each	4
17	CLM0337CB	120	DISPATCH ASSY	30	Item Code	FST0320	SCREW BUTTON TORX S/S M8 X 25 (QTY1000)	Each	4
18	CLM0337CB	120	DISPATCH ASSY	50	Item Code	FST0002CM	SCREW BUTTON TORX S/S M10 X 16 (TW 111051) (800 QTY)	Each	4
19	CLM0337CB	120	DISPATCH ASSY	60	Labour Code	DISPATCH ASSY	Assemble as per Picklist	Minutes	10
20	CLM0337CM	910	FABRICATION	20	Labour Code	FABRICATION ASSY	Assembly Fabrication	Minutes	10
21	CLM0337CM	910	FABRICATION	50	Item Code	CLM0331CM	Universal Footing Channel Type 1	Each	1
22	CST1001SB	20	EXT MACHINE	30	Labour Code	HANDLING	Handling	Minutes	1
23	CST1001SB	20	EXT MACHINE	40	Item Code	CST0001SM	PS 27 Single Hole Flange (Flat)(AP-005)	Each	1
24	CST1001SB	20	EXT MACHINE	50	Descriptor Code	CST1001SE	Machine CST0001SM PS 27 Single Hole Flange (Flat)	Each	1
25	FST0001CB	10	PACK LIST	10	Item Code	FST0005CM	SCREW BUTTON TORX S/S M10 X 40 (TW - 18999) (2000 QTY)	Each	1
26	FST0001CB	190	DISPATCH ASSY	20	Item Code	FST009	NUTSERT M10 SPLINED ZP (TW-17893) - Grip Range .78mm to 3.8mm	Each	1
27	FST0001CB	190	DISPATCH ASSY	30	Labour Code	UPRIGHT ASSY	Nutsert and Cap Fitment	Minutes	0.5
28	FST0001CB	10	PACK LIST	60	Labour Code	PACKING	Packing	Minutes	0.5
29	FST0001CB	20	CUT LIST	70	Labour Code	CUT AND DRILL	Cut and Drill Metal	Minutes	1
30	FST0223-L25	130	FABRICATION	10	Item Code	FST0223CM	Allthread M10 x 1000 SS 316	Each	0.03
31	FST0223-L25	130	FABRICATION	20	Labour Code	FABRICATION ASSY	Assembly Fabrication	Minutes	5
32	PNL0686SB	100	FABRICATION	10	Item Code	PBN0395CM	27 OD Hand Grip Inner Bend (AS4685:2021)	Each	1
33	PNL0686SB	100	FABRICATION	20	Item Code	FST0009CM	SCREW BUTTON TORX S/S M8 X 20	Each	1
34	PNL0686SB	100	FABRICATION	30	Item Code	CST0001SM	PS 27 Single Hole Flange (Flat)(AP-005)	Each	1
35	PNL0686SB	100	FABRICATION	50	Labour Code	FABRICATION ASSY	Fabricated Assembly	Minutes	15
36	PNL0686SBC	20	POWDERCOAT	10	Item Code	PNL0686SB	PS Hand Grip Inner (AS4685:2021)	Each	1
37	PNL0686SBC	20	POWDERCOAT	20	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	4
38	PNL0687SB	100	FABRICATION	60	Item Code	FST0009CM	SCREW BUTTON TORX S/S M8 X 20	Each	2
39	PNL0687SB	100	FABRICATION	10	Item Code	PBN0396CM	27 OD Hand Grip Outer Bend (AS4685:2021)	Each	1
40	PNL0687SB	100	FABRICATION	30	Item Code	CST1001SB	PS 27 Single Hole Flange (Flat) Machined for 3 screws	Each	1
41	PNL0687SB	100	FABRICATION	50	Labour Code	FABRICATION ASSY	Fabricated Assembly	Minutes	15
42	PNL0687SB	100	FABRICATION	70	Item Code	FST165	M8 X 16 RTOX (1000 QTY)	Each	1
43	PNL0687SBC	20	POWDERCOAT	10	Item Code	PNL0687SB	PS Hand Grip Outer (AS4685:2021)	Each	1
44	PNL0687SBC	20	POWDERCOAT	20	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	6
45	PNL1688SBA	900	Assembly	10	Item Code	PNL0686SBC	PS Hand Grip Inner (AS4685:2021) Pcoated	Each	2
46	PNL1688SBA	190	PACK LIST	20	Item Code	FST0034CM	WASHER FLAT S/S 3/8 X 3/4 X 18" (5000 QTY)	Each	4
47	PNL1688SBA	900	Assembly	50	Item Code	FST0001CB	PR/PS Nutsert M10 x 40 Assy	Each	4
48	PNL1688SBA	190	PACK LIST	60	Item Code	FST0004CM	SCREW BUTTON TORX S/S M10 X 25 (800 QTY)	Each	4
49	PNL1688SBA	190	PACK LIST	30	Labour Code	PACKING	Packing	Minutes	15
50	PNL1688SBA	900	Assembly	70	Item Code	FST0019CM	Tube Closure - Plastic 27 x M10 (N782110MMB)	Each	4
51	PNL1688SBA	900	Assembly	80	Item Code	PNL0687SBC	PS Hand Grip Outer (AS4685:2021) Pcoated	Each	2
52	ROP0389CB	70	ROPE LIST	140	Item Code	CHN0100CM	Chain 6mm Short Link ST/STEEL	M	2.4
53	ROP0389CB	70	ROPE LIST	10	Item Code	ROP0012CM	Cross Joint Ferrule (GC16-C)	Each	6
54	ROP0389CB	70	ROPE LIST	20	Item Code	ROP0014CM	Short Eye Ferrule (GC16-EK - 24mm)	Each	4
55	ROP0389CB	70	ROPE LIST	30	Item Code	ROP0018CM	Short T-Cross Joint Ferrule (GC16-TCK)	Each	6
56	ROP0389CB	70	ROPE LIST	50	Item Code	FST0219CM	10G x 2" Csunk Square Drive Quad Screw SS	Each	12
57	ROP0389CB	70	ROPE LIST	60	Item Code	ROP0001CM-BLACK	Combi Rope 16 (GC16-RMU) (Black)	M	7.3
58	ROP0389CB	70	ROPE LIST	150	Labour Code	ROPE	Rope Assembly	Minutes	115
59	ROP0389CB	70	ROPE LIST	80	Item Code	ROP0022CM	Thimble 14mm to Suit 16mm Rope SS	Each	4
60	ROP0389CB	70	ROPE LIST	90	Item Code	FST0346	M10 x 25 EYE BOLT 23MM EYE DIA SS-580-10	Each	4
61	ROP0389CB	70	ROPE LIST	100	Item Code	ROP0010CM	Bolt Joint Ferrule Long 55mm (GC16-B)	Each	4
62	ROP0389CB	70	ROPE LIST	110	Item Code	FST0223-L25	Allthread M10 SS 316 - Cut to Length 25mm	Each	4
63	ROP0389CB	70	ROPE LIST	120	Item Code	KRP5040	Chain Boss Flat Surface M10 316 SS	Each	4
64	ROP0389CB	70	ROPE LIST	130	Item Code	FST0320	SCREW BUTTON TORX S/S M8 X 25 (QTY1000)	Each	4
65	SHT0738CBC	180	POWDERCOAT	20	Item Code	SHT0738CM	Pfm Support Plate 5mm SS - for Cable Rope Climbers	Each	1
66	SHT0738CBC	180	POWDERCOAT	20	Labour Code	POWDERCOAT	Powder Coating Operation	Minutes	5
67	SHT0738CBC	910	EXT PREP	30	Descriptor Code	FACTORY - EXTERNAL PREP	External Preperation for Powdercoating	$	1
CLM0022SBA-SM SINGLE LEVEL

Here is the multi-level/exploded BOM that I have manually created showing all BOM levels. The fields discussed so far are highlighted blue. Obviously there is more line data that would be useful but one thing at a time!

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

Hi Herakles

Apologies, I inadvertently posted before completing my responses to your queries. Please see below for the remaining answers.

3. Please see the below XL2BB examples:
- An extract from the original single level BOM report which will be the basis for constructing the multi-level BOMs (for all our products this is currently 80,000+ lines). There is no info to designate a product vs. assembly vs. part
- A manually constructed multilevel BOM (which is ideally how we would want the final report to look).

4. Yes, assumption correct.

5. Yes, assumption correct.

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

Let me know if you need any more info etc.

Many thanks in advance .

 

[Andy Howcroft]

Hi Andy,
I knew that the method I applied in post #3 would prove to be impractical on large data sets. I was hoping to convert the principle (at least) to an in-memory approach via arrays/collections/dictionaries etc. but unfortunately I've hit a brick wall getting the flow of logic right. I'm not certain I will ever be able to get a workable solution for you, and sincerely hope that those on the forum more advanced than me will come to the rescue. In the meantime, I'd still like to look at the actual data - but you need to make shared file available to anyone with the link (the link you provided is 'restricted'.
Good luck!

Hi Kevin,

All good, thanks for your efforts, it's much appreciated. I have included the XL2BB data in my recent post to Herakles if you wanted to have a look