Thread: Comprehensive Duplicate Formula

Someone at work asked me to setup an excel spreadsheet to meet these specifications:
He wants to be able to type in an email address into a list and have excel tell him instantly if it is a duplicate.
Or
Type an email address in the same cell each time and then have excel add it to a list only if it is not already there.

Is there a way to do this, or should he just sort the list alphabetically and check it each time he adds?

Thanks!

one way is with conditional formatting
assuming list is in column a
select the range you expect to use say a1 to a200
use format/conditional format
choose
formula is
enter this formula
=MATCH(INDIRECT("A"&ROW()),INDIRECT("$A$1:A"&ROW()-1),0)
choose a cell colour.
exit
any duplicate added to list will then highlight