Convert a flat bom to an indented bom

[kcoates]

I'm trying to create a multilevel bom from a flat bom. The flat bom would look similar to the attached with the output indented bom also on the attached. I want to find the assemblies in columns where each part are used and create a waterfalling bom.

 

[kcoates]

TABLE INPUT (COPIED FROM CAD)
PN	DESC	USED IN ASSEM1	QTY IN ASSEM1.	USED IN ASSEM2	QTY IN ASSMEM2	USED IN ASSEM3	QTY IN ASSEM 3
111	BIKE	TOP LEVEL	1
1110	WHEEL	111	1
51	LUG	1110	4	1111	6	1112	2
52	NUT	1110	4	1111	6	1112	2
53	BOLT	1110	4	1111	6	1112	2
54	WASHER	1110	8	1111	10	1112	4
1111	LARGE WHEEL	111	1
1112	SMALL WHEEL	111	1
INDENTED BOM DESIRED
LEVEL 1	LEVEL2	LEVEL3	PN	DESC	QTY
X			111	BIKE	1
	X		1110	WHEEL	4
		X	51	LUG	4
		X	52	NUT	4
		X	53	BOLT	4
		X	54	WASHER	8
	X		1111	LARGE WHEEL	1
		X	51	LUG	6
		X	52	NUT	6
		X	53	BOLT	6
		X	54	WASHER	10
	X		1112	SMALL WHEEL	1
		X	51	LUG	2
		X	52	NUT	2
		X	53	BOLT	2
		X	54	WASHER	4

<colgroup><col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="135" style="width: 101pt; mso-width-source: userset; mso-width-alt: 4937;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <tbody> </tbody>