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