MrExcel.com - Your One Stop for Excel Tips & Solutions

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

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

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


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

Posted by Paul on October 15, 2001 4:38 AM

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

I was hoping it could be done with one formula, wishful thinking, Thanks

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

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

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","")

Aladin

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

Thanks Aladin thats what I need


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

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


Aladin,
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!

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

SUMPRODUCT

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.

Aladin