Hi,
I am trying to come up with a formula or an excel VBA macro to identify the duplicate records present in an excel sheet based on multiple criterion and highlight the duplicate records found. I am using Excel 2010. Here is the sample data which I have and criterion to consider 2 records to be duplicates.
<tbody>
</tbody>
Can this be achieved in a single formula or it needs to be done as part of multiple steps in VBA macro? Please help
<tbody>
</tbody>
Best Regards,
Vas
I am trying to come up with a formula or an excel VBA macro to identify the duplicate records present in an excel sheet based on multiple criterion and highlight the duplicate records found. I am using Excel 2010. Here is the sample data which I have and criterion to consider 2 records to be duplicates.
Logic | All of the conditions below must be met for it to be a duplicate. | ||||||||
Column | Condition | Comments | |||||||
Name | Same | ||||||||
Vertical | Same | ||||||||
Customer | Same or blank | If one record is blank and the other has a value they are not duplicates, If both are same or both are blank it is a duplicate. | |||||||
comments | Same or blank | If one record is blank and the other has a value they are not duplicates, If both are same or both are blank it is a duplicate. | |||||||
Additive Flag | N | Overlapping records must have N for additive flag. If one is N and the other is Y is it NOT a duplicate. | |||||||
ID Number | Different | ID numbers of 2 matching records must be different to mark them as duplicates | |||||||
Subjects | Same or Partial | Would be duplicate if one is Maths and one is Maths/English | |||||||
Start Date | Overlap | If there is ANY overlap in the time periods of the records it is a duplicate (e.g. If the start date or end date overlaps it's a duplicate | |||||||
End Date | Overlap | If there is ANY overlap in the time periods of the records it is a duplicate |
<tbody>
</tbody>
Can this be achieved in a single formula or it needs to be done as part of multiple steps in VBA macro? Please help
NAME | VERTICAL | ID NUMBER | SUBJECTS | COMMENTS | CUSTOMER | Additive Flag | Start Date | End Date | Is Duplicate | Remarks |
John | Vert1 | 95547 | Maths | N | 11/1/2013 | 1/31/2014 | Y | Duplicates as all the conditions mentioned above are met. | ||
Tom | Vert2 | 95553 | English | N | 11/1/2013 | 10/31/2014 | N | Not duplicate as the dates are not overlapping | ||
John | Vert1 | 95535 | Maths\English\History | N | 12/1/2013 | 1/15/2014 | Y | Duplicates as all the conditions mentioned above are met. | ||
Dave | Vert3 | 92249 | Chemistry | N | 8/1/2013 | 10/31/2013 | N | Not Duplicate | ||
Tom | Vert2 | 92250 | English | N | 11/1/2014 | 11/21/2014 | N | Not duplicate as the dates are not overlapping |
<tbody>
</tbody>
Best Regards,
Vas