Check for Duplicate data cell wise

Hi,

I have a Excel Sheet contains 3 sheets [Leads, Props & Agents]
I keep adding data in all Sheets time to time. Some times duplicates has been added.
I want to check the duplication every time after adding a data to any of these sheets.

For Example:-
If I Add data in sheet Props. I will run a Macro to check the duplicates with other 2 sheets.
If I Add data in sheet Leads. I will run a Macro to check the duplicates with other 2 sheets.

If duplicates found then I need duplicates summary as in sheet "Duplicates"
In Column A I want duplicate mobile number (If Duplicate found) and in Column B I want the sheet name which contains duplicate data.
In Column C I want duplicate Land line number (If Duplicate found) and in Column D I want the sheet name which contains duplicate data.
In Column E I want duplicate email id (If Duplicate found) and in Column F I want the sheet name which contains duplicate data.

SO every time I will add data and I will run macro.


Note: some cell contains Multiple numbers and email id separated by "/"
So Check all the number email individually

wnimax, I have removed your workbook, it looked like it comtained real email and phone numbers. If this is not the case, I apoligise.

Based on your question, I would st ask - why not keep all data on 1 sheet, with an extra column to show Leads, Props & Agents. That way it would be much easier to ID the duplicates

Take a look at the attached. I have combined all 3 sheets into 1, then tested for duplicates 2 different ways.

1. I applied Conditional Formatting to the data in column E using this rule...
=COUNTIF($E$2:$E2,E2)>1

2. An an alternative, I used a helper column to show you where the duplicates are...
=IF(COUNTIF($E$2:$E2,E2)>1,"Duplicate on row "&MATCH(E2,$E$2:$E$37,0)+1,"")

ok, good luck