Re Arranging Data in Excel

DEBR · May 10, 2011

The data below is downloaded from our inhouse software program. I would like to set the data into a table. The first spreadsheet is how it is downloaded. THe second spreadsheet is how I would like the table to be.
Please help to write a macro.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Invoice No 45678</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Order Nr 1234 DD20.08.1999 Order Type VOR </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>PULLEY</TD><TD></TD><TD style="TEXT-ALIGN: right">8979421870</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD>5 EA </TD><TD>478.17 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">478.17</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Delivery no. </TD><TD style="TEXT-ALIGN: right">9.4E+09</TD><TD>Subtotal</TD><TD style="TEXT-ALIGN: right">478.17</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Item</TD><TD></TD><TD>Description </TD><TD></TD><TD>Material</TD><TD></TD><TD></TD><TD></TD><TD>ZAR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD></TD><TD>Qty</TD><TD>Price</TD><TD>Price unit</TD><TD>Value</TD><TD>Trade Disc %</TD><TD>Trade Disc value</TD><TD>Net value</TD><TD>Total net value</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Items total </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">478.17</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Output Tax </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">0.14</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">66.94</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Final Amount </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">545.11</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Directors: D.Duck* (President & Managing Director, ABC Africa)</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>*Brazil</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Invoice No 12345</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>Item</TD><TD></TD><TD>Description </TD><TD></TD><TD>Material</TD><TD></TD><TD></TD><TD></TD><TD>ZAR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD></TD><TD>Qty</TD><TD>Price</TD><TD>Price unit</TD><TD>Value</TD><TD>Trade Disc %</TD><TD>Trade Disc value</TD><TD>Net value</TD><TD>Total net value</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>Order Nr 2468 DD01.04.1999 Order Type STK </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>BUSH;LEAF SPRING</TD><TD></TD><TD style="TEXT-ALIGN: right">8970748260</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD></TD><TD>2 EA </TD><TD>98.92 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">197.84</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">98.92</TD><TD style="TEXT-ALIGN: right">197.84</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD>OUTLET ASM-WAT</TD><TD></TD><TD style="TEXT-ALIGN: right">12605785</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD></TD><TD>1 EA </TD><TD>327.38 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">327.38</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">327.38</TD><TD style="TEXT-ALIGN: right">327.38</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Delivery no. </TD><TD style="TEXT-ALIGN: right">9.4E+09</TD><TD>Subtotal</TD><TD style="TEXT-ALIGN: right">525.22</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD>Items total </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">525.22</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD>Output Tax </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">0.14</TD><TD style="TEXT-ALIGN: right">525.22</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">73.53</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD>Final Amount </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">598.75</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD>Directors: D.Duck* (President & Managing Director, ABC Africa)</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD>*Brazil</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Invoice No</TD><TD>Order Nr</TD><TD>DD</TD><TD>Order Type</TD><TD>Line</TD><TD>Description</TD><TD>Part No</TD><TD>Qty</TD><TD>Price</TD><TD>Delivery No</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">45678</TD><TD style="TEXT-ALIGN: right">1234</TD><TD>20.08.1999</TD><TD>VOR</TD><TD style="TEXT-ALIGN: right">1</TD><TD>PULLEY</TD><TD style="TEXT-ALIGN: right">8979421870</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">9401631530</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="TEXT-ALIGN: right">2468</TD><TD>01.04.1999</TD><TD>STK</TD><TD style="TEXT-ALIGN: right">1</TD><TD>BUSH;LEAF SPRING</TD><TD style="TEXT-ALIGN: right">8970748260</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">98.92</TD><TD style="TEXT-ALIGN: right">9401633034</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="TEXT-ALIGN: right">2468</TD><TD>01.04.2000</TD><TD>STK</TD><TD style="TEXT-ALIGN: right">2</TD><TD>OUTLET ASM-WAT</TD><TD style="TEXT-ALIGN: right">12605785</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">327.38</TD><TD style="TEXT-ALIGN: right">9401633034</TD></TR></TBODY></TABLE>

hiker95 · May 13, 2011

DEBR,

Please reply in a Private Message:

For my own personal information: How much time in hours do you think my macro will save you per day/week/month?

Please send your company name, and web site.

DEBR · May 14, 2011

I do apologies for not doing it in the first place. I just need 1 more column to be added which is Cust Ord No.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 102px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Please wait while your request is being processed...</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>NEXT PAGE </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD>P&A Sales Tax Invoice 9202323920</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD>Date:29.04.2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD>Customer number </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD style="TEXT-ALIGN: right">1331</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD>Your VAT Registration number</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD>ZA4350110971</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>KEY PINETOWN</TD><TD>These parts and accessories are manufactured</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>KEY PINETOWN</TD><TD>to General Motors specification </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>PO Box 585</TD><TD>Page 1 of 1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>3610 PINETOWN</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>Requirements:</TD><TD>Terms of payment 1 Calendar Month from Statement</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Item</TD><TD> </TD><TD>Description </TD><TD> </TD><TD>Material</TD><TD> </TD><TD> </TD><TD> </TD><TD>ZAR</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD> </TD><TD>Qty</TD><TD>Price</TD><TD>Price unit</TD><TD>Value</TD><TD>Trade Disc %</TD><TD>Trade Disc value</TD><TD>Net value</TD><TD>Total net value</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>Order No.: 0003339649 DD 30.03.2011; Order Type YVOR; Ship. Method ;Cust. Order no. DEN81396;Delivery note 9401631530 </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD>PULLEY</TD><TD> </TD><TD style="TEXT-ALIGN: right">8979421870</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD> </TD><TD>1 EA </TD><TD>478.17 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Delivery no. </TD><TD style="TEXT-ALIGN: right">9.4E+09</TD><TD>Subtotal</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD>Items total </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">478.17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD>Output Tax </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">0.14</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">66.94</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD>Final Amount </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">545.11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD>Directors: E.Lourencon* (President & Managing Director, GM Africa),J.M. Baepi, M.G. Sacke</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD>*Brazil</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">39</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">40</TD><TD> </TD><TD>P&A Sales Tax Invoice 9202323926</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">41</TD><TD> </TD><TD>Date:29.04.2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">42</TD><TD> </TD><TD>Customer number </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">43</TD><TD> </TD><TD style="TEXT-ALIGN: right">1331</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">44</TD><TD> </TD><TD>Your VAT Registration number</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">45</TD><TD> </TD><TD>ZA4350110971</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">46</TD><TD>KEY PINETOWN</TD><TD>These parts and accessories are manufactured</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">47</TD><TD>KEY PINETOWN</TD><TD>to General Motors specification </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">48</TD><TD>PO Box 585</TD><TD>Page 1 of 1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">49</TD><TD>3610 PINETOWN</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">50</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">51</TD><TD>Requirements:</TD><TD>Terms of payment 1 Calendar Month from Statement</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">52</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">53</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">54</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">55</TD><TD>Item</TD><TD> </TD><TD>Description </TD><TD> </TD><TD>Material</TD><TD> </TD><TD> </TD><TD> </TD><TD>ZAR</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">56</TD><TD> </TD><TD>Qty</TD><TD>Price</TD><TD>Price unit</TD><TD>Value</TD><TD>Trade Disc %</TD><TD>Trade Disc value</TD><TD>Net value</TD><TD>Total net value</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">57</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">58</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">59</TD><TD>Order No.: 0003345786 DD 04.04.2011; Order Type YVOR; Ship. Method ;Cust. Order no. HAR81985;Delivery note 9401631413 </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">60</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">61</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD>MOLDING A-ROOF</TD><TD> </TD><TD style="TEXT-ALIGN: right">96543107</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">62</TD><TD> </TD><TD>1 EA </TD><TD>113.00 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">113</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">113</TD><TD style="TEXT-ALIGN: right">113</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">63</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Delivery no. </TD><TD style="TEXT-ALIGN: right">9.4E+09</TD><TD>Subtotal</TD><TD style="TEXT-ALIGN: right">113</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">64</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">65</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">66</TD><TD>Items total </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">113</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">67</TD><TD>Output Tax </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">0.14</TD><TD style="TEXT-ALIGN: right">113</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">15.82</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">68</TD><TD>Final Amount </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">128.82</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">69</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">70</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">71</TD><TD>Directors: E.Lourencon* (President & Managing Director, GM Africa),J.M. Baepi, M.G. Sacke</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">72</TD><TD>*Brazil</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

After Macro

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 77px"><COL style="WIDTH: 61px"><COL style="WIDTH: 71px"><COL style="WIDTH: 71px"><COL style="WIDTH: 31px"><COL style="WIDTH: 331px"><COL style="WIDTH: 102px"><COL style="WIDTH: 28px"><COL style="WIDTH: 56px"><COL style="WIDTH: 77px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Invoice No</TD><TD>Order NR</TD><TD>DD</TD><TD>Order Type</TD><TD>Line</TD><TD>Description</TD><TD>Part No</TD><TD>Qty</TD><TD>Price</TD><TD>Delivery No</TD><TD>Cust Ord No</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">9202323920</TD><TD style="TEXT-ALIGN: right">3339649</TD><TD>30.03.2011</TD><TD>YVOR</TD><TD style="TEXT-ALIGN: right">1</TD><TD>PULLEY</TD><TD style="TEXT-ALIGN: right">8979421870</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">9401631530</TD><TD>DEN81396</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">9202323926</TD><TD style="TEXT-ALIGN: right">3345786</TD><TD>04.04.2011</TD><TD>YVOR</TD><TD style="TEXT-ALIGN: right">1</TD><TD>MOLDING A-ROOF</TD><TD style="TEXT-ALIGN: right">96543107</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">113</TD><TD style="TEXT-ALIGN: right">9401631413</TD><TD>HAR81985</TD></TR></TBODY></TABLE>

hiker95 · May 14, 2011

DEBR,

See the YELLOW cells:
83856_PART1
and
201104211526 DIFF

Do you want me to strip off the?
_PART1
and
DIFF

Excel Workbook

L

M

N

O

P

Q

R

S

T

U

V

1

Invoice No

Order NR

DD

Order Type

Line

Description

Part No

Qty

Price

Delivery No

Cust Ord No

2

9202323920

3339649

30.03.2011

YVOR

1

PULLEY

8979421870

1

478.17

9401631530

DEN81396

3

9202323926

3345786

04.04.2011

YVOR

1

MOLDING A-ROOF

96543107

1

113

9401631413

HAR81985

13

9202324056

3363422

20.04.2011

YSTK

1

BELT;FAN COOLING

8979125940

1

167.66

9401631553

83856_PART1

14

9202324310

3366534

28.04.2011

YEMG

1

HEADLAMP, RH (IDENT JH)

96995730

1

1004.4

9401630691

DAN84367

24

9202324928

3368474

29.04.2011

YEMG

1

CLEANER A-AIR

96553445

1

276.86

9401634103

SHEV84633

25

9202324948

3368937

29.04.2011

YVOR

1

RADIATOR GRILLE - WITHOUT MOULDING

93191829

1

1514.58

9401634485

201104211526 DIFF

26

9202324949

3368938

29.04.2011

YVOR

1

COVER ASM;WHL TR

93394102

1

115.06

9401634486

201104211526 DIFF

35

9202325067

3367537

29.04.2011

YEMG

1

SCREW CYL BLOCK PLATE CYL BLOCK

24454170

10

5.73

9401633021

iq84510

38

9202325069

3367541

29.04.2011

YEMG

1

BUSHING;LWR CONTROL ARM

93395604

2

58.58

9401633023

ABU84509

39

9202325070

3367320

28.04.2011

YSTK

1

CABLE ASM MANUAL TRANSM SHIFT LEVER

93386267

1

744.24

9401632395

84317_PART1

40

9202325070

3367320

28.04.2011

YSTK

2

CABLE ASM;PARK BRAKE

93601359

1

168.31

9401632395

84317_PART1

50

9202325071

3366324

28.04.2011

YEMG

1

PIPE WATER OUTLET

8976020470

1

345.55

9401632168

KUB84341

51

9202325072

3356852

14.04.2011

YEMG

1

BLADE; WIPER

8980537880

1

179.34

9401631995

SHEV83166

Sheet1

hiker95 · May 14, 2011

DEBR,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Option Base 1
Sub ReorgDataV4()
' hiker95, 05/14/2011
' http://www.mrexcel.com/forum/showthread.php?t=549125
Dim c As Range, firstaddress As String
Dim a As Long, aa As Long, LR As Long, i As Long, NR As Long, b As Long
Dim Iary, Sp, myChar As String
Application.ScreenUpdating = False
myChar = Chr(160)
With Range("B1", Range("B" & Rows.Count).End(xlUp))
  .Replace What:=myChar, Replacement:=" ", LookAt:=xlPart, _
  SearchFormat:=False, ReplaceFormat:=False
End With
Columns("L:V").ClearContents
Range("L1:V1") = [{"Invoice No","Order NR","DD","Order Type","Line","Description","Part No","Qty","Price","Delivery No","Cust Ord No"}]
i = Application.CountIf(Columns(1), "*Brazil")
ReDim Iary(1 To i + 1)
For a = 1 To 10 Step 1
  If InStr(Cells(a, 2), "Invoice") > 0 Then
    Iary(1) = a
    Exit For
  End If
Next a
With Columns(1)
  i = 1
  Set c = .Find("*Brazil", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      i = i + 1
      Iary(i) = c.Row + 4
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
For i = LBound(Iary) To UBound(Iary) - 1
  NR = Range("L" & Rows.Count).End(xlUp).Offset(1).Row
  Sp = Split(Range("B" & Iary(i)), " ")
  Range("L" & NR).Value = Sp(UBound(Sp))
  b = 0
  For a = Iary(i) To Iary(i + 1) - 1 Step 1
    If Cells(a, 1) = "" Then
      'do nothing
    ElseIf InStr(Cells(a, 1), "Order No") > 0 Then
      Sp = Split(Cells(a, 1), " ")
      Range("M" & NR).Value = Sp(2)
      Range("N" & NR).Value = Left(Sp(4), Len(Sp(4)) - 1)
      Range("O" & NR).Value = Left(Sp(7), Len(Sp(7)) - 1)
      Sp = Split(Cells(a, 1), "Cust. Order no. ")
      Sp = Split(Sp(1), ";")
      Range("V" & NR).Value = Sp(0)
    ElseIf IsNumeric(Cells(a, 1)) Then
      b = b + 1
      If b = 1 Then
        Range("P" & NR).Value = Cells(a, 1).Value
        Range("Q" & NR).Value = Cells(a, 3).Value
        Range("R" & NR).Value = Cells(a, 5).Value
        Range("S" & NR).Value = Left(Cells(a + 1, 2), Len(Cells(a + 1, 2)) - 3)
        Sp = Split(Cells(a + 1, 3), " ")
        Range("T" & NR).Value = Sp(0)
        For aa = a To Iary(i + 1) - 1 Step 1
          If InStr(Cells(aa, 6), "Delivery no.") > 0 Then
            Range("U" & NR).Value = Cells(aa, 7).Value
            Exit For
          End If
        Next aa
      ElseIf b > 1 Then
        NR = NR + 1
        Range("L" & NR).Resize(, 4).Value = Range("L" & NR - 1).Resize(, 4).Value
        Range("P" & NR).Value = Cells(a, 1).Value
        Range("Q" & NR).Value = Cells(a, 3).Value
        Range("R" & NR).Value = Cells(a, 5).Value
        Sp = Split(Cells(a + 1, 2), " ")
        Range("S" & NR).Value = Sp(0)
        Sp = Split(Cells(a + 1, 3), " ")
        Range("T" & NR).Value = Sp(0)
        For aa = a To Iary(i + 1) - 1 Step 1
          If InStr(Cells(aa, 6), "Delivery no.") > 0 Then
            Range("U" & NR).Value = Cells(aa, 7).Value
            Exit For
          End If
        Next aa
        Range("U" & NR).Value = Range("U" & NR - 1).Value
        Range("V" & NR).Value = Range("V" & NR - 1).Value
      End If
    Else
      'do nothing
    End If
  Next a
  b = 0
Next i
Columns("L:V").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV4 macro.

DEBR · May 15, 2011

hiker95

Thank you for all the help. Everything is perfect.

DEBR

hiker95 · May 15, 2011

DEBR,

Per your request in your Private Message:

Training / Books / Sites

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

What is a Visual Basic Module?
http://www.emagenit.com/VBA Folder/what_is_a_vba_module.htm

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=2417&o=101881&l=dis&q=Where+to+paste+code+in+the+Excel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

Excel Tutorials / Video Tutorials - Functions
http://www.contextures.com/xlFunctions02.html

http://www.xl-central.com/index.html

http://www.datapigtechnologies.com/ExcelMain.htm

http://www.contextures.com/xlDataVal02.html

Cascading queries

http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

http://www.contextures.com/xlDataVal05.html
http://www.contextures.com/xlDataVal08.html#Larger

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/

http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA (Excel Power Programming With Vba)
by John Walkenbach

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml

Professional Excel Development
by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)

Excel 2002 VBA: Programmers Reference
by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)

VB & VBA in a Nutshell: The Language
(http://www.amazon.co.uk/VB-VBA-Nutsh...4671189&sr=1-2)

Writing Excel Macros with VBA
(http://www.amazon.co.uk/Writing-Exce...4671189&sr=1-3)

User Form Creation
http://www.contextures.com/xlUserForm01.html

DonkeyOte: My Recommended Reading
Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
http://www.xtremevbtalk.com/showthread.php?t=296012

Pivot Intro
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Email from XL - VBA
http://www.rondebruin.nl/sendmail.htm

Outlook VBA
http://www.outlookcode.com/article.aspx?ID=40

Function Dictionary
http://www.xlfdic.com/

Function Translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/management-dashboards-excel/

Excel Dashboard / Scorecard Ebook
http://www.qimacros.com/excel-dashboard-scorecard.html

And, as your skills increase, try answering posts on sites like:
http://www.mrexcel.com/
http://www.excelforum.com/
http://www.ozgrid.com/
http://www.vbaexpress.com/portal.php

Re Arranging Data in Excel

DEBR

New Member

hiker95

Well-known Member

Excel Facts

DEBR

New Member

hiker95

Well-known Member

hiker95

Well-known Member

DEBR

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Re Arranging Data in Excel

DEBR

New Member

hiker95

Well-known Member

Excel Facts

DEBR

New Member

hiker95

Well-known Member

hiker95

Well-known Member

DEBR

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock