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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > 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
|
Similar Threads
