Search Cells for key words and add category code

Hi,

I have a situation where I would like to have a macro that looks at a list of descriptions in Column A, determines if any key words from a master list are in the cell, and then put the corresponding category code in the B column right next to it.

In my example, I have the descriptions in Column A, the "yet-to-be-filled" category column B, and then a key showing which key words to look for. The macro would need to search Column A for the predetermined key words, and then put the corresponding category code in the same row, Column B.

Thanks in advance for any help! I was banging my head against the wall (not literally!) trying to figure this out!

-Jim

Hi
try this formula:

Please Login or Register  to view this content.

note the it will pick the last (highest") value, so a cell containing "red green white" will return only "white"

Hi Jim

Try this. I put a table in cells D2:E4
RED 1
GREEN 2
WHITE 3

(No need to do the All Else). The macro refers to this table. It looks for the colours, then puts the number in the next cell in to column B.

Please Login or Register  to view this content.

Hope this helps.

Cheers, Rob.

Oooh. Nicky's soltution is far neater!

thanks for the rep rcsmith
it's actually a slightly modified version of a formula I saw on this forum some time ago and copied into my "neat fomulas" workbook
So really the kudos belongs elsewhere, I'm afraid

Wow, both solutions work perfectly and I learned a few things from each. Thanks to both NickyC and RSCSmith for both perspectives! I'll also be adding NickyC's shared formula to my list of cool tricks!