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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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!
 
Back
Top