Need help in coming up with a formula or VBA macro to find the duplicate records based on multiple conditions and highlight the duplicate records foun

[bhandarx]

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.

[TABLE="width: 882"]
<tbody>[TR]
[TD]Logic[/TD]
[TD="colspan: 5"]All of the conditions below must be met for it to be a duplicate.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Column[/TD]
[TD]Condition[/TD]
[TD]Comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Name[/TD]
[TD]Same[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vertical[/TD]
[TD][/TD]
[TD][/TD]
[TD]Same[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Customer[/TD]
[TD]Same or blank[/TD]
[TD="colspan: 6"]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.[/TD]
[/TR]
[TR]
[TD="colspan: 3"]comments[/TD]
[TD]Same or blank[/TD]
[TD="colspan: 6"]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.[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Additive Flag[/TD]
[TD]N[/TD]
[TD="colspan: 6"]Overlapping records must have N for additive flag. If one is N and the other is Y is it NOT a duplicate.[/TD]
[/TR]
[TR]
[TD="colspan: 3"]ID Number[/TD]
[TD]Different[/TD]
[TD="colspan: 6"]ID numbers of 2 matching records must be different to mark them as duplicates[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Subjects[/TD]
[TD]Same or Partial[/TD]
[TD="colspan: 5"]Would be duplicate if one is Maths and one is Maths/English[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Start Date[/TD]
[TD]Overlap[/TD]
[TD="colspan: 6"]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[/TD]
[/TR]
[TR]
[TD="colspan: 3"]End Date[/TD]
[TD]Overlap[/TD]
[TD="colspan: 6"]If there is ANY overlap in the time periods of the records it is a duplicate[/TD]
[/TR]
</tbody>[/TABLE]

Can this be achieved in a single formula or it needs to be done as part of multiple steps in VBA macro? Please help


[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]VERTICAL[/TD]
[TD]ID NUMBER[/TD]
[TD]SUBJECTS[/TD]
[TD]COMMENTS[/TD]
[TD]CUSTOMER[/TD]
[TD]Additive Flag[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Is Duplicate[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Vert1[/TD]
[TD]95547[/TD]
[TD]Maths[/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]11/1/2013[/TD]
[TD]1/31/2014[/TD]
[TD]Y[/TD]
[TD]Duplicates as all the conditions mentioned above are met.[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Vert2[/TD]
[TD]95553[/TD]
[TD]English[/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]11/1/2013[/TD]
[TD]10/31/2014[/TD]
[TD]N[/TD]
[TD]Not duplicate as the dates are not overlapping[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Vert1[/TD]
[TD]95535[/TD]
[TD]Maths\English\History[/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]12/1/2013[/TD]
[TD]1/15/2014[/TD]
[TD]Y[/TD]
[TD]Duplicates as all the conditions mentioned above are met.[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]Vert3[/TD]
[TD]92249[/TD]
[TD]Chemistry[/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]8/1/2013[/TD]
[TD]10/31/2013[/TD]
[TD]N[/TD]
[TD]Not Duplicate[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Vert2[/TD]
[TD]92250[/TD]
[TD]English[/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD]11/1/2014[/TD]
[TD]11/21/2014[/TD]
[TD]N[/TD]
[TD]Not duplicate as the dates are not overlapping[/TD]
[/TR]
</tbody>[/TABLE]


Best Regards,
Vas