Creating a BOM Heirarchy using VBA

[blakecw3]

Hello,

I'm trying to take a list of items and components and create a heirarchical Bill of Materials from the data sample shown here. The total list will be for 12000+ rows.

	A	B	C	D	E
1	Item Number	UOM	Component	Component UOM	Quantity
2	CPT001A	EA	FPM1010R	MS	0.19
3	CPT0002A	EA
4	CPT005A	EA	FPM15XR	MS	0.17
5	CPT010A	EA	FPM3010R	MS	0.19
6	CPT00122CYFN	EA	CPT001	EA	2
7	CRE00562NNNN	EA
8	MME3S01613VP	EA	21334	EA	13
9	MME3S01613VP	EA	10105	EA	3
10	MME3S01613VP	EA	992	EA	2
11	MME3S01613VP	EA	10114	EA	2
12	MME3S01613VP	EA	985	EA	2
13	MME3S01613VP	EA	984	EA	2
14	MME3S01613VP	EA	982	EA	1
15	MME3S01613VP	EA	980	EA	1
16	982	EA	323	LB	1.75
17	984	EA
18	985	EA

<tbody>
</tbody>

In column A, an item number may or may not have a corresponding component in column C. I want to create a new array in a separate worksheet where the component is matched with the Item Number and the quantity is shown for each level in the BOM in a table of conversions. If there is no component value, then it should be skipped in the list. Note that there can be multiple components for a given Item Number and the list should repeat with each item number and component pairing on a separate line. If the Item Number does have a component, the component value will have to be searched in the item number column to find its subcomponent in the BOM. The BOM can go as high as 8 levels of subcomponents and can have as many as 8 components for each Item Number. The example results would look something like the following:

	A	B	C	D	F	G	H
1	Item Number	Component 1	Component 2	Component 3	Quantity Item --> Component 1	Quantity Component 1 --> Component 2	Quantity Component 2--> Component 3
2	CPT001A	FPM1010R			0.19
3	CPT005A	FPM15XR			0.17
4	CPT010A	FPM3010R			0.19
5	CPT00122CYFN	CPT001			2
6	MME3S01613VP	21334			13
7	MME3S01613VP	10105			3
8	MME3S01613VP	992			2
9	MME3S01613VP	10114			2
10	MME3S01613VP	985			2
11	MME3S01613VP	984			2
12	MME3S01613VP	982	323		1	1.75
13	MME3S01613VP	980			1

<tbody>
</tbody>

Thank you for your review.