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

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

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((Range<>"")/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.