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.

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