Solved Search a field and assign a label based on results

March 15, 2013 at 18:36:05
Specs: Windows 7
Hi Everyone,
I have an Excel sheet with records arranged in rows. The records represent activities, baseball, football, dance, swimming, etc.

One of the columns (fields) contains an "activity description", another an activity name. I need to assign a category based on that information. The categories are things like Cultural Arts, Educational, Aquatics, Baseball, Football, Soccer, etc.

Is there an Excel macro or some other tool or function that will search one column (or 2) then assign a value to another column (the category)?

Does anyone have some other way to accomplish this? I need to end up with a .CSV file?

Thanks in advance for your help !

PS: Solution can be Windows based (Excel) but I also have a box with Ubuntu on it.

See More: Search a field and assign a label based on results

Report •

March 15, 2013 at 19:35:51
✔ Best Answer
A solution can probably be offered, but I'm not sure exactly sure what you are trying to accomplish. In order to have an Excel function or macro assign a category to each activity, it has to pull the information from some place. It has to know what category to assign, so that has to be entered someplace first.

How would a function or macro save any time if you have to first set up a table that matches every activity to a category?

Perhaps some more example data would help. Before posting example data, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 15, 2013 at 19:51:01
Sorry, maybe I didn't state that clearly. That's exactly what I'm trying to do. Find keywords in a description, then assign it to a category based on results. So if the description was
"Level 1 of the Learn-to-Swim Program is for children with little or no swimming experience. The focus of this is to move comfortably through water, fully submerge face in water and execute supported front and back kicking and floating."

It would be assigned to a category "Aquatics" based on finding the keyword "swim". The category would be created in an additional column.

The search term could look for portions of a string as well as whole words so "swimming" is seen as "swim". Other keywords for this category could be aquatics, float, etc.

A category like "Baseball" might be based on terms like "softball", "fast-pitch", T-Ball", "hardball", etc. They would all be assigned "Baseball" as the category.

The search terms could come from a list while the categories to be assigned could come from another list.

Activity Name	Description	Activity ID
Aquatics-grunion 2	The focus is to move comfortably through the water, execute supported front/back glides and floats, and swim using combined arm and leg actions with support.	1172.155

Thanks for your help!

Report •

March 15, 2013 at 22:07:08
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:

Sub AssignCategory()
'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
     End If
'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
End Sub

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.

Report •

Related Solutions

March 15, 2013 at 23:29:38
This is great! I'm not sure how this forum works but am willing to hire you to make this work if we can come to agreement.

Actually your example of water vs. water color is right on the money and one I had already thought of. Fortunately, most all aquatic events will either contain "swim", "swimming", or "Aquatics". I'm sure there may be others though so thanks for the heads up

Here is a link to a public folder containing data. I don't yet have the exact list of categories, but I can put that in the folder when it's ready, maybe Sunday mid-day.

The existing data also contains a "Legacy Category" which will be helpful. The goal is to boil down many categories(well over 100) used by various agencies to about 30 or so, and match up the new categories with the individual events.

The new "Category" column can go the far right for example.

How best to contact you if you're interested?

Thanks very much!


Report •

March 16, 2013 at 06:41:51
We don't do this for hire, we are all volunteers. However, that doesn't mean that we want to spend huge amounts of time on a project that may indeed need "professional help". We do this for fun and when a offering a solution begins to feel like a job, the fun ends.

There is also the issue of maintenance. You shouldn't be dependent on free help from an on-line forum for the maintenance of software that you require for your business.

That said, the code I've written will do what you want provided that a table of categories and keywords is provided. It simply needs to be modified to search the correct ranges.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 16, 2013 at 07:32:47
Understood. I think it's great that your all willing to spend some time helping people like this, that's what we're about as well. In this case we're trying to make order out of what is a chaotic way some of these groups do business.

This was not part of our original project but came up at the last minute and I don't have anyone available to write me code. It's just a temporary solution so no maintenance will be required. I just need it to work for the initial data collection and cleaning phase.

This is great, maybe I can find someone who knows this stuff better than I to finish it up.

Thanks you for your help and all your doing for the community!

BTY- if anyone can help further.... (email address deleted)

edited by moderator: email address deleted

Report •

March 16, 2013 at 08:14:38
I'm not saying I won't try to categorize the data once you complete the keyword list. Right now I just don't have enough info to work with.

I did think of a workaround for the "Aquatics/water-Painting/water" issue, but it's kind of manual.

Instead of trying to categorize everything in one pass, we simply do it category by category and move the correctly categorized activities to another sheet. For example, you run the code just using the Aquatics category and keywords. You then sort the results by the category and visually scan it for any activities that shouldn't be there, such as descriptions containing "water color". You leave those behind with everything else, but move the correctly categorized items to another sheet. Now run the code using the Painting category against the remaining list. Keep doing this for each category and eventually you will have correctly categorized everything.

Excel was never intended to be very strong with text based data, so there is often some manual labor involved with this type of project.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 16, 2013 at 08:22:26
BTW..I have deleted your email address from your post. You should never post your email address in plain text in a public forum.

From our perspective, it's not about you receiving a lot of spam, it's more about this forum not becoming a site from which emails can be harvested. Once the bad guys start hanging around for any reason, we will be open to all sorts of security issues.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 16, 2013 at 08:27:13
Thank you! I also was thinking about the aquatics issue and realized there are probably enough other keywords that are used that we can just avoid water. That may also be the case for art but we'll see. If not, I like your solution, fact is we are already manually scanning the data to do a little cleaning.

I have to run out, then meeting at 11 CDT to finish up categories. Should be done ny noon.

Is there an easier more direct way to communicate or would you rather keep it all here. I hate make so many posts, this thread will get very long with not much useful info for anyone else. We can always post the solution when it's done?

Thanks again!

Report •

March 16, 2013 at 09:10:06
re: "...this thread will get very long with not much useful info for anyone else"

That is not necessarily true. Often times the details of one thread point other members in the right direction for their own questions. Categorizing data is not specific to your situation, text based searches can be used in many other projects and coding examples might just help someone looking for the syntax for an instruction.

Since all threads get saved in the archives, we can never know when a small nugget of information is just what someone is looking for.

That said, I have sent you an email address via private message. Please do not share it with anyone else. If it begins to receive unwanted emails, I will simply delete it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 1, 2013 at 13:13:26
DerbyDad03, I have a similar case for this one that I searched around, yet not find a solution.

I have the main spreadsheet with several info collected such as ref #, date, hours, and so on. Then another spreadsheet contains ref # that need to mark as "good" in the main spreadsheet.

Main spreadsheet:
1 5964 02/02/12 Drop list of "good", "take a look" ....
2 6588 02/02/12
3 1285 12/12/12

Spreadsheet 2:
1 1285
2 8562

Report •

April 1, 2013 at 16:05:04
Please post your question in it's own thread. Before doing so, please click on the following line and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 2, 2013 at 06:35:23
DerbyDad03, I posted and here is the link:


Report •

Ask Question