Mark duplicate emails

[books4cars]

I have 2 columns and about 7000 rows. Column 1 is name, column 2 is email address.

I want to ensure each email address is associated with only 1 name. Either by conditional formatting or otherwise.

Much obliged.

 

[Macropod]

What have you tried?

 

[books4cars]

Sorting them by email address and looking through. I'm stuck on how else to identify which email addresses need further investigation.

 

[Macropod]

You could, for example, insert a formula in column C:
=COUNTIF(B:B,B1)
and copy down as far as needed. Every entry will display the frequency with which that entry occurs. You could even turn it into a conditional formatting formula for column B:
=COUNTIF(B:B,B1)>1

 

[books4cars]

You could, for example, insert a formula in column C:
=COUNTIF(B:B,B1)
and copy down as far as needed. Every entry will display the frequency with which that entry occurs. You could even turn it into a conditional formatting formula for column B:
=COUNTIF(B:B,B1)>1

THis would tell me if the email address is duplicated in the column, correct? Is there any way to extend it to see if any of instances of the email addresses are next to the same or multile values?

Like the instance below, for example.


John Smith j@smith.com
Joe Smith j@smith.com

 

[Macropod]

And just how likely do you suppose it would be that John Smith and Joe Smith have the same email address? Surely the only possibility for duplicate email addresses with different names is because either one or both email addresses and/or names is incorrect.

 

[books4cars]

And just how likely do you suppose it would be that John Smith and Joe Smith have the same email address? Surely the only possibility for duplicate email addresses with different names is because either one or both email addresses and/or names is incorrect.

Unfortunately, the data I have has numerous instances such as this. I'm trying to clean up the mess.

 

[Macropod]

To find name & email address duplicates, you could use:
=SUMPRODUCT((A:A=A1)*(B:B=B1))
or
=SUMPRODUCT((A:A=A1)*(B:B=B1))> 1
for conditional formatting.

Similarly, to find email address duplicates with different names, you could use:
=SUMPRODUCT((A:A<>A1)*(B:B=B1))
or
=SUMPRODUCT((A:A<>A1)*(B:B=B1))> 1
for conditional formatting.

Finally, to find name duplicates with different email addresses, you could use:
=SUMPRODUCT((A:A=A1)*(B:B<>B1))
or
=SUMPRODUCT((A:A=A1)*(B:B<>B1))> 1
for conditional formatting.