duplicate value query

[Harvey Waxman]

If I have a grid with nine squares, what formula can I place in a cell to
indicate that there is a duplicate value in one of the squares?

I don't see how I can use validation to effect this.

 

[JE McGimpsey]

If I have a grid with nine squares, what formula can I place in a cell to
indicate that there is a duplicate value in one of the squares?

I don't see how I can use validation to effect this.

This formula will return TRUE if all cells are unique, FALSE if there's
a duplicate (ignoring blanks):

=SUMPRODUCT((A1:C3<>"")/COUNTIF(A1:C3,A1:C3&""))=COUNTA(A1:C3)

To prevent duplicate entry using validation, select A1:C3 (with A1
active) and enter this simpler Custom validation criterion (check the
Ignore blanks checkbox):

Formula: =COUNTIF($A$1:$C$3,A1)=1

 

[Harvey Waxman]

This formula will return TRUE if all cells are unique, FALSE if there's
a duplicate (ignoring blanks):

=SUMPRODUCT((A1:C3<>"")/COUNTIF(A1:C3,A1:C3&""))=COUNTA(A1:C3)

To prevent duplicate entry using validation, select A1:C3 (with A1
active) and enter this simpler Custom validation criterion (check the
Ignore blanks checkbox):

Formula: =COUNTIF($A$1:$C$3,A1)=1

This is going to work just great!

Please forgive my obtuseness but could you explain them in English? The more I
can learn the less I'll bother you

Harvey

 

[JE McGimpsey]

Please forgive my obtuseness but could you explain them in English?
The more I can learn the less I'll bother you

Well, if *that's* the goal, I'll try to help you learn how to learn...<g>

You can experiment yourself with how these formulae work. Put the
numbers 1-9, in any order, in A1:C3.

The heart of the first formula is

SUMPRODUCT((A1:C3<>"")/COUNTIF(A1:C3,A1:C3&""))

so break it down a bit. Select E1:G3 and array-enter (CMD-RETURN):

=1/COUNTIF(A1:C3, A1:C3&"")

(It must be array-entered - SUMPRODUCT() is an array function that
doesn't require CMD-RETURN).

In E5:G7 array enter:

=(A1:C3<>"")/COUNTIF(A1:C3,A1:C3&"")

In E9 enter

=SUMPRODUCT((A1:C3<>"")/COUNTIF(A1:C3,A1:C3&""))

In E10 enter

=COUNTA(A1:C3)

In E11 enter

=SUMPRODUCT((A1:C3<>"")/COUNTIF(A1:C3,A1:C3&""))=COUNTA(A1:C3)

Now, start changing values in A1:C3. Duplicate one number. How do all
the formulae change? Enter that number in a third cell, now how do the
formulae change?

Restore the values 1-9 in A1:C3. Clear a cell. How do all the formulae
change?

You can do the same thing with the second formula. In E1 enter

=COUNTIF($A$1:$C$3, A1) = 1

copy that formula to the range E1:G3. Start changing values in A1:C3.
Duplicate some. What happens?

If you can't figure out what's going on, post back, but the above
technique is how I approach formulae that seem obscure...

 

[Harvey Waxman]

Well, if *that's* the goal, I'll try to help you learn how to learn...<g>

You can experiment yourself with how these formulae work. Put the
numbers 1-9, in any order, in A1:C3.

The heart of the first formula is

SUMPRODUCT((A1:C3<>"")/COUNTIF(A1:C3,A1:C3&"")) snip

copy that formula to the range E1:G3. Start changing values in A1:C3.
Duplicate some. What happens?

If you can't figure out what's going on, post back, but the above
technique is how I approach formulae that seem obscure...

Thanks yet again. If that isn't enough (I hope it will be) I may yet be back


You (others too) are an asset to the community.

Harvey