Check no cell is the same within a range

[Neil Pearce]

Dear all,

The range A1:A500 contains references.

What formula would I require to return a true of false answer to the
question: are all cells different?

i.e. are all references independent.


Thanking-you in advance. Kind regards,

Neil

 

[JLatham]

In any available empty column, put this formula in a cell on row 1 and fill
it down to row 500:
=IF(COUNTIF($A$1:$A$500,A1)=1,"","MULTIPLE ENTRIES")
Rather than nag you with a bunch of "TRUE" entries when the item on a row
only appears once, this leaves it blank, making the MULTIPLE ENTRIES noted
stand out better. If you want a real TRUE/FALSE output, then use
=COUNTIF($A$1:$A$10,A1)=1

Either case, when you have an entry that is repeated, each instance of it
will be marked as either "MULTIPLE ENTRIES" (first formula) or FALSE (2nd
formula).

Hope this helps.

 

[Chip Pearson]

The following array formula will return TRUE if all items in A1:A10
are distinct (no duplicates, ignoring empty cells) or FALSE if there
are duplicate values in A1:A10.

=MAX(COUNTIF(A1:A10,A1:A10))<=1

This is an array formula, so you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the cell and whenever you edit it
later. If you do this properly, Excel will display the formula
enclosed in curly braces { }. See
http://www.cpearson.com/Excel/ArrayFormulas.aspx for more information
about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary''s Student]

An alternative to JLatham is to use the single cell array formula:

=IF(MAX(COUNTIF(A1:A500,A1:A500))>1,"not all are different","all are
different")

The formula must be entered with CNTRL-SHFT-ENTER rather than just the ENTER
key.