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?


--
nebb
------------------------------------------------------------------------
nebb's Profile: http://www.excelforum.com/member.php...fo&userid=8981
View this thread: http://www.excelforum.com/showthread...hreadid=511727

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

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)

"nebb" wrote in message
...

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?


--
nebb
------------------------------------------------------------------------
nebb's Profile:
http://www.excelforum.com/member.php...fo&userid=8981
View this thread: http://www.excelforum.com/showthread...hreadid=511727