Categorization based on keywords in a string « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Categorization based on keywords in a string

(10 posts) (4 voices)

Started 9 months ago by ajgodson
Latest reply from Luke M

Tags:

No tags yet.

ajgodson

Member
Joined: Sep '12
Posts: 5

Hi all - I could use help with the following problem.

In column A, I have a string of words. (e.g. Senior Process Engineer - Plano, TX)

In sheet 2, column B through column D, I have up to 3 keywords the help me categorize the string and in column E, I have the name of the category. (e.g, Columns B-D, "engineering", "engineer", "CPE". Column E, "Engineering")

I have about 25 categories, thus about 25 rows.

What formula or Macro can I write that essentially says, "If the keywords in columns B-D appear in the string in column A, return the value from column E"

I feel like I've been close with a series of formulas, but haven't been able to figure it out.

Any help would be greatly appreciated. If I can clarify in any way, please let me know. Thanks!

Posted 9 months ago #
Debraj Roy

Excel Ninja
Joined: Mar '12
Posts: 1,075

Hi, ajgodson!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.

You have not specified where do you want Value of E..
I guess If A1 contain CPE, then you want Value of E in Column D..

So try, beloW formula in B2, and copy the same to B2 to D25.

=IFERROR(IF(SEARCH(B$1,$A2)>0,$E2),"")

Please let us know, if you are looking for something else.. or paste your sample data with requirement..

for detail, visit below URL.
http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards,
Deb

Posted 9 months ago #
Luke M

Excel Ninja
Joined: Nov '10
Posts: 4,365

This will work if E1 contains your "value not found" message.
Array formula:
=INDEX(E:E,MAX(ISNUMBER(SEARCH(B2:D25,A2))*ROW(B2:D25)*(D2:D25<>"")))

Remeber to confirm formula using Ctrl+Shift+Enter not just Enter. In the formula, the B2:B10 array can be adjusted to fit the size of your actual data.

Posted 9 months ago #
Debraj Roy

Excel Ninja
Joined: Mar '12
Posts: 1,075

OOPS.. my mistake :( Sorry..
I have not read the post carefully..

Luke's formula is working perfect, with some normal modification..
use the below formula in Sheet 1 > B2. and drag as per requirement..
{=IFERROR(INDEX(Sheet2!$E$2:$E$25,MAX(ISNUMBER(SEARCH(Sheet2!$B$2:$D$25,A2))*ROW(Sheet2!$B$2:$D$25)-1)),"Not Found")}

I am assuming you have filled category Text in Sheet2 > B2 to D25..

Thanks Luke..

Regards,
Deb

Posted 9 months ago #
ajgodson

Member
Joined: Sep '12
Posts: 5

Hi Luke & Deb -

First, a HUGE thanks! It put a big smile on my face on Friday night to see two intelligent responses within mere hours of posting.

I feel like I'm close, but I keep getting the last result as the return instead of the result from the row. It's probably something simple I'm doing, but I'm hoping you could take a peek and see if there's something you'd recommend.

https://www.dropbox.com/s/aabksrgvf6iyucn/JM%20-%20Excel%20Help.xlsx

Many thanks for your help with this!

Adam

Posted 9 months ago #
Sajan

Member
Joined: May '12
Posts: 410

Hi Adam,
I think Deb and Luke interpreted your question as wanting to search the whole column, and not just the current row. If you are looking for a value based on just a given row, please try the following formula:

=IF(SUMPRODUCT(N(ISNUMBER(SEARCH(B1:D1, A1))))>0,E1, "Not found")

Copy it to additional rows.

Cheers,
Sajan.

Posted 9 months ago #
ajgodson

Member
Joined: Sep '12
Posts: 5

Hi Sajan - Thanks for the response! It is very much appreciated. I'm not 100% sure that gets me to where I want to be, as I need to search the string in column A for all the keywords and if it is found, then return the value in column E of whatever row the keyword was found in.

If it's user error, feel free to point that out, too. Thx!

Adam

Posted 9 months ago #
Luke M

Excel Ninja
Joined: Nov '10
Posts: 4,365
ajgodson,

Here's the sample data I created, and formula output
```
A                B        C                 D             E                F
String	      Keyword1	Keyword2	Keyword3	No Value     FormulaOutput
I am a fox	cat	cry		                c-words		f-words
I am a cat	dog			                d-words		c-words
I am dog	frog	fox	         fly             f-words	d-words
I am nothing						                No Value
```
Array formula in F2 is:
=INDEX(E:E,MAX(ISNUMBER(SEARCH($B$2:$D$25,A2))*ROW($B$2:$D$25)*($B$2:$D$25<>"")))

Note that I did change my original formula a little to include absolute references, and corrected the last array to be b2:d25 instead of d2:d25. If the above output is not what you are looking for, can you correct me? Thanks.
Posted 9 months ago #
ajgodson

Member
Joined: Sep '12
Posts: 5

Luke - Glorious! Thank you for all your help.

Adam

Posted 9 months ago #
Luke M

Excel Ninja
Joined: Nov '10
Posts: 4,365

Hurrah! Glad I was able to clear things up, and to hear that things are working okay now. =)

Posted 9 months ago #