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.

 

[Anonymous1378]

I have sincere doubts that this will work at the scale you need; nor does it exactly match your output, but here's something I tried:

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

Cell Formulas
Range	Formula
K3:W67	K3	=LET( _a,A3:A67, _b,F3:F67, _c,UNIQUE(_a), _d,FILTER(_c,ISNA(XMATCH(_c,_b))), _e,IFERROR(DROP(REDUCE("",_b,LAMBDA(_x,_y,VSTACK(_x,LET(getParent, LAMBDA(ME,linecode,stack,LET(r,IFERROR(linecode-2,XMATCH(linecode,_b)), parent,INDEX(_a,r), list,HSTACK(parent,stack), IF(ISNUMBER(XMATCH(parent,_d)),list,ME(ME,parent,list)))),getParent(getParent,ROW(_y),_y))))),1),""), _f,BYROW(_e,LAMBDA(_z,SUM(IF(_z<>"",1,0)))), _g,MAKEARRAY(ROWS(_f),MAX(_f)-1,LAMBDA(ro,co,IF(INDEX(_f,ro)=co+1,INDEX(_f,ro),""))), _h,BYROW(_e,LAMBDA(_w,CONCAT(_w))), _i,SORTBY(HSTACK(_g,B3:I67),_h),_i)
Dynamic array formulas.

 

[Andy Howcroft]

I have sincere doubts that this will work at the scale you need; nor does it exactly match your output, but here's something I tried:

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

Cell Formulas
Range	Formula
K3:W67	K3	=LET( _a,A3:A67, _b,F3:F67, _c,UNIQUE(_a), _d,FILTER(_c,ISNA(XMATCH(_c,_b))), _e,IFERROR(DROP(REDUCE("",_b,LAMBDA(_x,_y,VSTACK(_x,LET(getParent, LAMBDA(ME,linecode,stack,LET(r,IFERROR(linecode-2,XMATCH(linecode,_b)), parent,INDEX(_a,r), list,HSTACK(parent,stack), IF(ISNUMBER(XMATCH(parent,_d)),list,ME(ME,parent,list)))),getParent(getParent,ROW(_y),_y))))),1),""), _f,BYROW(_e,LAMBDA(_z,SUM(IF(_z<>"",1,0)))), _g,MAKEARRAY(ROWS(_f),MAX(_f)-1,LAMBDA(ro,co,IF(INDEX(_f,ro)=co+1,INDEX(_f,ro),""))), _h,BYROW(_e,LAMBDA(_w,CONCAT(_w))), _i,SORTBY(HSTACK(_g,B3:I67),_h),_i)
Dynamic array formulas.

Hi - Thanks heaps for looking at this one, your solution works excellently and I haven't found any incorrect BOM lines after much checking. However, as you suspected when applying to the 80,000+ lines it fails. I managed to get 17,000 multi-level BOM lines before it won't go any further. I can do a VLOOKUP to glean the item data from the code if required so effectively all I need is the LINECODE, the STEPNAME and the PERQTY. If you removed the other data fields I am presuming this would reduce the calculation requirements...? I have been trying to allow more CPU % for Excel (multi-thread, High priority) as despite my laptop having 32GB RAM Excel will not use more than 15-20% of CPU when processing. Not sure if this will help withe the issue, though...?

Also, the Parent item (BOM level "1") is missing from the report. Is there any way to include this as per the below as without it I can't identify what Parent the BOM lines are attributed to easily...? Of course the formatting isn't required.

Many thanks in advance

BOMLINES_Anonymous1378.xlsm
	Y	Z	AA	AB	AC	AD	AE	AF
2						STEPNAME	LINECODE	PERQTY
3	1						ACC0020LBA	1
4		2				Assembly	ACC0020LBC	1
5			3			POWDERCOAT	ACC0020LB	1
6				4		Assembly	PBN0107LM	2
7				4		Assembly	PBN0108LM	5
8				4		WELD LIST	WELD	70
9			3			POWDERCOAT	POWDERCOAT	22
10		2				PACK LIST	CAP0023CM-BLACK	2
11		2				PACK LIST	FST0004CM	2
12		2				PACK LIST	FST0034CM	2
13		2				PACK LIST	FST013	2
14		2				PACK LIST	PACKING	15
15		2				PACK LIST	PNL0192LBA	2
16	1						ACC0024LBA	1
17		2				Assembly	ACC0024LB	1
18			3			WELD LIST	PBN0106LM	2
19			3			WELD LIST	PBN0108LM	4
20			3			POWDERCOAT	POWDERCOAT	5
21			3			WELD LIST	PROCESS WELD/POWDER	1
22			3			WELD LIST	WELD	135
23		2				Assembly	CAP0023CM-BLACK	2
24		2				Assembly	FST0004CM	2
25		2				FASTENER LIST	FST0034CM	2
26		2				FASTENER LIST	FST013	2
27		2				Assembly	PACKING	15
28		2				Assembly	PNL0192LBA	2
29	1						ACC0025LBA	1
30		2				Assembly	ACC0025LB	1
31			3			WELD LIST	PBN0107LM	2
32			3			WELD LIST	PBN0108LM	7
33			3			POWDERCOAT	POWDERCOAT	5
34			3			WELD LIST	PROCESS WELD/POWDER	1
35			3			WELD LIST	WELD	185
36		2				Assembly	CAP0023CM-BLACK	2
37		2				Assembly	FST0004CM	2
38		2				FASTENER LIST	FST0034CM	2
39		2				FASTENER LIST	FST013	2
40		2				Assembly	PACKING	15
41		2				Assembly	PNL0192LBA	2
42	1						ACC0027LBA	1
43		2				Assembly	ACC0026CBC	1
44			3			POWDERCOAT	ACC0026CB	1
45				4		WELD LIST	FST009	4
46				4		WELD LIST	GLT0059CM	2.3
47				4		WELD LIST	PROCESS WELD/POWDER	1
48				4		WELD LIST	WELD	45
49			3			POWDERCOAT	POWDERCOAT	5
50		2				Assembly	ACC0027LB	1
51			3			Assembly	BDG0101CB	1
52			3			DISPATCH ASSY	CAP0040CM	1
53			3			DISPATCH ASSY	CAP0041CM	2
54			3			DISPATCH ASSY	DISPATCH ASSY	15
55			3			DISPATCH ASSY	FST030	2
56			3			DISPATCH ASSY	FST086	1
57			3			PRE-FAB STOCK 2	ROD0014CB	2
58			3			Assembly	SHT0103CBC	1
59			3			Assembly	SHT0104CBC	1
60		2				PACK LIST	CAP0023CM-BLACK	4
61		2				PACK LIST	FST0004CM	8
62		2				PACK LIST	FST0034CM	4
63		2				PACK LIST	FST013	4
64		2				PACK LIST	PACKING	15
65		2				Assembly	PNL0192LBA	2
BOMLINES_Anonymous1378

 

[Anonymous1378]

Before I attempt to optimize it any further; can you test if variable _e works on your 80,000 rows? If it can't, then test if it works cell by cell without an array (without the IFERROR to VSTACK)? If it doesn't even work cell by cell then I'm not sure there's much hope of the formulaic approach working.

 

[Andy Howcroft]

Before I attempt to optimize it any further; can you test if variable _e works on your 80,000 rows? If it can't, then test if it works cell by cell without an array (without the IFERROR to VSTACK)? If it doesn't even work cell by cell then I'm not sure there's much hope of the formulaic approach working.

Unfortunately, I'm not an Excel expert and so don't understand how to perform your requests (your formula structure is very different to anything I have done before). If you can provide a formula(s) to utilise it would be appreciated. Otherwise I can provide a link to the original single level BOM data...

 

[Anonymous1378]

Unfortunately, I'm not an Excel expert and so don't understand how to perform your requests (your formula structure is very different to anything I have done before). If you can provide a formula(s) to utilise it would be appreciated. Otherwise I can provide a link to the original single level BOM data...

To test if _e works for 80,000 rows as an array, you just need the formula up to that step:

=LET(
_a,A3:A67,
_b,F3:F67,
_c,UNIQUE(_a),
_d,FILTER(_c,ISNA(XMATCH(_c,_b))),
_e,IFERROR(DROP(REDUCE("",_b,LAMBDA(_x,_y,VSTACK(_x,LET(getParent,
LAMBDA(ME,linecode,stack,LET(r,IFERROR(linecode-2,XMATCH(linecode,_b)),
parent,INDEX(_a,r),
list,HSTACK(parent,stack),
IF(ISNUMBER(XMATCH(parent,_d)),list,ME(ME,parent,list)))),getParent(getParent,ROW(_y),_y))))),1),""),
_e)

To test if it works for 80,000 rows without an array (you need to copy it down for each row), try:

=LET(
_a,A$3:A$67,
_b,F$3:F$67,
_c,UNIQUE(_a),
_d,FILTER(_c,ISNA(XMATCH(_c,_b))),
_e,LET(getParent,
LAMBDA(ME,linecode,stack,LET(r,IFERROR(linecode-2,XMATCH(linecode,_b)),
parent,INDEX(_a,r),
list,HSTACK(parent,stack),
IF(ISNUMBER(XMATCH(parent,_d)),list,ME(ME,parent,list)))),getParent(getParent,ROW(F3),F3)),
_e)

 

[Andy Howcroft]

To test if _e works for 80,000 rows as an array, you just need the formula up to that step:

=LET(
_a,A3:A67,
_b,F3:F67,
_c,UNIQUE(_a),
_d,FILTER(_c,ISNA(XMATCH(_c,_b))),
_e,IFERROR(DROP(REDUCE("",_b,LAMBDA(_x,_y,VSTACK(_x,LET(getParent,
LAMBDA(ME,linecode,stack,LET(r,IFERROR(linecode-2,XMATCH(linecode,_b)),
parent,INDEX(_a,r),
list,HSTACK(parent,stack),
IF(ISNUMBER(XMATCH(parent,_d)),list,ME(ME,parent,list)))),getParent(getParent,ROW(_y),_y))))),1),""),
_e)

To test if it works for 80,000 rows without an array (you need to copy it down for each row), try:

=LET(
_a,A$3:A$67,
_b,F$3:F$67,
_c,UNIQUE(_a),
_d,FILTER(_c,ISNA(XMATCH(_c,_b))),
_e,LET(getParent,
LAMBDA(ME,linecode,stack,LET(r,IFERROR(linecode-2,XMATCH(linecode,_b)),
parent,INDEX(_a,r),
list,HSTACK(parent,stack),
IF(ISNUMBER(XMATCH(parent,_d)),list,ME(ME,parent,list)))),getParent(getParent,ROW(F3),F3)),
_e)

The second test worked but I don't believe it displays all the data as assemblies with subassemblies have extra lines added but the report stops at the end of the single level BOM, when there should be many more rows due to the extra levels added.

 

[Akuini]

@Andy Howcroft
It's an interesting case. I have questions:
1. Can an assembly belongs to multiple Product?
2. In post #12 you provided a result table, can you show us the original table?

 

[JEC]

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​

 

[Anonymous1378]

The second test worked but I don't believe it displays all the data as assemblies with subassemblies have extra lines added but the report stops at the end of the single level BOM, when there should be many more rows due to the extra levels added.
View attachment 100191

I am of the opinion you should favor a VBA approach since it will probably have less overhead than this one; but to answer your question: _d is used to determine BOM level 1 by checking for items which appear in the parent column but never in the child column. Are you sure Z-Welding appears in the child column without any superfluous or missing characters? If it does, then I might be out of ideas... (or perhaps the resources consumed by excel is making it malfunction...)

 

[Andy Howcroft]

@Andy Howcroft
It's an interesting case. I have questions:
1. Can an assembly belongs to multiple Product?
2. In post #12 you provided a result table, can you show us the original table?

Yes it is and one that doesn't seem to have an easy answer!
In answer to your queries:
1. Yes assemblies can belong to multiple products (and subassemblies within the high level Products).
2. Please see below for the original single level BOM report. Please note that the sample results only showed results for 4 assemblies, we have 16000+, so the full report is rather large!

BOMLINES_JEC.xlsm
	A	B	C	D	E
1	ITEMCODE	STEPNAME	LINECODE	LINEUNIT	PERQTY
2	ACC0020LB	Assembly	PBN0107LM	Each	2
3	ACC0020LB	Assembly	PBN0108LM	Each	5
4	ACC0020LB	WELD LIST	WELD	Minutes	70
5	ACC0020LBA	Assembly	ACC0020LBC	Each	1
6	ACC0020LBA	PACK LIST	CAP0023CM-BLACK	Each	2
7	ACC0020LBA	PACK LIST	FST0004CM	Each	2
8	ACC0020LBA	PACK LIST	FST013	Each	2
9	ACC0020LBA	PACK LIST	FST0034CM	Each	2
10	ACC0020LBA	PACK LIST	PACKING	Minutes	15
11	ACC0020LBA	PACK LIST	PNL0192LBA	Each	2
12	ACC0020LBC	POWDERCOAT	ACC0020LB	Each	1
13	ACC0020LBC	POWDERCOAT	POWDERCOAT	Minutes	22
14	ACC0024LB	POWDERCOAT	POWDERCOAT	Minutes	5
15	ACC0024LB	WELD LIST	PROCESS WELD/POWDER	Each	1
16	ACC0024LB	WELD LIST	PBN0106LM	Each	2
17	ACC0024LB	WELD LIST	PBN0108LM	Each	4
18	ACC0024LB	WELD LIST	WELD	Minutes	135
19	ACC0024LBA	FASTENER LIST	FST0034CM	Each	2
20	ACC0024LBA	FASTENER LIST	FST013	Each	2
21	ACC0024LBA	Assembly	FST0004CM	Each	2
22	ACC0024LBA	Assembly	ACC0024LB	Each	1
23	ACC0024LBA	Assembly	CAP0023CM-BLACK	Each	2
24	ACC0024LBA	Assembly	PNL0192LBA	Each	2
25	ACC0024LBA	Assembly	PACKING	Minutes	15
26	ACC0025LB	POWDERCOAT	POWDERCOAT	Minutes	5
27	ACC0025LB	WELD LIST	PROCESS WELD/POWDER	Each	1
28	ACC0025LB	WELD LIST	PBN0107LM	Each	2
29	ACC0025LB	WELD LIST	PBN0108LM	Each	7
30	ACC0025LB	WELD LIST	WELD	Minutes	185
31	ACC0025LBA	FASTENER LIST	FST0034CM	Each	2
32	ACC0025LBA	FASTENER LIST	FST013	Each	2
33	ACC0025LBA	Assembly	FST0004CM	Each	2
34	ACC0025LBA	Assembly	ACC0025LB	Each	1
35	ACC0025LBA	Assembly	CAP0023CM-BLACK	Each	2
36	ACC0025LBA	Assembly	PNL0192LBA	Each	2
37	ACC0025LBA	Assembly	PACKING	Minutes	15
38	ACC0026CB	WELD LIST	PROCESS WELD/POWDER	Each	1
39	ACC0026CB	WELD LIST	FST009	Each	4
40	ACC0026CB	WELD LIST	GLT0059CM	M	2.3
41	ACC0026CB	WELD LIST	WELD	Minutes	45
42	ACC0026CBC	POWDERCOAT	POWDERCOAT	Minutes	5
43	ACC0026CBC	POWDERCOAT	ACC0026CB	Each	1
44	ACC0027LB	DISPATCH ASSY	FST030	Each	2
45	ACC0027LB	Assembly	SHT0104CBC	Each	1
46	ACC0027LB	Assembly	SHT0103CBC	Each	1
47	ACC0027LB	Assembly	BDG0101CB	Each	1
48	ACC0027LB	DISPATCH ASSY	FST086	Each	1
49	ACC0027LB	DISPATCH ASSY	CAP0040CM	Each	1
50	ACC0027LB	DISPATCH ASSY	CAP0041CM	Each	2
51	ACC0027LB	PRE-FAB STOCK 2	ROD0014CB	Each	2
52	ACC0027LB	DISPATCH ASSY	DISPATCH ASSY	Minutes	15
53	ACC0027LBA	PACK LIST	FST0034CM	Each	4
54	ACC0027LBA	PACK LIST	FST013	Each	4
55	ACC0027LBA	PACK LIST	FST0004CM	Each	8
56	ACC0027LBA	Assembly	ACC0026CBC	Each	1
57	ACC0027LBA	Assembly	ACC0027LB	Each	1
58	ACC0027LBA	PACK LIST	CAP0023CM-BLACK	Each	4
59	ACC0027LBA	Assembly	PNL0192LBA	Each	2
60	ACC0027LBA	PACK LIST	PACKING	Minutes	15
XL2BB - Akuini