Back to Forms in Excel VBA archive index

Back to archive home

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","") in cell A2, and copy to AJ2

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

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

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!

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.

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.