Cond Format & helper-cell based "duplicate rec" tricked by content

Using 2003

Goal was to use Conditional Format and/or a helper-column cell to isolate
duplicated records in a range.

The formulas used were:
Conditional Format =IF(COUNTIF(Range1, B5)>1,TRUE,FALSE)
(Cell turns Yellow)
Contigious cell =IF(COUNTIF(Range1,B2)>1,"Duplicate","")


All of below cells do NOT have a duplicate thru 7 characters!
But XL senses duplicates via both above formulas!
M*D9000
M*D5000
M*D0004
M*D0035
M*D0002

Is there a "Bug" in XL that may see the second letter "*" as a wildcard OR
stops the compare at "M*" ? therefore evaluating all five as identical only
to the first two characters?

TIA Dennis
In both cases the formulas identified the following as duplicates:

Hi, as far as I know, you can not use an IF function in CF.
Try:

=COUNTIF(Range1, B5)>1

HTH
JG

I put those 5 values in B1:B5. I named that range Range1.

And put your formulas in C1:C5 and D1:D5 (with addressing changes) and each
evaluated the as False or "".

Are you sure Range1 is what you expect--maybe it's larger than you wanted???

I did change the value in b2 to M* (just two characters) and did get
True/Duplicate, though.

If you want to be really careful, you can "convert" the wild cards in your
formula:

=IF(COUNTIF(range1,
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"~","~~"),"?","~?"),"*","~*"))>1,
"Duplicate","")

All one cell.



Dennis wrote:
>
> Using 2003
>
> Goal was to use Conditional Format and/or a helper-column cell to isolate
> duplicated records in a range.
>
> The formulas used were:
> Conditional Format =IF(COUNTIF(Range1, B5)>1,TRUE,FALSE)
> (Cell turns Yellow)
> Contigious cell =IF(COUNTIF(Range1,B2)>1,"Duplicate","")
>
> All of below cells do NOT have a duplicate thru 7 characters!
> But XL senses duplicates via both above formulas!
> M*D9000
> M*D5000
> M*D0004
> M*D0035
> M*D0002
>
> Is there a "Bug" in XL that may see the second letter "*" as a wildcard OR
> stops the compare at "M*" ? therefore evaluating all five as identical only
> to the first two characters?
>
> TIA Dennis
> In both cases the formulas identified the following as duplicates:

--

Dave Peterson

Thanks Dave

My Range is OK.

There is something unexpected occuring in the operation of the formula
related to its evaluation of the data in the cell or "M*".

I really appreciate your time and thoughts!




"Dave Peterson" wrote:

> I put those 5 values in B1:B5. I named that range Range1.
>
> And put your formulas in C1:C5 and D1:D5 (with addressing changes) and each
> evaluated the as False or "".
>
> Are you sure Range1 is what you expect--maybe it's larger than you wanted???
>
> I did change the value in b2 to M* (just two characters) and did get
> True/Duplicate, though.
>
> If you want to be really careful, you can "convert" the wild cards in your
> formula:
>
> =IF(COUNTIF(range1,
> SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"~","~~"),"?","~?"),"*","~*"))>1,
> "Duplicate","")
>
> All one cell.
>
>
>
> Dennis wrote:
> >
> > Using 2003
> >
> > Goal was to use Conditional Format and/or a helper-column cell to isolate
> > duplicated records in a range.
> >
> > The formulas used were:
> > Conditional Format =IF(COUNTIF(Range1, B5)>1,TRUE,FALSE)
> > (Cell turns Yellow)
> > Contigious cell =IF(COUNTIF(Range1,B2)>1,"Duplicate","")
> >
> > All of below cells do NOT have a duplicate thru 7 characters!
> > But XL senses duplicates via both above formulas!
> > M*D9000
> > M*D5000
> > M*D0004
> > M*D0035
> > M*D0002
> >
> > Is there a "Bug" in XL that may see the second letter "*" as a wildcard OR
> > stops the compare at "M*" ? therefore evaluating all five as identical only
> > to the first two characters?
> >
> > TIA Dennis
> > In both cases the formulas identified the following as duplicates:
>
> --
>
> Dave Peterson
>