I just started a new job and my company already had a file with about
16,000 records in it (called Products). This file contains all of the
parts numbers that they carry. In this file there is one column called
"description". This cell contains the dimensions, color name and some
other information such as if the piece is flat or rounded. I have
another file that has about 400 records and this file tells me "color
name" as well as the "type" of stone that it is (called Stone_Type).

What I am trying to do is add a field to the Products file that will
say stone type. Is there a way to have Excel search the "description"
from Products for "color name" from Stone_Type and if it finds it
return "type"? As previously mentioned "description" contains things
other than just the color name. To further complicate things there is
no consistency as to where in the cell the color name is (meaning I
cannot extract just that data). Most of the color names are more than
one work long.

If the "description" field matched "color name" I could just do a
vlookup and return "type" but with there being other data in the field
I don't know what to do. I thought about putting each work in its own
column and doing a whole bunch of vlookups but the problem with that is
that most of the names are more than one word long. What I am thinking
about now is using the find function to look for the "color name" in
"description" and if it finds it then return the cell reference so that
I can run a vlookup off of that. I think this will work but I don't
know how to make it search all of the "color name’s (A2:A400 in
Stone_Type).

Does anyone know how I could do this or know of a better way to go
about it? As stated above I have 16,000 record I have to do this to so
I am sure this will take a very long time to run but once it is done the
first time that is it and I can hard code the values so I do not really
care how long it takes.

Thank you. I am not sure if I did a decent job explaining this if
there are any questions please ask and I will try to respond a.s.a.p.


--
hatter
------------------------------------------------------------------------
hatter's Profile: http://www.excelforum.com/member.php...o&userid=26762
View this thread: http://www.excelforum.com/showthread...hreadid=400154