Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Categorizing an excel column
I have an excel spreadsheet. In column A, is a list of 15,000 phrases
consisting of 5-7 words. Last night, I was going to go through each phrase in the list, look for keywords and if it contained it, I would enter the keywords in the adjoining columns. This would be quite tedious. Is there any simple way for me to categorize this list using macros or some third party addon. As an example, lets say I wanted to to categorize the list based on race, religion, sex, and country of origin. I would enter specific criteria in the macro such as race could equal white, black, asian, indian, religion could equal christian, jewish, muslim, sex could be m or f and country of origin could be America, or Europe. The macro would look for these specific words in each of the phrases and if the phrase contained those keywords, it would tag that phrase by putting the keyword in the adjoining columns. The end result would be as follows: One phrase would be White Christian male teacher. Using the above example, Column B next to this phrase would contain white Column C next to this phrase would contain christian Column D next to this phrase would contain male Column D next to this phrase would remain blank as it doesn't have any criteria defined in the macro Thank you in advance for any help provided. Jason |
#2
|
|||
|
|||
Here's one way ...
Assuming that all your phrases are in Column A starting at Row2, and your various keywords are in B1, C1, D1, etc. Then put this formula into B2, then copy it across Row 2 as far as you need, and down as many rows as you need. =IF(ISERROR(FIND(B$1,$A2)),"",B$1) Then whenever you chamnge the Keywords across Row 1, the results will recalculate. Hope this suits your needs, Rgds, ScottO "Anna" wrote in message news:x8FBe.1956352$Xk.1461960@pd7tw3no... | I have an excel spreadsheet. In column A, is a list of 15,000 phrases | consisting of 5-7 words. | | Last night, I was going to go through each phrase in the list, look for | keywords and if it contained it, I would enter the keywords in the adjoining | columns. This would be quite tedious. | | Is there any simple way for me to categorize this list using macros or some | third party addon. | | As an example, lets say I wanted to to categorize the list based on race, | religion, sex, and country of origin. I would enter specific criteria in | the macro such as race could equal white, black, asian, indian, religion | could equal christian, jewish, muslim, sex could be m or f and country of | origin could be America, or Europe. | | The macro would look for these specific words in each of the phrases and if | the phrase contained those keywords, it would tag that phrase by putting the | keyword in the adjoining columns. | | The end result would be as follows: | | One phrase would be White Christian male teacher. Using the above example, | | Column B next to this phrase would contain white | Column C next to this phrase would contain christian | Column D next to this phrase would contain male | Column D next to this phrase would remain blank as it doesn't have any | criteria defined in the macro | | Thank you in advance for any help provided. | | Jason | | | |
#3
|
|||
|
|||
Thank you for your tip, that worked really well. I do appreciate your
assitance in making my job much easier "ScottO" wrote in message ... Here's one way ... Assuming that all your phrases are in Column A starting at Row2, and your various keywords are in B1, C1, D1, etc. Then put this formula into B2, then copy it across Row 2 as far as you need, and down as many rows as you need. =IF(ISERROR(FIND(B$1,$A2)),"",B$1) Then whenever you chamnge the Keywords across Row 1, the results will recalculate. Hope this suits your needs, Rgds, ScottO "Anna" wrote in message news:x8FBe.1956352$Xk.1461960@pd7tw3no... | I have an excel spreadsheet. In column A, is a list of 15,000 phrases | consisting of 5-7 words. | | Last night, I was going to go through each phrase in the list, look for | keywords and if it contained it, I would enter the keywords in the adjoining | columns. This would be quite tedious. | | Is there any simple way for me to categorize this list using macros or some | third party addon. | | As an example, lets say I wanted to to categorize the list based on race, | religion, sex, and country of origin. I would enter specific criteria in | the macro such as race could equal white, black, asian, indian, religion | could equal christian, jewish, muslim, sex could be m or f and country of | origin could be America, or Europe. | | The macro would look for these specific words in each of the phrases and if | the phrase contained those keywords, it would tag that phrase by putting the | keyword in the adjoining columns. | | The end result would be as follows: | | One phrase would be White Christian male teacher. Using the above example, | | Column B next to this phrase would contain white | Column C next to this phrase would contain christian | Column D next to this phrase would contain male | Column D next to this phrase would remain blank as it doesn't have any | criteria defined in the macro | | Thank you in advance for any help provided. | | Jason | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel XP - 2 Column Pages | New Users to Excel | |||
Excel: is it possible to move column contents into a line | Excel Discussion (Misc queries) | |||
Column A is Town, Column B is names. How can Excel add & tell how. | Charts and Charting in Excel | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
Excel - option to extend function in cell to column | Excel Worksheet Functions |