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
