Check for Duplicates

N

nebb

Cells A1 thru A50 contain a single number between 1 and 100. If I
wanted to determine if the number 8 appeared more than once, I could
use
‘if (countif(a1:a100,8)>=2,x,y)’. Is there a short way of checking to
see if any number between 1 and 100 appears more than once without
repeating the above formula 100 times?
 
P

Pete_UK

You could use Data | Filter | Advanced Filter to obtain unique values
from your list (copied to B1 downwards, say). Then you could compare
the length of your first list with the length of your second list - if
the column B list is shorter then you have duplicates. To find out
which are duplicated, enter this formula in C1 and copy down by
double-clicking the fill handle on C1:

=COUNTIF(A$1:A$100,B1)

Hope this helps.

Pete
 
B

Bob Phillips

Select say B1:b50

in the formula bar enter

=IF(ISERROR(SMALL(IF(COUNTIF($A$1:$A$50,$A$1:$A$50)>1,ROW($A1:$A50),""),ROW(
$A1:$A50))),"",
INDEX($A$1:$A$50,SMALL(IF(COUNTIF($A$1:$A$50,$A$1:$A$50)>1,ROW($A1:$A50),"")
,ROW($A1:$A50))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

This will show the duplicates many times, but that's not so bad.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top