Finding Duplicates
 

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<"")*C OLUMNS(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.

For a non-array approach see:

http://tinyurl.com/5gnfq

nospaminlich wrote:
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<"")*C OLUMNS(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.

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.

Do you mean a formula for diagnosis...

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

Custom format the formula cell as:

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


nospaminlich wrote:
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.

Thanks a lot Aladin. That's sorted it.