Macro to Move Data with a Row if Criteria is Met

[Michael151]

Hello all,

Trying to write a macro that will move and delete info within rows based on certain criteria.

In the Title column (marked in the row 1 header), if there are two titles that match and are sequential, the following happens:
- On the row containing the first instance of the duplicate title, the dates in the HB Start Date and HB End Date columns are moved into the Start Date and End Date columns (replace any existing data in these cells). The data in the Hdate and Gdate columns in this row is deleted.
- On the row below this (containing the second instance of the duplicate title), delete the Gdate, HB Start Date, HB End Date, and Hdate data in this row.

In the example below, Title2 is a duplicate, so the macro will move the data in the two rows for Title2:

Before Macro:

<table border="0" cellpadding="0" cellspacing="0" width="505"><col style="width: 48pt;" width="64" span="2"> <col style="width: 70pt;" width="93"> <col style="width: 65pt;" width="86"> <col style="width: 53pt;" width="70"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Title</td> <td class="xl24" style="width: 48pt;" width="64">Gdate</td> <td class="xl24" style="width: 70pt;" width="93">HB Start Date</td> <td class="xl24" style="width: 65pt;" width="86">HB End Date</td> <td class="xl24" style="width: 53pt;" width="70">Start Date</td> <td class="xl24" style="width: 48pt;" width="64">End Date</td> <td class="xl24" style="width: 48pt;" width="64">Hdate</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title2</td> <td>gdate1</td> <td>date1</td> <td>date2</td> <td>
</td> <td>
</td> <td>hdate1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title2</td> <td>gdate1</td> <td>date1</td> <td>date2</td> <td>
</td> <td>
</td> <td>hdate1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title4</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
After Macro:

<table border="0" cellpadding="0" cellspacing="0" width="505"><col style="width: 48pt;" width="64" span="2"> <col style="width: 70pt;" width="93"> <col style="width: 65pt;" width="86"> <col style="width: 53pt;" width="70"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Title</td> <td class="xl24" style="width: 48pt;" width="64">Gdate</td> <td class="xl24" style="width: 70pt;" width="93">HB Start Date</td> <td class="xl24" style="width: 65pt;" width="86">HB End Date</td> <td class="xl24" style="width: 53pt;" width="70">Start Date</td> <td class="xl24" style="width: 48pt;" width="64">End Date</td> <td class="xl24" style="width: 48pt;" width="64">Hdate</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title2</td> <td>
</td> <td>
</td> <td>
</td> <td>date1</td> <td>date2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title2</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Title4</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>


Would need to loop through several thousand rows. The columns are not always in the same column letters, which is why I’d like to use the header in row 1 to identify the columns. Also, there may be instances of triple titles that match. In this case, do nothing – the macro should only move dates if it finds only two sequential titles. If there are more than this in the Title column, then do nothing.

Please let me know if there are any questions. Help is most appreciated on this – thank you!

 

[Michael151]

Something along the lines of:

IF Title in row 1 matches Title in row 2, THEN:

Move HB Start Date and HB End Date into Start and End Date columns

THEN in row 2, delete Gdate, HB Start Date, HB End Date and Hdate

Gdate and Hdate can be in any order or any column, which is why i'd like to use the header in row 1 to identify these columns.

 

[Michael151]

Is there anyone who might be able to help with this - I'm pretty stumped...

Thank you!