MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Alert for matching cells in single column


Posted by Daniel Lee on February 12, 2002 11:36 AM

I have a number of email lists that I am in charge of managing. It's very easy to export these lists into Excel with the "send to" function of Windows. One of the dreaded tasks though is finding multiple identical email addresses in the list. Sometimes people sign up to be on the same list multiple times without realizing it and the number of email addresses can reach the 1000s. What's the best way to find identical values in a single column and having it alert me to the multiple addresses? In other words, is it possible to have the address that is duplicated copied to another column or highlighted somehow? I have read the discussion board thoroughly and checked the archives. I can't find something that addresses this. Thanks!


Posted by Aladin Akyurek on February 12, 2002 11:47 AM

This has addressed many times, but it is hard to locate them (my experience).

Select all of the cells of the e-mail addresses.

I'll suppose them to be in A from A2 on in what follows (change to suit).

Activate Format|Conditional Formatting.
Choose Formula Is for Condition 1.
Enter as formula:

=COUNTIF(A:A,A2)>1

Activate Format.

Choose a color (red maybe?) on the Patterns tab.

Click OK, OK.

=========

Posted by George A. on February 12, 2002 11:55 AM

If I understand what you need here is a quick and dirty way.

Select the range and then do an advanced filter.

Check 'copy to another range'
Criteria (use the email column header)
Check unique records only.
Copy to another range.

and that should work