Limit to nested IF(ISNUMBER(SEARCH)) functions?

thanks to all who haved responded so quickly... i used "bill, rob, joe" etc
for simplification however the cellc i'm searching on contain more complex
entries. so perhaps something a bit more complex would help.

col A desired return
bill jones bill
bill johnson bill
robert smith rob
rob allen rob
jon jones jon
jonathan smith jon
jonny carson jon


etc... there are probably 10 different, but unique, strings i'd be searching
for within a very long list (over 2,000 items) that have over 75 different
variations of those 10 unique strings

thanks again,

Jonathan

On Tue, 19 Jun 2007 15:28:18 -0700, Jonathan Horvath
wrote:

thanks to all who haved responded so quickly... i used "bill, rob, joe" etc
for simplification however the cellc i'm searching on contain more complex
entries. so perhaps something a bit more complex would help.

col A desired return
bill jones bill
bill johnson bill
robert smith rob
rob allen rob
jon jones jon
jonathan smith jon
jonny carson jon


etc... there are probably 10 different, but unique, strings i'd be searching
for within a very long list (over 2,000 items) that have over 75 different
variations of those 10 unique strings

thanks again,

Jonathan

Are all the strings you'll be searching consist of names as above?

Will all the 10 different unique strings for which you'll be searching to be
found in the beginning letters of the searched string as above?


--ron

On Tue, 19 Jun 2007 15:28:18 -0700, Jonathan Horvath
wrote:

thanks to all who haved responded so quickly... i used "bill, rob, joe" etc
for simplification however the cellc i'm searching on contain more complex
entries. so perhaps something a bit more complex would help.

col A desired return
bill jones bill
bill johnson bill
robert smith rob
rob allen rob
jon jones jon
jonathan smith jon
jonny carson jon


etc... there are probably 10 different, but unique, strings i'd be searching
for within a very long list (over 2,000 items) that have over 75 different
variations of those 10 unique strings

thanks again,

Jonathan

If the unique strings can be found anywhere in the searched strings, you could
use this **array** formula.

First, set up a range where you list your 10 different unique strings. I
NAME'd it "Uniques".

Then, assuming your strings to search start in A2, enter the formula below as
an array formula. That means to hold down ctrlshift while you hit enter.
Excel will place braces {...} around the formula

=IF(OR(ISNUMBER(FIND(Uniques,A2))),
INDEX(Uniques,MATCH(TRUE,(ISNUMBER(
FIND(Uniques,A2))),0)),"")

Then fill down the 2000 entries.

If the unique strings are required to be at the beginning of the search string,
I'd probably use a UDF.
--ron

Hi Ron,

we're getting really close. the unique string can be anywhere in the
searched strings. however, the desired return may not contain something in
the unique string.

Col A desired return
william jones bill
bill smith bill
will robins bill
jon smith jon
d. robert burns rob
rob miller rob

so i need a translator in there as well, i could set up the "uniques" as you
suggest, but i'd need a second so that i would have

Col A uniques_ID uniques_return
william jones william bill
bill smith bill bill
will robins will bill
jon smith jon jon
d. robert burns robert rob
rob miller rob rob


so it would search Col A for the Uniques_ID string and if it finds it it
would then return the Uniques_return...

is that a possible variation on your suggestion?

Thanks!

Jonathan

"Ron Rosenfeld" wrote:

On Tue, 19 Jun 2007 15:28:18 -0700, Jonathan Horvath
wrote:

thanks to all who haved responded so quickly... i used "bill, rob, joe" etc
for simplification however the cellc i'm searching on contain more complex
entries. so perhaps something a bit more complex would help.

col A desired return
bill jones bill
bill johnson bill
robert smith rob
rob allen rob
jon jones jon
jonathan smith jon
jonny carson jon


etc... there are probably 10 different, but unique, strings i'd be searching
for within a very long list (over 2,000 items) that have over 75 different
variations of those 10 unique strings

thanks again,

Jonathan

If the unique strings can be found anywhere in the searched strings, you could
use this **array** formula.

First, set up a range where you list your 10 different unique strings. I
NAME'd it "Uniques".

Then, assuming your strings to search start in A2, enter the formula below as
an array formula. That means to hold down ctrlshift while you hit enter.
Excel will place braces {...} around the formula

=IF(OR(ISNUMBER(FIND(Uniques,A2))),
INDEX(Uniques,MATCH(TRUE,(ISNUMBER(
FIND(Uniques,A2))),0)),"")

Then fill down the 2000 entries.

If the unique strings are required to be at the beginning of the search string,
I'd probably use a UDF.
--ron

On Wed, 20 Jun 2007 13:04:08 -0700, Jonathan Horvath
wrote:

Hi Ron,

we're getting really close. the unique string can be anywhere in the
searched strings. however, the desired return may not contain something in
the unique string.

Col A desired return
william jones bill
bill smith bill
will robins bill
jon smith jon
d. robert burns rob
rob miller rob

so i need a translator in there as well, i could set up the "uniques" as you
suggest, but i'd need a second so that i would have

Col A uniques_ID uniques_return
william jones william bill
bill smith bill bill
will robins will bill
jon smith jon jon
d. robert burns robert rob
rob miller rob rob


so it would search Col A for the Uniques_ID string and if it finds it it
would then return the Uniques_return...

is that a possible variation on your suggestion?

Thanks!

That is pretty simple to do. You have your two columns of Uniques_ID and
Uniques_Return. You just need to return the value in the second column.

=IF(OR(ISNUMBER(FIND(Uniques_ID,A2))),
INDEX(uniques_return,MATCH(TRUE,(ISNUMBER(
FIND(Uniques_ID,A2))),0)),"")

(Array-entered as before).

You realize that since you are accepting finding the unique string anywhere in
the searched string, the following will occur, with "jon" as one of the
uniques:

michael jones -- jon





--ron

Yeah, that's the difficulty...

here are some different examples:

Name unique-ID unique-return
umts_r5.667.1 umts umts
gsm_r5.234.1.a gsm gsm
gsm_ra8.234.1 ra ajar
gsm_6.234.ezx ezx ezx

when i use the equation you provided, it returns gsm for anything that
begins with gsm... so the last 3 return gsm and not the appropriate return.

thanks!

Jonathan

On Sat, 23 Jun 2007 05:20:00 -0700, Jonathan Horvath
wrote:

Yeah, that's the difficulty...

here are some different examples:

Name unique-ID unique-return
umts_r5.667.1 umts umts
gsm_r5.234.1.a gsm gsm
gsm_ra8.234.1 ra ajar
gsm_6.234.ezx ezx ezx

when i use the equation you provided, it returns gsm for anything that
begins with gsm... so the last 3 return gsm and not the appropriate return.

thanks!

Jonathan

Your examples are even more complicated to deal with, in that it appears that
there can be multiple unique_ID's in the string to be searched; and the
required unique_ID may be located anywhere within the string!

You will need some rules to determine which unique_ID you want returned when
multiple possible unique_ID's are present within the string.

The rules might include, for example, prioritizing the unique_ID's. They may
depend, in part, on position; on whether the unique_ID is embedded within other
numbers/letters or has some clear sort of separations (e.g. ".", "_", or
start/end of string); and so forth.

Once you develop these rules, they should be able to be implemented in Excel
(or more likely, in VBA).
--ron

Hi Ron,

Thanks, you're correct in that this is rather complicated. My thought was
to search for "ra" first and have that return "ajar" then "ezx" to return
"ezx", then "umts" etc for "umts" and so on... which is why i started with
the IF(isnumber(search)), it was working, but unfortunately I can't have
enough nests.

hmmmmm....

Jonathan

On Sun, 24 Jun 2007 04:49:00 -0700, Jonathan Horvath
wrote:

Hi Ron,

Thanks, you're correct in that this is rather complicated. My thought was
to search for "ra" first and have that return "ajar" then "ezx" to return
"ezx", then "umts" etc for "umts" and so on... which is why i started with
the IF(isnumber(search)), it was working, but unfortunately I can't have
enough nests.

hmmmmm....

Jonathan

If your rules are that there is a priority order of unique-ID's, then all you
need to do is list them in that order in your lookup tables.

e.g.

Tables:

unique-ID unique-return
ra ajar
ezx ezx
umts umts
gsm gsm


Formula:

=IF(OR(ISNUMBER(FIND(unique_ID,A2))),
INDEX(unique_return,MATCH(TRUE,(ISNUMBER(
FIND(unique_ID,A2))),0)),"")

**ARRAY-ENTERED**
--ron

I have to wait until I get in the office tomorrow to double check with the
full list but I think this will work!

Thanks!!!

Jonathan

"Ron Rosenfeld" wrote:

On Sun, 24 Jun 2007 04:49:00 -0700, Jonathan Horvath
wrote:

Hi Ron,

Thanks, you're correct in that this is rather complicated. My thought was
to search for "ra" first and have that return "ajar" then "ezx" to return
"ezx", then "umts" etc for "umts" and so on... which is why i started with
the IF(isnumber(search)), it was working, but unfortunately I can't have
enough nests.

hmmmmm....

Jonathan

If your rules are that there is a priority order of unique-ID's, then all you
need to do is list them in that order in your lookup tables.

e.g.

Tables:

unique-ID unique-return
ra ajar
ezx ezx
umts umts
gsm gsm


Formula:

=IF(OR(ISNUMBER(FIND(unique_ID,A2))),
INDEX(unique_return,MATCH(TRUE,(ISNUMBER(
FIND(unique_ID,A2))),0)),"")

**ARRAY-ENTERED**
--ron