Countif fields with **

[Jerry]

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.

 

[Bob Phillips]

Because the * is acting as a wildcard.

 

[Dave Peterson]

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)

 

[T. Valko]

=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.

 

[Dave Peterson]

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!

=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.

 

[T. Valko]

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

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!

 

[Dave Peterson]

It's tough to guess why anyone does anything, huh?

(I still don't buy it <vvbg>.)

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.

 

[T. Valko]

That's why I hate programming.

I'm way too anal. I have to account for every possibility!

 

[Dave Peterson]

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!

That's why I hate programming.

I'm way too anal. I have to account for every possibility!