Finding Duplicates

[Guest]

I'm trying to check for duplicates excluding blank cells across a range A3:I3

I've looked at Chip Pearson's site and tried adapting the formula there

=IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A500<>"")*ROW(A2:A500)))),INDIRECT("A2:A"&(MAX((A2:A500<>"")*ROW(A2:A500))))))>1,"Duplicates","No Duplicates")

to
=IF(MAX(COUNTIF(INDIRECT("A3:I"&(MAX((A3:I3<>"")*COLUMNS(A3:I3)))),INDIRECT("A3:I"&(MAX((A3:I3<>"")*COLUMNS(A3:I3))))))>1,"Duplicates","No
Duplicates") as an array

but it gives the answer Duplicates if any cell has a value, if they are all
blank the answer is #Ref!

I'm stuck now I'm afraid

I'd appreciate any help. Thanks.

 

[Aladin Akyurek]

For a non-array approach see:

http://tinyurl.com/5gnfq

 

[Guest]

Thanks.

I just want to check a range of cells and if there are duplicates, excluding
blank cells, return "Duplicates". I don't want to eliminate them, just know
there is one or more there.

 

[Aladin Akyurek]

Do you mean a formula for diagnosis...

=(COUNT(Range)+COUNTIF(Range,"?*")=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&"")))+0

Custom format the formula cell as:

[=1]"No Duplicates";[=0]"Duplicates"

 

[Guest]

Thanks a lot Aladin. That's sorted it.