|
Countif fields with **
I have had an inconsistent problem using Countif (Excel 2003) to find
duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry |
Countif fields with **
Because the * is acting as a wildcard.
-- __________________________________ HTH Bob "Jerry" wrote in message ... I have had an inconsistent problem using Countif (Excel 2003) to find duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry |
Countif fields with **
Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(), =search() (but
not =find() for some strange reason???). And maybe more(???). Excel supports wild cards: * = any string of characters ? = any one character and the way you tell excel that you want to use an asterisk is to prefix it with ~ (~*). And since that ~ is a special character, you have to tell excel to use two when you want to use 1. ~ becomes ~~. So this could be an =vlookup() formula that "fixes" all 3 of those special characters: =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) So... In your formula, you could use: =COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*")) But if there's a chance that any of those strings contain *, ? or ~, you'd want to use: =COUNTIF($I$5:$I$13, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*")) (all one cell) Jerry wrote: I have had an inconsistent problem using Countif (Excel 2003) to find duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry -- Dave Peterson |
Countif fields with **
=search() (but not =find() for some strange reason???).
I suspect that reason is because FIND is case sensitive. Although it may be that 2 different programmers wrote the code for those 2 functions. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(), =search() (but not =find() for some strange reason???). And maybe more(???). Excel supports wild cards: * = any string of characters ? = any one character and the way you tell excel that you want to use an asterisk is to prefix it with ~ (~*). And since that ~ is a special character, you have to tell excel to use two when you want to use 1. ~ becomes ~~. So this could be an =vlookup() formula that "fixes" all 3 of those special characters: =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) So... In your formula, you could use: =COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*")) But if there's a chance that any of those strings contain *, ? or ~, you'd want to use: =COUNTIF($I$5:$I$13, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*")) (all one cell) Jerry wrote: I have had an inconsistent problem using Countif (Excel 2003) to find duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry -- Dave Peterson |
Countif fields with **
I'd guess it was the second--or maybe it was written by the same person.
=Find() before lunch and =search() after lunch. I don't see how case sensitivity would make a difference with those wildcards, though. I do understand how a lunch break would make me smarter! "T. Valko" wrote: =search() (but not =find() for some strange reason???). I suspect that reason is because FIND is case sensitive. Although it may be that 2 different programmers wrote the code for those 2 functions. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(), =search() (but not =find() for some strange reason???). And maybe more(???). Excel supports wild cards: * = any string of characters ? = any one character and the way you tell excel that you want to use an asterisk is to prefix it with ~ (~*). And since that ~ is a special character, you have to tell excel to use two when you want to use 1. ~ becomes ~~. So this could be an =vlookup() formula that "fixes" all 3 of those special characters: =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) So... In your formula, you could use: =COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*")) But if there's a chance that any of those strings contain *, ? or ~, you'd want to use: =COUNTIF($I$5:$I$13, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*")) (all one cell) Jerry wrote: I have had an inconsistent problem using Countif (Excel 2003) to find duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry -- Dave Peterson -- Dave Peterson |
Countif fields with **
I don't see how case sensitivity would make a
difference with those wildcards If you start with the premise that FIND is case sensitive for a reason: A1 = abcd FIND("ab?d",A1) FIND("ab*",A1) Would work on abXd or abxd. The wildcards don't take case into consideration which is contrary to the functionality of FIND. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I'd guess it was the second--or maybe it was written by the same person. =Find() before lunch and =search() after lunch. I don't see how case sensitivity would make a difference with those wildcards, though. I do understand how a lunch break would make me smarter! "T. Valko" wrote: =search() (but not =find() for some strange reason???). I suspect that reason is because FIND is case sensitive. Although it may be that 2 different programmers wrote the code for those 2 functions. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(), =search() (but not =find() for some strange reason???). And maybe more(???). Excel supports wild cards: * = any string of characters ? = any one character and the way you tell excel that you want to use an asterisk is to prefix it with ~ (~*). And since that ~ is a special character, you have to tell excel to use two when you want to use 1. ~ becomes ~~. So this could be an =vlookup() formula that "fixes" all 3 of those special characters: =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) So... In your formula, you could use: =COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*")) But if there's a chance that any of those strings contain *, ? or ~, you'd want to use: =COUNTIF($I$5:$I$13, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*")) (all one cell) Jerry wrote: I have had an inconsistent problem using Countif (Excel 2003) to find duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry -- Dave Peterson -- Dave Peterson |
Countif fields with **
It's tough to guess why anyone does anything, huh?
(I still don't buy it <vvbg.) "T. Valko" wrote: I don't see how case sensitivity would make a difference with those wildcards If you start with the premise that FIND is case sensitive for a reason: A1 = abcd FIND("ab?d",A1) FIND("ab*",A1) Would work on abXd or abxd. The wildcards don't take case into consideration which is contrary to the functionality of FIND. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I'd guess it was the second--or maybe it was written by the same person. =Find() before lunch and =search() after lunch. I don't see how case sensitivity would make a difference with those wildcards, though. I do understand how a lunch break would make me smarter! "T. Valko" wrote: =search() (but not =find() for some strange reason???). I suspect that reason is because FIND is case sensitive. Although it may be that 2 different programmers wrote the code for those 2 functions. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(), =search() (but not =find() for some strange reason???). And maybe more(???). Excel supports wild cards: * = any string of characters ? = any one character and the way you tell excel that you want to use an asterisk is to prefix it with ~ (~*). And since that ~ is a special character, you have to tell excel to use two when you want to use 1. ~ becomes ~~. So this could be an =vlookup() formula that "fixes" all 3 of those special characters: =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) So... In your formula, you could use: =COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*")) But if there's a chance that any of those strings contain *, ? or ~, you'd want to use: =COUNTIF($I$5:$I$13, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*")) (all one cell) Jerry wrote: I have had an inconsistent problem using Countif (Excel 2003) to find duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Countif fields with **
That's why I hate programming.
I'm way too anal. I have to account for every possibility! -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... It's tough to guess why anyone does anything, huh? (I still don't buy it <vvbg.) "T. Valko" wrote: I don't see how case sensitivity would make a difference with those wildcards If you start with the premise that FIND is case sensitive for a reason: A1 = abcd FIND("ab?d",A1) FIND("ab*",A1) Would work on abXd or abxd. The wildcards don't take case into consideration which is contrary to the functionality of FIND. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I'd guess it was the second--or maybe it was written by the same person. =Find() before lunch and =search() after lunch. I don't see how case sensitivity would make a difference with those wildcards, though. I do understand how a lunch break would make me smarter! "T. Valko" wrote: =search() (but not =find() for some strange reason???). I suspect that reason is because FIND is case sensitive. Although it may be that 2 different programmers wrote the code for those 2 functions. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(), =search() (but not =find() for some strange reason???). And maybe more(???). Excel supports wild cards: * = any string of characters ? = any one character and the way you tell excel that you want to use an asterisk is to prefix it with ~ (~*). And since that ~ is a special character, you have to tell excel to use two when you want to use 1. ~ becomes ~~. So this could be an =vlookup() formula that "fixes" all 3 of those special characters: =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) So... In your formula, you could use: =COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*")) But if there's a chance that any of those strings contain *, ? or ~, you'd want to use: =COUNTIF($I$5:$I$13, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*")) (all one cell) Jerry wrote: I have had an inconsistent problem using Countif (Excel 2003) to find duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Countif fields with **
And that's why I figure that you would enjoy it.
But after looking at your formulas, I'd guess that anything as straightforward as a "select case" would just bore you completely! "T. Valko" wrote: That's why I hate programming. I'm way too anal. I have to account for every possibility! -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... It's tough to guess why anyone does anything, huh? (I still don't buy it <vvbg.) "T. Valko" wrote: I don't see how case sensitivity would make a difference with those wildcards If you start with the premise that FIND is case sensitive for a reason: A1 = abcd FIND("ab?d",A1) FIND("ab*",A1) Would work on abXd or abxd. The wildcards don't take case into consideration which is contrary to the functionality of FIND. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I'd guess it was the second--or maybe it was written by the same person. =Find() before lunch and =search() after lunch. I don't see how case sensitivity would make a difference with those wildcards, though. I do understand how a lunch break would make me smarter! "T. Valko" wrote: =search() (but not =find() for some strange reason???). I suspect that reason is because FIND is case sensitive. Although it may be that 2 different programmers wrote the code for those 2 functions. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(), =search() (but not =find() for some strange reason???). And maybe more(???). Excel supports wild cards: * = any string of characters ? = any one character and the way you tell excel that you want to use an asterisk is to prefix it with ~ (~*). And since that ~ is a special character, you have to tell excel to use two when you want to use 1. ~ becomes ~~. So this could be an =vlookup() formula that "fixes" all 3 of those special characters: =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) So... In your formula, you could use: =COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*")) But if there's a chance that any of those strings contain *, ? or ~, you'd want to use: =COUNTIF($I$5:$I$13, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*")) (all one cell) Jerry wrote: I have had an inconsistent problem using Countif (Excel 2003) to find duplicates in a column. I do not wish to remove them just count the number of times the item shows in the list. The data column looks like this. ID # Countif (Duplicate) BW-CA-46**94-2227626 1 BW-CA-4614-*94-2227626 1 BW-CA-4**94-0358460 3 BW-CA-4213**94-1009696 1 BW-CA-4258**94-1009696 1 BW-CA-4505**94-0358460 1 BW-CA-4808--94-0358460 1 The Countif uses this =COUNTIF($I$5:$I$13,I5) The first item also counts the second and the third counts the last two. To confuse me the 4th and fith do not count each other. When I replace the ** with -- I get the correct result. Any Idea why the * or ** cause this problem and does it occur in other functions. -- Jerry -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 11:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com