return the FIRST 'color' in a product description string that exists in a list of colors

Greetings all,

I am posting this in both VBA and Formulas forums even though my money is on VBA as the only way this could even get close to being solved.

Background:

I have a list of products (over 50,000) in column A describing the colors of said product on sheet "Data".
Examples:

Cell A1: "Brown Turquoise"
Cell A2: "Shiny Black Yellow Pink"
Cell A3: "Dark Green Orange Brown"
Cell A4: "Violet Dark Green"
...etc...

In column A on sheet "Colors" I have a listing of colors.
Example:


Pink
Black
Turquoise
Brown
Yellow
Green
Orange


What I am needing to do is search each string in each cell on the "Data" tab and find the FIRST color in that string that is also in the list of 'acceptable' colors on the COLORS tab.

I have found several solutions that find and display the FIRST matching color it finds in the list of colors on the "Colors" tab, but I need it to find the FIRST matching color within the text sting on the "Data" tab.

Results I would like to get:

Cell B1: "Brown" - since brown is the first 'acceptable' color in "BROWN Turquoise"
Cell B2: "Black" - since "Shiny" is not a color and Black is the first 'acceptable' color in "Shiny BLACK Yellow Pink"
Cell B3: "Green" - since "Dark" is not a color and Green is the first color in "Dark GREEN Orange Brown"
Cell B4: "Green" - since both "Violet" and "Dark" are NOT in my color list and green is the first acceptable color in "Violet Dark GREEN"


The results that I am getting with other solutions I have found so far are as follow:

Cell B1: "Turquoise" WRONG, but it is because Turquoise is before Green in my list of colors
Cell B2: "Pink" WRONG, but it is because PINK is before "Black" and "Yellow" on my list of colors
Cell B3: "Brown" WRONG, but it is because BROWN is before "Green" and "Orange" on my list of colors
Cell B4: "Green" RIGHT! but only because "Violet" is not in my list and neither is "Dark"..

So if I am LUCKY enough that the first color in the list of colors is also the first color in the description string, I get the correct answer, but you can see from my example that is only 25% success rate. yikes!

So instead of being this logic which does NOT work:

Does the first color in the list "Pink" exist in the description string in Data!A1?
Does the second color in the list "Black" exist in the description string in Data!A1?
Does the third color in the list "Turquoise" exist in the description string Data!A1?
.. I found I match "Turquoise"... (WRONG).. enter that in Data!B1 and move to Data!A2
....etc...etc..


I think what it needs to do is:

"Brown" is the first 'word' in the description string in Data!A1 and does this exist in the list of colors?
... if TRUE, then put "Brown" in Data!B1 and move to Data!A2
... if FALSE, then look at the second 'word' in Data!A1
"Turquoise is the second word in the description string in Data!A1 and does this exist in the list of colors?
...etc..etc...

That way when the FIRST 'word' in the string in Data!A(n) is a HIT, it puts that value in Data!B(n) and moves on to the next description string.

Again I have found several solutions both with VBA and Array functions that are AMAZING in what they do (I could never write formulas or code like that) but NONE of them I have found to date give me the FIRST matching color from the DESCRIPTION STRING, they give me the first matching color from the LIST OF COLORS.


NOTE: I did find another post from THIS forum (which I don't think is a violation of the rules to put links to) which is sounding like it could be the way to go, but I do not know VBA enough to:

"Loop through the string, one character at a time. Each time you come to a space, that signifies the end of a word. "Build" your words in this manner and store that word you just found in a one dimensional table. After you've stored all of the words in your table, you simply loop through the table, searching it"

Again that sounds GREAT! and makes 'sense' to me but ain't no way mateo561 is capable of writing such code! LOL


http://www.excelforum.com/excel-prog...in-a-cell.html

I hope that is clear enough and if not, please ask for clarification and I assume I can upload example files to this forumn if that would help as well.

I would like to thank everyone in advance who takes a crack at this.

Regards
mateo561