finding duplicates

hi

i've a list of 200 phone nos. in cell E1 to E200 and the list continue to
grow.

I want a WARN message everytime i enter a phone number that is already in
the list as "Duplicate Phone Number".

Kindly help

reg
deepak

Debra
Thu Jun 03 05:24:34 CDT 2004

You can use Data Validation for this. Chip Pearson has instructions on
his web site:

http://www.cpearson.com/excel/NoDupEntry.htm

Deepak wrote:
> i've a list of 200 phone nos. in cell E1 to E200 and the list continue to
> grow.
>
> I want a WARN message everytime i enter a phone number that is already in
> the list as "Duplicate Phone Number".

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Freemini
Thu Jun 03 05:26:26 CDT 2004

One way to do this :

In cell F1 type =COUNTIF($E$1:$E$200,E1) and then copy down to F200

if there are 2 telephone numbers the same this will equal more than 1

In an empty cell type your warning and make the font colour white s
that it is not visible normally. Now use Format>Conditional format an
set the formula is.. and condition 1 as max(E:E)>1 and finally choose
suitable colour for the background.

Now every time an entry is duplicated you message will be visible.

Finally as your list grows alter the formula to suit.

hth
Mik

--
Message posted from http://www.ExcelForum.com

David
Thu Jun 03 13:35:40 CDT 2004

Finding Duplicate Phone Numbers:
you already have Validation (Debra Dalgleish's page),
and you have a helper column (like Chip Pearson's page)

Another option would be to use Conditional Formatting
Select the column or the entire sheet, I think the column probably best.

Duplicate identifications (yellowish first among duplicates, greenish for actual duplicates)
formula 1 is: =IF(COUNTIF($D:$D,$D1)>1,COUNTIF($D$1:$D1,$D1)=1)
formula 2 is: =IF(COUNTIF($D:$D,$D1)>1,COUNTIF($D$1:$D1,$D1)>1)

If you want to treat the first occurrence and duplicate as an exception
then all you need is a formula like this: (note no need to expand the range later on)
Formula 1 is: =COUNTIF($D:$D,$D1)>1

More information on Conditional Formatting
Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

One advantage of C.F. on a slower computer, touch typing is that
you don't have to wait on validation, and you see the C.F. but
you can continue typing and fix it a few minutes later instead of
interrupting what you are doing.

Another might be you don't need an additional column, so you
are more likely to be able see everything at one time in row.

The methods are all practically the same, as you can tell
from the formulas. Actually the formulas I used don't need
adjusting as you add more rows, but you can see that and
make the change yourself.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Deepak
Fri Jun 04 01:56:20 CDT 2004

THANKS ALL.

IT REALLY HELPS.


Deepak <deepak5142@hotmail.com> wrote in message
news:OLAAQ1TSEHA.2780@TK2MSFTNGP09.phx.gbl...
> hi
>
> i've a list of 200 phone nos. in cell E1 to E200 and the list continue to
> grow.
>
> I want a WARN message everytime i enter a phone number that is already in
> the list as "Duplicate Phone Number".
>
> Kindly help
>
> reg
> deepak
>
>