Formula to delete Duplicates based on certain rules

boldcode · Apr 20, 2011

[FONT=&quot]I am trying to create a formula in excel that will get rid of duplicates based on some business rules.

Below I explain the situation at hand.

I have the following 4 columns in my dataset (these are the actual names of the columns):

Under Column A the name of the column is: Announcement Number
Under Column B the name of the column is: Issue Date
Under Column C the name of the column is: Certificate Returned to HR (Date)
Under Column D the name of the column is: K or D and the formula would stored in Cell D2-D12

[/FONT]
[FONT=&quot]Example of dataset and what the results should look like:[/FONT]
<table width="891" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:3430;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:6476;width:152pt" width="202"> <col style="mso-width-source:userset;mso-width-alt:4608;width:108pt" width="144"> <col style="mso-width-source:userset;mso-width-alt:10700;width:251pt" width="334"> <col style="mso-width-source:userset;mso-width-alt:3328;width:78pt" width="104"> <tbody><tr style="height:13.8pt" height="18"> <td style="height:13.8pt;width:80pt" width="107" height="18">
</td> <td class="xl66" style="width:152pt" width="202">Column A</td> <td class="xl66" style="border-left:none;width:108pt" width="144">Column B</td> <td class="xl66" style="border-left:none;width:251pt" width="334">Column C</td> <td class="xl66" style="border-left:none;width:78pt" width="104">Column D</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt" height="18">Row 1</td> <td class="xl65" style="border-top:none">Announcement Number</td> <td class="xl65" style="border-top:none;border-left:none">Issue Date</td> <td class="xl65" style="border-top:none;border-left:none">Certificate Returned to HR (Date)</td> <td class="xl65" style="border-top:none;border-left:none">K or D</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 2</td> <td class="xl65" style="border-top:none">1003098SQMP</td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">Delete</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 3</td> <td class="xl66" style="border-top:none">1003098SQMP</td> <td class="xl69" style="border-top:none;border-left:none">10/28/2010</td> <td class="xl69" style="border-top:none;border-left:none"> </td> <td class="xl66" style="border-top:none;border-left:none">Keep</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 4</td> <td class="xl65" style="border-top:none">1103001MCMP</td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">Delete</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 5</td> <td class="xl65" style="border-top:none">1103001MCMP</td> <td class="xl68" style="border-top:none;border-left:none">11/3/2010</td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">Delete</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 6</td> <td class="xl66" style="border-top:none">1103001MCMP</td> <td class="xl69" style="border-top:none;border-left:none">11/3/2010</td> <td class="xl69" style="border-top:none;border-left:none">12/16/2010</td> <td class="xl66" style="border-top:none;border-left:none">Keep</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 7</td> <td class="xl65" style="border-top:none">1103001MCMP</td> <td class="xl68" style="border-top:none;border-left:none">11/3/2010</td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">Delete</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 8</td> <td class="xl66" style="border-top:none">1103002JPMP</td> <td class="xl69" style="border-top:none;border-left:none"> </td> <td class="xl69" style="border-top:none;border-left:none"> </td> <td class="xl66" style="border-top:none;border-left:none">Keep</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 9</td> <td class="xl65" style="border-top:none">1103003KSMP</td> <td class="xl68" style="border-top:none;border-left:none">11/8/2010</td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">Delete</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 10</td> <td class="xl65" style="border-top:none">1103003KSMP</td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl68" style="border-top:none;border-left:none"> </td> <td class="xl65" style="border-top:none;border-left:none">Delete</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 11</td> <td class="xl66" style="border-top:none">1103003KSMP</td> <td class="xl69" style="border-top:none;border-left:none"> </td> <td class="xl69" style="border-top:none;border-left:none">12/8/2010</td> <td class="xl66" style="border-top:none;border-left:none">Keep</td> </tr> <tr style="height:13.8pt" height="18"> <td class="xl67" style="height:13.8pt;border-top:none" height="18">Row 12</td> <td class="xl66" style="border-top:none">1103005MCMP</td> <td class="xl69" style="border-top:none;border-left:none"> </td> <td class="xl69" style="border-top:none;border-left:none"> </td> <td class="xl66" style="border-top:none;border-left:none">Keep</td> </tr> </tbody></table>
[FONT=&quot][/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]So below are the 4 business rules:

1. If the Announcement Number is the same, then look at both columns B and C to see which PAIR of dates are greater than the other pair of dates in the other records with same Announcement Number and the word “keep” would appear next to that record with the pair of dates that are greater (e.g., In the case of rows 4, 5, 6, 7 the word “keep” would appear next to row 6 and all the other rows the word “delete” would appear next to rows 4, 5, and 7)

2. If the Announcement Number is the same, but no dates exist for column C then look at the dates in column B and keep the greater of the two and (e.g., In the case of rows 2 and 3 word “keep” would appear next to row 3 and the row the word “delete” would appear next to row 2)

3. If the Announcement Number is the same, and if one row has a date in Column B and nothing in column C and the other row has a date in Column C and nothing in column B then the word “keep” would appear next to the row in which it has a date in Column C and nothing in column B (e.g., In the case of rows 9, 10, 11the word “keep” would appear next to row 11 and the row the word “delete” would appear next to rows 9 and 10)

4. If the Announcement Number is not a duplicate then obviously the word “keep” would appear next to the row regardless if it has blank dates in columns B and C (for example rows 8 and 12)

So if I manually filter out the “deletes” in the dataset the only rows that would remain would be rows 3, 6, 8, 11, 12.

[/FONT]

Formula to delete Duplicates based on certain rules

boldcode

Active Member

Excel Facts

Similar threads

Forum statistics

Share this page

Formula to delete Duplicates based on certain rules

boldcode

Active Member

Excel Facts

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