I am putting together a spreadsheet. One of the columns is phone numbers. I want excel to tell me if I am duplicating an entry in that column. You know, that I already entered that phone number. Does anyone know how to do this? I tried asking Mr Paper Clip but he must be mad at me he has stopped answering my questions.
have you tried asking Mr. Waggy Tail Dog ? 
sometimes i wonder whether they’ll give me different answers 
excel seems to allow you to figure out duplicates, provided they contain a text string or text with numbers…
here’s somewhat of a work around for phone numbers…
select the phone number column, go to data/sort and sort the column ascending/descending whatever, you’ll be able to see duplicates easily.
thanks xash, but I am trying to get excel to tell me it’s a duplicate as I enter it. So I don’t enter it. I don’t really want to enter it then go back and figure out it’s a duplicate. And it is all numbers so autocomplete doesn’t work.
Highlight the cells in the column you want to validate.
Top menu: Data Validation
Settings TAB for Allow select Custom.
In the formula box put something like the following:
=COUNTIF($A$1:$A$1000,A1)=1
This one will prevent duplicates for the first 1000 rows of column A.
Use the Error Alert TAB to choose the action when a duplicate is attempted. Warning brings up a box with the error message and allows a choice to continue or stop.
AFAIK, Excel doesn’t have a really good duplicates detector. A rather awkward workaround I’ve used is to sort the phone numbers, then go to Data > Subtotals, and use the “Count” function to see how many times each phone number appears in your list.
don’t ask
Thanks that is exactly what I was looking for. That worked perfectly. Again thanks alot.
My pleasure.
If you use Excel much, bookmark these guys have lots of free tips which I’ve stolen before.