I Currently receive data that is sorted by a contract number. There in many cases, are a number of contracts and amounts linked to one address.
What I would like to do is have a macro that can manipulate the data so that the address appears only once and the data that was in rows, now appears in columns under Contract 1 to contract 5 with the same info for the amount.
Below is a mock up of what i get and what i would like the result to look like.
Raw Data</SPAN>
<TBODY>
</TBODY>
Expected result</SPAN>
<TBODY>
</TBODY>
Any assistance will be greatly appreciated.
What I would like to do is have a macro that can manipulate the data so that the address appears only once and the data that was in rows, now appears in columns under Contract 1 to contract 5 with the same info for the amount.
Below is a mock up of what i get and what i would like the result to look like.
Raw Data</SPAN>
| A</SPAN></SPAN> | B</SPAN></SPAN> | C</SPAN></SPAN> | |
| Contract Number</SPAN></SPAN> | Address</SPAN></SPAN> | Amount owing</SPAN></SPAN> | |
| 1</SPAN></SPAN> | 4236</SPAN></SPAN> | 40 Owens Road</SPAN></SPAN> | $ 125.69 </SPAN></SPAN> |
| 2</SPAN></SPAN> | 4985</SPAN></SPAN> | 40 Owens Road</SPAN></SPAN> | $ 568.64 </SPAN></SPAN> |
| 3</SPAN></SPAN> | 6289</SPAN></SPAN> | 40 Owens Road</SPAN></SPAN> | $ 968.23 </SPAN></SPAN> |
| 4</SPAN></SPAN> | 3986</SPAN></SPAN> | 26 Bond Street</SPAN></SPAN> | $ 875.23 </SPAN></SPAN> |
| 5</SPAN></SPAN> | 6254</SPAN></SPAN> | 26 Bond Street</SPAN></SPAN> | $ 251.39 </SPAN></SPAN> |
| 6</SPAN></SPAN> | 7546</SPAN></SPAN> | 26 Bond Street</SPAN></SPAN> | $ 184.36 </SPAN></SPAN> |
| 7</SPAN></SPAN> | 9159</SPAN></SPAN> | 19 Patrick Drive</SPAN></SPAN> | $ 369.54 </SPAN></SPAN> |
| 8</SPAN></SPAN> | 1963</SPAN></SPAN> | 4 Kings Place</SPAN></SPAN> | $ 987.63 </SPAN></SPAN> |
| 9</SPAN></SPAN> | 5842</SPAN></SPAN> | 4 Kings Place</SPAN></SPAN> | $ 369.51 </SPAN></SPAN> |
| 10</SPAN></SPAN> | 6585</SPAN></SPAN> | 4 Kings Place</SPAN></SPAN> | $ 465.82 </SPAN></SPAN> |
| 11</SPAN></SPAN> | 2634</SPAN></SPAN> | 4 Kings Place</SPAN></SPAN> | $ 285.46 </SPAN></SPAN> |
| 12</SPAN></SPAN> | 8429</SPAN></SPAN> | 4 Kings Place</SPAN></SPAN> | $ 753.19 </SPAN></SPAN> |
<TBODY>
</TBODY>
Expected result</SPAN>
| A</SPAN></SPAN> | B</SPAN></SPAN> | C</SPAN></SPAN> | D</SPAN></SPAN> | E</SPAN></SPAN> | F</SPAN></SPAN> | G</SPAN></SPAN> | H</SPAN></SPAN> | I</SPAN></SPAN> | J</SPAN></SPAN> | K</SPAN></SPAN> | L</SPAN></SPAN> | |
| 1</SPAN></SPAN> | Address</SPAN></SPAN> | Contract 1</SPAN></SPAN> | Contract 2</SPAN></SPAN> | Contract 3</SPAN></SPAN> | Contract 4</SPAN></SPAN> | Contract 5</SPAN></SPAN> | Amt Owing 1</SPAN></SPAN> | Amt Owing 2</SPAN></SPAN> | Amt Owing 3</SPAN></SPAN> | Amt Owing 4</SPAN></SPAN> | Amt Owing 5</SPAN></SPAN> | Total owing</SPAN></SPAN> |
| 2</SPAN></SPAN> | 40 Owens Road</SPAN></SPAN> | 4236</SPAN></SPAN> | 4985</SPAN></SPAN> | 6289</SPAN></SPAN> | $125.69</SPAN></SPAN> | $568.64</SPAN></SPAN> | $968.23</SPAN></SPAN> | $1,662.56</SPAN></SPAN> | ||||
| 3</SPAN></SPAN> | 26 Bond Street</SPAN></SPAN> | 3986</SPAN></SPAN> | 6254</SPAN></SPAN> | 7546</SPAN></SPAN> | $ 875.23</SPAN></SPAN> | $ 251.39</SPAN></SPAN> | $ 184.36</SPAN></SPAN> | $1,310.98</SPAN></SPAN> | ||||
| 4</SPAN></SPAN> | 19 Patrick Drive</SPAN></SPAN> | 9159</SPAN></SPAN> | $369.54</SPAN></SPAN> | $369.54</SPAN></SPAN> | ||||||||
| 5</SPAN></SPAN> | 4 Kings Place</SPAN></SPAN> | 1963</SPAN></SPAN> | 5842</SPAN></SPAN> | 6585</SPAN></SPAN> | 2634</SPAN></SPAN> | 8429</SPAN></SPAN> | $987.63</SPAN></SPAN> | $369.51</SPAN></SPAN> | $465.82</SPAN></SPAN> | $285.46</SPAN></SPAN> | $753.19</SPAN></SPAN> | $2,861.61</SPAN></SPAN> |
<TBODY>
</TBODY>
Any assistance will be greatly appreciated.
