Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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: |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
How do I copy a cell (content AND format) from one worksheet to a. | Excel Worksheet Functions | |||
How do I change content of a cell based on date | Excel Worksheet Functions | |||
Put an autoshape in a cell based on another cells content | Excel Worksheet Functions |