Flatten multilevel Bill of materials

Hello,
I have a Excel bill of material i can get out of Solidworks (3D design program).
I either have the option to do this as parts only (without the subs) or full structure (as the example).
I would like Excel to calculate every Doc Nr and quantity.
Parts and subs can be have quantities that need to be multiplied to get a complete bom.

example of what i would like to create: (column numbers in the example in brackets)

level(A) docnr(C) quantity (N)
1...........SW1...........1
1.1........SW2...........2
1.1.1.....SW3...........2
2...........SW4...........2
2.1........SW3..........4

Export should be
Docnr quantity

SW1 1
SW2 2
SW3 8
SW4 2

is this feasable? can somebody put me in the right direction?

I guess, SW3 - 12 (4 in SW4 x 2 SW4 + 2 in SW2 x 2 SW2)?

First, you must fix the way you import the data, as for your current file, the column A is set as mixed data format (some as "Text", other as "Number") , this will ruin the calculation.
For example, on cell A12, the value is "1.1", while it should be "1.10" (the zero is discarded by Excel, because Excel treat this cell as "Number")
Other example, on cell A22, the value is "1.2", while it should be "1.20"
.. etc --> I have fix the data and set the background cell as red, so you can see.

After the data is cleaned up, you then can run this macro :

Please Login or Register  to view this content.

You are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.