software

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)gt;1,TRUE,FALSE)
(Cell turns Yellow)
Contigious cell =IF(COUNTIF(Range1,B2)gt;1,quot;Duplicatequot;,quot;quot;)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 quot;Bugquot; in XL that may see the second letter quot;*quot; as a wildcard OR
stops the compare at quot;M*quot; ? 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)gt;1

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=494092I 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 quot;quot;.

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 quot;convertquot; the wild cards in your
formula:

=IF(COUNTIF(range1,
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,quot;~quot;,quot;~~quot;),quot;?quot;, quot;~?quot;),quot;*quot;,quot;~*quot;))gt;1,
quot;Duplicatequot;,quot;quot;)

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

I really appreciate your time and thoughts!

quot;Dave Petersonquot; wrote:

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