Indent BOM to desired output Formula

[stalinc434]

Hello, I have this Project in which I am supposed to convert this Indented BOM (Bill of materials) and convert it to a desired output, please see at the bottom.
At first glance I believed it was an easy problem, however when dealing with indented bills the item will be at a certain level and the quantity of the item will depend on that. Take an example bolt1, listed on the indented bill

Bolt1 is three times on the list,
- The first one on level 2 (inside of subassy 1, subassy 1 has a quantity of 2) and it has a quantity of 3; therefore we will have 3X2=6... 6 being the number of bolts1 on that level.

-The second one is on level level 3 (inside subassi3, which is inside of subassy2), and it has a quantity of 4, since bolt one is inside of "subassy3",4 quantities, and "subassy3" is inside "subassy2", 2 quantities. therefore we will have 4X4=16 (now at level three we go up the level and; 16X2=32... 32 being the number of bolts on that level.

- the third Bolt1 is located on level 1 and there is 1 quantity, since is on level on 1x1=1 having only 1 bolt.

Finally adding up all "bolt1" we would have 6+32+1=39 Bolt1.

I will need to do that for all the parts listed on the indented bill, I have tried using the vlookup function and it will return to me the number, however I don't know how to implement the indented level of the bill.
If someone could help me I will greatly appreciate it.

Input

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { color: white; font-weight: 700; border: 2pt double rgb(63, 63, 63); background: none repeat scroll 0% 0% rgb(165, 165, 165); }.xl64 { border-width: 0.5pt 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; }.xl65 { border: 0.5pt solid windowtext; }.xl66 { border-width: 0.5pt 1pt 0.5pt 0.5pt; border-style: solid; border-color: windowtext; }.xl67 { border-width: 0.5pt 0.5pt 1pt 1pt; border-style: solid; border-color: windowtext; }.xl68 { border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; }.xl69 { border-width: 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; }.xl70 { border-width: 0.5pt 1pt 1pt 0.5pt; border-style: solid; border-color: windowtext; }.xl71 { text-align: center; border-width: medium medium 2pt; border-style: none none double; border-color: -moz-use-text-color -moz-use-text-color rgb(63, 63, 63); }</style> <table style="border-collapse: collapse; width: 260pt;" border="0" cellpadding="0" cellspacing="0" width="260"> <col style="width: 65pt;" width="65" span="4"> <tbody><tr style="height: 16pt;" height="16"> <td colspan="4" class="xl71" style="height: 16pt; width: 260pt;" height="16" width="260">Indented BOM</td> </tr> <tr style="height: 17pt;" height="17"> <td class="xl63" style="height: 17pt; border-top: medium none;" height="17">Indent(level)</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Part Number</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Revision </td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Qtty</td> </tr> <tr style="height: 16pt;" height="16"> <td class="xl64" style="height: 16pt; border-width: 0.5pt 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(99, 190, 123);" align="right" height="16">0</td> <td>Top Assy</td> <td class="xl66">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(99, 190, 123);" align="right">1</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(177, 212, 127);" align="right" height="15">1</td> <td>Sub Assy 1</td> <td class="xl66" style="border-top: medium none;">C</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(203, 220, 129);" align="right">2</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(255, 235, 132);" align="right" height="15">2</td> <td>Bolt1</td> <td class="xl66" style="border-top: medium none;">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(253, 192, 124);" align="right">3</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(255, 235, 132);" align="right" height="15">2</td> <td>Nut1</td> <td class="xl66" style="border-top: medium none;">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(253, 192, 124);" align="right">3</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(255, 235, 132);" align="right" height="15">2</td> <td>Washer1</td> <td class="xl66" style="border-top: medium none;">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(253, 192, 124);" align="right">3</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(177, 212, 127);" align="right" height="15">1</td> <td>Sub Assy 2</td> <td class="xl66" style="border-top: medium none;">B</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(203, 220, 129);" align="right">2</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(255, 235, 132);" align="right" height="15">2</td> <td>Bolt2</td> <td class="xl66" style="border-top: medium none;">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(99, 190, 123);" align="right">1</td> </tr> <tr style="height: 16pt;" height="16"> <td class="xl64" style="height: 16pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(255, 235, 132);" align="right" height="16">2</td> <td>Sub Assy 3</td> <td class="xl66" style="border-top: medium none;">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(248, 105, 107);" align="right">4</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(248, 105, 107);" align="right" height="15">3</td> <td>Bolt1</td> <td class="xl66" style="border-top: medium none;">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(248, 105, 107);" align="right">4</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(248, 105, 107);" align="right" height="15">3</td> <td>Bolt2</td> <td class="xl66" style="border-top: medium none;">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(248, 105, 107);" align="right">4</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(177, 212, 127);" align="right" height="15">1</td> <td>Bolt1</td> <td class="xl66" style="border-top: medium none;">A</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(99, 190, 123);" align="right">1</td> </tr> <tr style="height: 16pt;" height="16"> <td class="xl67" style="height: 16pt; border-right: 0.5pt solid windowtext; border-bottom: 1pt solid windowtext; border-left: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(177, 212, 127);" align="right" height="16">1</td> <td class="xl68">Nut1</td> <td class="xl70" style="border-top: medium none;">A</td> <td class="xl69" style="border-right: 0.5pt solid windowtext; border-bottom: 1pt solid windowtext; border-left: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(99, 190, 123);" align="right">1</td> </tr> </tbody></table>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { border-width: 0.5pt 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; }.xl64 { border: 0.5pt solid windowtext; }.xl65 { border-width: 0.5pt 1pt 0.5pt 0.5pt; border-style: solid; border-color: windowtext; }.xl66 { border-width: 0.5pt 0.5pt 1pt 1pt; border-style: solid; border-color: windowtext; }.xl67 { border-width: 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; }.xl68 { border-width: 0.5pt 1pt 1pt 0.5pt; border-style: solid; border-color: windowtext; }.xl69 { color: rgb(0, 97, 0); border-width: 1pt 0.5pt 1pt 1pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(198, 239, 206); }.xl70 { color: rgb(0, 97, 0); border-width: 1pt 0.5pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(198, 239, 206); }.xl71 { color: rgb(0, 97, 0); border-width: 1pt 1pt 1pt 0.5pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(198, 239, 206); }.xl72 { border-width: medium 0.5pt 0.5pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; }.xl73 { border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; }.xl74 { border-width: medium 1pt 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; }.xl75 { text-align: center; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; }</style> <table style="border-collapse: collapse; width: 195pt;" border="0" cellpadding="0" cellspacing="0" width="195"> <col style="width: 65pt;" width="65" span="3"> <tbody><tr style="height: 16pt;" height="16"> <td colspan="3" class="xl75" style="height: 16pt; width: 195pt;" height="16" width="195">Desired Output
</td> </tr> <tr style="height: 17pt;" height="17"> <td class="xl69" style="height: 17pt; border-top: medium none;" height="17">Part Number</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">Revission</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">Qtty</td> </tr> <tr style="height: 16pt;" height="16"> <td class="xl72" style="height: 16pt;" height="16">Bolt1</td> <td class="xl73" style="border-left: medium none;">A</td> <td class="xl74" style="border-left: medium none;" align="right">39</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="15">Bolt2</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">34</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="15">Nut1</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">7</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="15">Sub Assy 1</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="15">Sub Assy 2</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="15">Sub Assy 3</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">8</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="15">Top Assy</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 16pt;" height="16"> <td class="xl66" style="height: 16pt; border-top: medium none;" height="16">Washer1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="right">6</td> </tr> </tbody></table>

 

[shg]

I think this is correct:

       --A-- ----B----- -C- ---D--- ------------------------E------------------------
   1   Level    Desc    Qty Ext Qty                                                  
   2       0 Top Assy    1       1  D2: =C2                                          
   3       1 Sub Assy 1  2       2  D3: =C3* LOOKUP(9E+307, D$1:D2 / (A$1:A2 = A3-1))
   4       2 Bolt1       3       6                                                   
   5       2 Nut1        3       6                                                   
   6       2 Washer1     3       6                                                   
   7       1 Sub Assy 2  2       2                                                   
   8       2 Bolt2       1       2                                                   
   9       2 Sub Assy 3  4       8                                                   
  10       3 Bolt1       4      32                                                   
  11       3 Bolt2       4      32                                                   
  12       1 Bolt1       1       1                                                   
  13       1 Nut1        1       1

 

[stalinc434]

Thanks for your help!! But where exactly do you put that formula, I'm sorry I'm kinda new to excel..

 

[shg]

The formulas are in D2 and D3. The formula in D3 is copied down.

 

[stalinc434]

I have tried to copy that formula into the cells but it keeps giving me
Zeros.. Maybe I am doing something wrong?
Could you please
Post the formula again? Maybe I missed a parenthesis or the rest of the formula.. Thank you very much.

 

[stalinc434]

SHG;
Thank you very much for
Your help, I was putting the formula in the wrong cell, I appreciate your help in this problem.
Thanks!!

 

[shg]

You're welcome, glad it helped.