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 we
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:

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

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

=COUNTIF(Range1, B5)1

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494092

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

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 we
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

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

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 we
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