Need Help to obtain desired output from an Idented Bill Of Materials

stalinc434

New Member
Joined
Oct 3, 2011
Messages
5
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 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 (on the bottom of the thread)

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 dont know how to implement the indented level of the bill.
If someone could help me I will greaatly appreciate it.

Thanks!


This would be the 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>
This the desired output (ordered in alphabetical order)


<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>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top