Hi,
I want to create a macro that matches each record from a worksheet I call "ATemplate" with another worksheet I call "BTemplate" using an unique ID (the ID row title is called "AT2RecID") that exists in both worksheets. For the sake of simplicity I only used several records as opposed to the thousands that I have in both worksheets. Below are representations of what is found in both worksheets: ATemplate and BTemplate.
This is what the ATemplate looks like, the row titles start in Row 1 from columns A through M:
<TBODY>
</TBODY>
This is what the BTemplate looks like, the row titles start in Row 1 from columns A through P:
<TBODY>
</TBODY>
This is what I want the Macro to create in a new worksheet, ROW TITLES FOR THE NEW WORKSHEET START IN ROW 2 AND THE DATA STARTS IN ROW 3. Row Titles B THROUGH M along with its associated data from worksheet "ATemplate" come over and Row Titles O and P along with its associated data from worksheet "BTemplate" come over. Below is a visual of what the results in a new worksheet should look like and they from Columns A through N. Again the records are matched up by the row title "AT2RecID" found in both worksheets:
<TBODY>
</TBODY>
thanks,
BC
I want to create a macro that matches each record from a worksheet I call "ATemplate" with another worksheet I call "BTemplate" using an unique ID (the ID row title is called "AT2RecID") that exists in both worksheets. For the sake of simplicity I only used several records as opposed to the thousands that I have in both worksheets. Below are representations of what is found in both worksheets: ATemplate and BTemplate.
This is what the ATemplate looks like, the row titles start in Row 1 from columns A through M:
| AT1</SPAN> | AT2RecID</SPAN> | AT3</SPAN> | AT4</SPAN> | AT5</SPAN> | AT6</SPAN> | AT7</SPAN> | AT8Period</SPAN> | AT9</SPAN> | AT10</SPAN> | AT11Duration</SPAN> | AT12StartDate</SPAN> | AT13EndDate</SPAN> |
| blah</SPAN> | A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | blah</SPAN> | blah</SPAN> | 322</SPAN> | 3/17/2011</SPAN> | 6/25/2012</SPAN> |
| blah</SPAN> | A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | blah</SPAN> | blah</SPAN> | 210</SPAN> | 6/26/2012</SPAN> | 4/26/2013</SPAN> |
| blah</SPAN> | A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | blah</SPAN> | blah</SPAN> | 284</SPAN> | 4/29/2013</SPAN> | 6/13/2014</SPAN> |
<TBODY>
</TBODY>
This is what the BTemplate looks like, the row titles start in Row 1 from columns A through P:
| BT1</SPAN> | AT2RecID</SPAN> | BT3</SPAN> | BT4</SPAN> | BT5</SPAN> | BT6</SPAN> | BT7</SPAN> | BT8</SPAN> | BT9</SPAN> | BT10</SPAN> | BT11</SPAN> | BT12</SPAN> | BT13</SPAN> | BT14Period</SPAN> | BT15Resource</SPAN> | BT16Amount</SPAN> |
| blah</SPAN> | A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | B</SPAN> | 0.30</SPAN> |
| blah</SPAN> | A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | C</SPAN> | 1.30</SPAN> |
| blah</SPAN> | A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | E</SPAN> | 2.80</SPAN> |
| blah</SPAN> | A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | P</SPAN> | 0.20</SPAN> |
| blah</SPAN> | A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | Q</SPAN> | 0.40</SPAN> |
| blah</SPAN> | A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | ES</SPAN> | 0.90</SPAN> |
| blah</SPAN> | A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | B</SPAN> | 0.40</SPAN> |
| blah</SPAN> | A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | C</SPAN> | 1.50</SPAN> |
| blah</SPAN> | A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | E</SPAN> | 2.90</SPAN> |
| blah</SPAN> | A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | P</SPAN> | 0.90</SPAN> |
| blah</SPAN> | A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | Q</SPAN> | 1.10</SPAN> |
| blah</SPAN> | A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | ES</SPAN> | 0.90</SPAN> |
| blah</SPAN> | A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | B</SPAN> | 0.40</SPAN> |
| blah</SPAN> | A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | C</SPAN> | 1.50</SPAN> |
| blah</SPAN> | A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | E</SPAN> | 3.40</SPAN> |
| blah</SPAN> | A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | P</SPAN> | 1.10</SPAN> |
| blah</SPAN> | A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | Q</SPAN> | 2.00</SPAN> |
| blah</SPAN> | A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | ES</SPAN> | 0.90</SPAN> |
<TBODY>
</TBODY>
This is what I want the Macro to create in a new worksheet, ROW TITLES FOR THE NEW WORKSHEET START IN ROW 2 AND THE DATA STARTS IN ROW 3. Row Titles B THROUGH M along with its associated data from worksheet "ATemplate" come over and Row Titles O and P along with its associated data from worksheet "BTemplate" come over. Below is a visual of what the results in a new worksheet should look like and they from Columns A through N. Again the records are matched up by the row title "AT2RecID" found in both worksheets:
| AT2RecID</SPAN> | AT3</SPAN> | AT4</SPAN> | AT5</SPAN> | AT6</SPAN> | AT7</SPAN> | AT8Period</SPAN> | AT9</SPAN> | AT10</SPAN> | AT11Duration</SPAN> | AT12StartDate</SPAN> | AT13EndDate</SPAN> | BT15Resource</SPAN> | BT16Amount</SPAN> |
| A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | blah</SPAN> | blah</SPAN> | 322</SPAN> | 3/17/2011</SPAN> | 6/25/2012</SPAN> | B</SPAN> | 0.30</SPAN> |
| A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | blah</SPAN> | blah</SPAN> | 322</SPAN> | 3/17/2011</SPAN> | 6/25/2012</SPAN> | C</SPAN> | 1.30</SPAN> |
| A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | blah</SPAN> | blah</SPAN> | 322</SPAN> | 3/17/2011</SPAN> | 6/25/2012</SPAN> | E</SPAN> | 2.80</SPAN> |
| A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | blah</SPAN> | blah</SPAN> | 322</SPAN> | 3/17/2011</SPAN> | 6/25/2012</SPAN> | P</SPAN> | 0.20</SPAN> |
| A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | blah</SPAN> | blah</SPAN> | 322</SPAN> | 3/17/2011</SPAN> | 6/25/2012</SPAN> | Q</SPAN> | 0.40</SPAN> |
| A1</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | A</SPAN> | blah</SPAN> | blah</SPAN> | 322</SPAN> | 3/17/2011</SPAN> | 6/25/2012</SPAN> | ES</SPAN> | 0.90</SPAN> |
| A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | blah</SPAN> | blah</SPAN> | 210</SPAN> | 6/26/2012</SPAN> | 4/26/2013</SPAN> | B</SPAN> | 0.40</SPAN> |
| A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | blah</SPAN> | blah</SPAN> | 210</SPAN> | 6/26/2012</SPAN> | 4/26/2013</SPAN> | C</SPAN> | 1.50</SPAN> |
| A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | blah</SPAN> | blah</SPAN> | 210</SPAN> | 6/26/2012</SPAN> | 4/26/2013</SPAN> | E</SPAN> | 2.90</SPAN> |
| A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | blah</SPAN> | blah</SPAN> | 210</SPAN> | 6/26/2012</SPAN> | 4/26/2013</SPAN> | P</SPAN> | 0.90</SPAN> |
| A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | blah</SPAN> | blah</SPAN> | 210</SPAN> | 6/26/2012</SPAN> | 4/26/2013</SPAN> | Q</SPAN> | 1.10</SPAN> |
| A2</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | S</SPAN> | blah</SPAN> | blah</SPAN> | 210</SPAN> | 6/26/2012</SPAN> | 4/26/2013</SPAN> | ES</SPAN> | 0.90</SPAN> |
| A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | blah</SPAN> | blah</SPAN> | 284</SPAN> | 4/29/2013</SPAN> | 6/13/2014</SPAN> | B</SPAN> | 0.40</SPAN> |
| A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | blah</SPAN> | blah</SPAN> | 284</SPAN> | 4/29/2013</SPAN> | 6/13/2014</SPAN> | C</SPAN> | 1.50</SPAN> |
| A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | blah</SPAN> | blah</SPAN> | 284</SPAN> | 4/29/2013</SPAN> | 6/13/2014</SPAN> | E</SPAN> | 3.40</SPAN> |
| A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | blah</SPAN> | blah</SPAN> | 284</SPAN> | 4/29/2013</SPAN> | 6/13/2014</SPAN> | P</SPAN> | 1.10</SPAN> |
| A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | blah</SPAN> | blah</SPAN> | 284</SPAN> | 4/29/2013</SPAN> | 6/13/2014</SPAN> | Q</SPAN> | 2.00</SPAN> |
| A3</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | blah</SPAN> | K</SPAN> | blah</SPAN> | blah</SPAN> | 284</SPAN> | 4/29/2013</SPAN> | 6/13/2014</SPAN> | ES</SPAN> | 0.90</SPAN> |
<TBODY>
</TBODY>
thanks,
BC
