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

Aladin


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

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!


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.

Aladin


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.