# Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

## Formula to check for duplicate numbers

Posted by Paul on October 14, 2001 5:33 PM
I need a formula to check for duplicate numbers in A1:AJ1, it need to return “Duplicate” if it fines a duplicate number or “” if all the numbers are different. I am using excel ’97, thanks

 Check out our Excel Resources

## =IF(COUNTIF(\$A\$1:\$AJ\$1,A1)>1,"Duplicate","")

Posted by Richard S on October 14, 2001 5:53 PM

=IF(COUNTIF(\$A\$1:\$AJ\$1,A1)>1,"Duplicate","") in cell A2, and copy to AJ2

## Re: =IF(COUNTIF(\$A\$1:\$AJ\$1,A1)>1,"Duplicate","")

Posted by Paul on October 15, 2001 4:38 AM
I was hoping it could be done with one formula, wishful thinking, Thanks

## Re: =IF(COUNTIF(\$A\$1:\$AJ\$1,A1)>1,"Duplicate","")

Posted by Aladin Akyurek on October 15, 2001 5:50 AM

If you want a single formula that just assesses whether or not duplicates exit in the target range, use:

=IF(SUMPRODUCT((COUNTIF(A1:AJ1,A1:AJ1)>1)+0)>1,"Duplicates exist","")

## Thanks Aladin thats what I need

Posted by Paul on October 15, 2001 6:41 AM

## Re: =IF(COUNTIF(\$A\$1:\$AJ\$1,A1)>1,"Duplicate","")

Posted by Don C on October 15, 2001 6:54 AM

Help me understand that formula. I understand the IF part, that's straightforward.

It's the SUMPRODUCT and COUNTIF part that I'm not understanding. Could you "walk" me through the formula, inside out?

For example, I tried just the COUNTIF(A1:AJ1,A1:AJ1) and get a zero, whether there is a duplicate in the range or not. What does this piece do in your formula?

Then there is the next ">1" which I might understand if I knew what the countif is returning.

Then the next two pieces, the "+0" and the second ">1"; I don't see what they do.

Thanks!

## SUMPRODUCT

Posted by Aladin Akyurek on October 15, 2001 7:50 AM

Sure, Don.

That's right. It's exactly what it should do: COUNTIF can only count the occurence of a single value at a time.

SUMPRODUCT forces COUNTIF(A1:AJ1,A1:AJ1) to return an array of counts for each value in A1:AJ1, say,

{1,0,2,1,0,...} [1]

What does this piece do in your formula?

The test COUNTIF(A1:AJ1,A1:AJ1)>1 is factualized as
{1,0,2,1,0,...}>1

which leads to another array, consisting of logical values:

{FALSE,FALSE,TRUE,FALSE,...} [2]

+0 in (COUNTIF(A1:AJ1,A1:AJ1)>1)+0 forces/coerces Excel to treat the logical values as numbers where TRUE=1 and FALSE=0, so [2] becomes:

{0,0,1,0,...} [3]

SUMPRODUCT, so to say, sums this array into a single numeric value, which is tested in the condition part.

The second 1 in >1 might be formulated as >0. A single duplicate will always produce a 2, that's why it doesn't matter much which you pick: >1 or >0.