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

[Dennis]

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:

 

[pinmaster]

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

=COUNTIF(Range1, B5)>1

HTH
JG

 

[Dave Peterson]

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

And put your formulas in C1:C5 and D15 (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]

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!