Can anyone help me do the following...

I have a long list of names in column A and corresponding numeric
values in B of sheet 1.

These values relate to different categories and i want to split the
original list in sheet 1 into 5 sheets depending on the category.

Imagine the list consists of the following...

Dog 100
Doggy 50
Doggie 150
Kitten 75
Kitty 100
Kittens 120
Bird 90
Birdie 110
Birdy 25


I want to split the list into three category "Dog", "Kit" and "Bird".

Is it possible to put a formula in sheet 2 to search the original list
in sheet 1 and pull all of the entries containing the word dog (in this
case 'dog, doggy, doggie' and any other variations featuring 'dog') and
the respective values from sheet 1 into the second sheet.

Many thanks for your help


---
Message posted from http://www.ExcelForum.com/

Hi All:

Lookup tables are far easier to use. In fact, you will find that in order to solve your problem you will need nested ifs. Nesting ifs gets ugly quick. Therefore, I recommend using the vlookup table.

I hope that helps,

Jim

I've only ever used vlookups to search for exact matches - is i
possible to search for entries that are partial matches or contain som
of the words?

Eg, if i vlookup dog all i get back is the appropriate value for a
exact match of dog... however i need the lookup to pick up any matche
containing dog eg dog, doggy, doggie, dog123 etc

Anyone know if this is possible? Cheer

--
Message posted from http://www.ExcelForum.com

something like:

=vlookup("*dog*",sheet2!$a:$b,2,false)
or
=vlookup("dog*",sheet2!$a:$b,2,false)
(if it has to start with dog.)



"ian123 <" wrote:

I've only ever used vlookups to search for exact matches - is it
possible to search for entries that are partial matches or contain some
of the words?

Eg, if i vlookup dog all i get back is the appropriate value for an
exact match of dog... however i need the lookup to pick up any matches
containing dog eg dog, doggy, doggie, dog123 etc

Anyone know if this is possible? Cheers

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

each cell that holds that vlookup formula will return 100 (from the sample
data). Don't think vlookup is a viable approach to return multiple matches.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
something like:

=vlookup("*dog*",sheet2!$a:$b,2,false)
or
=vlookup("dog*",sheet2!$a:$b,2,false)
(if it has to start with dog.)



"ian123 <" wrote:

I've only ever used vlookups to search for exact matches - is it
possible to search for entries that are partial matches or contain some
of the words?

Eg, if i vlookup dog all i get back is the appropriate value for an
exact match of dog... however i need the lookup to pick up any matches
containing dog eg dog, doggy, doggie, dog123 etc

Anyone know if this is possible? Cheers

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

I didn't go back to the original post. My suggestion will pick up the first
match (and only the first match).

Tom Ogilvy wrote:

each cell that holds that vlookup formula will return 100 (from the sample
data). Don't think vlookup is a viable approach to return multiple matches.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
something like:

=vlookup("*dog*",sheet2!$a:$b,2,false)
or
=vlookup("dog*",sheet2!$a:$b,2,false)
(if it has to start with dog.)



"ian123 <" wrote:

I've only ever used vlookups to search for exact matches - is it
possible to search for entries that are partial matches or contain some
of the words?

Eg, if i vlookup dog all i get back is the appropriate value for an
exact match of dog... however i need the lookup to pick up any matches
containing dog eg dog, doggy, doggie, dog123 etc

Anyone know if this is possible? Cheers

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson


--

Dave Peterson

Tom corrected this response.

If you're tring to add the values returned by the =vlookup()'s, you could use
the userdefined function in this thread:

http://groups.google.com/groups?thre...EADA%40msn.com

And then wrap it in =sum()

=sum(mvlookup("*dog*",sheet2!$a:$b,2,false))

If you're returning text or want to see the individual values, you can select as
many cells (in a row or in a column) that you expect to return and then hit
ctrl-shift-enter (instead of just enter).



Dave Peterson wrote:

something like:

=vlookup("*dog*",sheet2!$a:$b,2,false)
or
=vlookup("dog*",sheet2!$a:$b,2,false)
(if it has to start with dog.)

"ian123 <" wrote:

I've only ever used vlookups to search for exact matches - is it
possible to search for entries that are partial matches or contain some
of the words?

Eg, if i vlookup dog all i get back is the appropriate value for an
exact match of dog... however i need the lookup to pick up any matches
containing dog eg dog, doggy, doggie, dog123 etc

Anyone know if this is possible? Cheers

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson


--

Dave Peterson

You could use an Advanced Filter to extract the lists. There are
instructions he

http://www.contextures.com/xladvfilter01.html#ExtractWs

In the criteria area use a heading that matches the heading in the table
(e.g. Animal), and in the cell below, enter the animal name --

Animal
Kit

Follow the instructions to 'Extract Data to Another Worksheet'

ian123 < wrote:
Can anyone help me do the following...

I have a long list of names in column A and corresponding numeric
values in B of sheet 1.

These values relate to different categories and i want to split the
original list in sheet 1 into 5 sheets depending on the category.

Imagine the list consists of the following...

Dog 100
Doggy 50
Doggie 150
Kitten 75
Kitty 100
Kittens 120
Bird 90
Birdie 110
Birdy 25


I want to split the list into three category "Dog", "Kit" and "Bird".

Is it possible to put a formula in sheet 2 to search the original list
in sheet 1 and pull all of the entries containing the word dog (in this
case 'dog, doggy, doggie' and any other variations featuring 'dog') and
the respective values from sheet 1 into the second sheet.

Many thanks for your help


---
Message posted from http://www.ExcelForum.com/



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html