finding duplicates

  • Thread starter Thread starter Deepak
  • Start date Start date
D

Deepak

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
 
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
 
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.
 
Back
Top