|As I said in my first response, Excel is going to need a complete list of keywords and the associated categories. Once that table exists, VBA code be used to assign a category by searching each description for a keyword and when one is found, the category could be assigned. However, after I offer a conceptual view of a solution, I'm going to point out a possible problem with this method.|
We'll start with a table of Categories and Keywords like this:
C D E
1 Aquatics Baseball Football
2 Swim T-Ball Tackle
3 Float Softball Block
Now let's assume your Descriptions are in Column A and you want the Category in Column B. (I'm using these columns for my example. If this suggestion works for you, we will need a more detailed explanation of your worksheet layout.)
So, based on the above assumptions, code could be written to loop through the descriptions in Column A looking for a keyword. The code would basically start looping through the keywords and checking the description to see if the keyword can be found. e.g. Does the description contain "swim"? No. Does the description contain "float" No. Does the description contain "T-Ball". Yes!
When the first hit occurs, the code would know in which Column the keyword was located and pull the category name from Row 1 of that Column. e.g. "T-Ball" is located in Column D, so put the value from D1 "Baseball" in Column B.
The code might look something like this for 3 Descriptions in A1:A3 and the table of keywords from above:
'Loop through Decriptions in Column A
For Each dScript In Range("A1:A3")
'Loop through seach set of keywords
For catCol = 3 To 5
For catRw = 2 To 3
'Reset "Keyword Found" to stop searching
fndFlag = 0
'If keyword is found, assign Category from Row 1, set "Keyword Found" flag
If UCase(dScript) Like UCase("*" & Cells(catRw, catCol)) & "*" Then
Cells(dScript.Row, 2) = Cells(1, catCol)
fndFlag = 1
'Countinue looping through Keywords unless "Keyword Found" is set
If fndFlag = 1 Then Exit For
If fndFlag = 1 Then Exit For
'Continue looping through Column A
OK, so here's the possible drawback of this suggestion: Your table of keywords needs to be built very carefully, with no ambiguity related to which category a keyword belongs in. For example, let's say you have categories of Aquatics and Painting. Let's say one of the keywords for Aquatics is "water". The description in A2 (below) will get categorized as Aquatics because it contains "water" in the description. This assumes that the Aquatics category is listed before Painting in your table. Remember, the code assigns the category after the first hit on a keyword.
1 The focus is to move comfortably through the water...
2 Various mediums, such as oil, water color and ink will be...
Granted, that isn't a great example, but I hope you see my point. If a description contains a keyword, it is going to be assigned to the first category in which that keyword was found while looping through the table.
If think you can build a unambiguous table of keywords and categories, then the code I offered above should work after it is modified to match your layout.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.