Creating an Indented Bill of Materials from a Set of Single Level Bills

[TimCD]

I am currently working on a problem where I need to extract a set of single level bills of materials from an engineering CAD system and then consolidate the set into a single, multi-level indented bill.
I am trying to build an Excel application that will take the set of single level bills of materials (extracted from a CAD system) and assemble them into a fully indented bill of materials for an entire product. The problem is figuring out how to recursively assemble the component sub-assemblies together and also consider that a single sub-assemblies can appear in multiple positions and at different levels in the final assembled bill.
The spreadsheet will comprise of two sheets; an input sheet with the raw CAD input data, and a results sheet with the formatted bill of materials. The input page comprises of the top level bill of materials (Type = Top) and then lists all of the various sub-assemblies referenced in the set (Type = Sub-Assembly), again as single level reports. The result page needs to use the flat sub-assembly lists to expand the top level list out into a fully indented report. It would laso be useful on the results page if the indent level is listed as an integer.
I have attached an example set of data input data and how the result page would look after running the VBA.
If anyone has any idea how to do this, it would be most welcome.

Input

Excel 2012
	A	B	C
1	Type	Qty	Prt Number
2	Top	1	TOP_LEVEL_ASSEMBLY
3		1	SUB_ASSY_1
4		1	SUB_ASSY_2
5
6
7	Sub-Assembly		SUB_ASSY_1
8		1	MODULE_01
9		2	PART_001
10		1	PART_002
11		1	MODULE_02
12
13	Sub-Assembly		MODULE_01
14		1	PART_003
15		1	PART_004
16		1	MODULE_03
17
18
19	Sub-Assembly		MODULE_02
20		6	PART_005
21		1	PART_006
22		1	PART_007
23
24
25	Sub-Assembly		MODULE_003
26		1	PART_003
27		1	PART_008
28
29
30	Sub-Assembly		SUB_ASSY_2
31		2	MODULE_01
32		1	PART_009
33		2	PART_010
34		1	MODULE_004
35
36
37	Sub-Assembly		MODULE_004
38		2	PART_003
39		1	PART_011
40		1	PART_012
41		1	MODULE_002
Input

Output

Excel 2012
	A	B	C	D	E	F	G	H
1	Qty	LEVEL	Description
2	1	1	TOP_LEVEL_ASSEMBLY
3	1	2		SUB_ASSY_1
4	1	3			MODULE_01
5	1	4				PART_003
6	1	4				PART_004
7	1	4				MODULE_03
8	1	5					PART_003
9	1	5					PART_008
10	2	3			PART_001
11	1	3			PART_002
12	1	3			MODULE_02
13	6	4				PART_005
14	1	4				PART_006
15	1	4				PART_007
16	1	2		SUB_ASSY_2
17	2	3			MODULE_01
18	1	4				PART_003
19	1	4				PART_004
20	1	4				MODULE_03
21	1	5					PART_003
22	1	5					PART_008
23	1	3			PART_009
24	2	3			PART_010
25	1	3			MODULE_004
26	2	4				PART_003
27	1	4				PART_011
28	1	4				PART_012
29	1	4				MODULE_002
30	6	5					PART_005
31	1	5					PART_006
32	1	5					PART_007
Result

 

[Rijnsent]

Hi Tim,
did you already generate some code of your own? I would run a macro that does the following:
-run through your list (row 2-41) and add the "parent" of each item in e.g. column D
-next, you would ideally have a recursive sub/function to find the "level" of that item/node, could this be of help: http://www.mrexcel.com/forum/excel-...e-totals-using-visual-basic-applications.html
-Finally, once you have those two items, you can put them in the columns you want

Feel free to put some code here (in [ CODE ] brackets please) in case you get stuck,

Koen