Dear all,

First time poster here but long time lurker!

I am attempting to undertake an analysis of a multi level (~6 level) bill of materials. The BOM list contains approx. 22,000 records detailing the part number, lower level part number and Qty required (amongst other data not relevant to discussion).

I wish to take a list of parts (approx. 150) and quiz the BOM to tell me the required quantities at each level in the BOM - i.e. make a Kit List of parts that I would require to make the 150 top level items. I do NOT require to know which part is associated with which lower level part.

For example...

Bill of materials

Part Feeder Qty
A B 1
B C 2
B D 2
C Y 5
C Z 10
G H 2
H I 1
H J 2
J K 0.5
J D 1

So to “Explode”

L0 L1 L2 L3
A B (1 per)
C (2 per)
Y (5 per)
Z (10 per)
D (2 per)
G H (2 per)
I (1 per)
J (2 per)
K (0.5 per)
D (1 per)

I wish to be able to “plug in” a list of parts and sales Qtys (sales Items) and get out a “usage” of materials

Parts List
Sales Item Qty
A 10
G 5

Usage Figs
A 10 =10
B (10*1) =10
C (10*1*2) =20
D (10*1*2) + (5*2*2*1) =40
G 5 =5
H (5*2) =10
I (5*2*1) =10
J (5*2*2) =20
K (5*2*2*0.5) =10
Y (10*1*2*5) =100
Z (10*1*2*10) =200

My data on the BOM will be from a ODBC connection to a table.

I have seen several items about "recursive macros" but I do not understand them!

If you require further explanation then please let me know. I need a solution that will work in Excel 2010 and 2013. Thanks in advance

Andy