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

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

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

=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

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

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

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

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

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