Categorization of the text with multiple keywords in excel

**Anirudh Chauhan** · 08-08-2019, 11:03 AM

I have to categorize my Description based on the Keywords and mention its Type in the Output Category

This can be seen below with an example. There are certain keywords with the type and I want the output category filled with the Type corresponding to the Description

s1.PNG
s2.PNG

With the help of the below function,

=INDEX(J:J,MATCH(1,IFNA(MATCH("asterisk"'&K:K&"asterisk",A2,0),0),0))

I can achieve the required result in B2 and B3 cell. However,I want the value in B4 to be 'performance,Quality' because 'EAM access issue' lies in both the type.

**KOKOSEK** · 08-08-2019, 11:47 AM

Excel 2016 (Windows) 32 bit

	A	B	J	K
1	desc	output	type	keywords
2	key1 operation	action1	performance	access
3	other2 key1	other1,action1	quality	EAM
4	EAM access issue	performance,quality	other1	other2
5	EAM key2	quality,action2	action2	key2
6			action1	key1

	A	B
1	desc	output
2	key1 operation	=TEXTJOIN(",",1,IF(IFNA(MATCH(""&$K$1:$K$10&"",A2,0)*($K$1:$K$10<>""),0)<>0,$J$1:$J$10,""))
3	other2 key1	=TEXTJOIN(",",1,IF(IFNA(MATCH(""&$K$1:$K$10&"",A3,0)*($K$1:$K$10<>""),0)<>0,$J$1:$J$10,""))
4	EAM access issue	=TEXTJOIN(",",1,IF(IFNA(MATCH(""&$K$1:$K$10&"",A4,0)*($K$1:$K$10<>""),0)<>0,$J$1:$J$10,""))
5	EAM key2	=TEXTJOIN(",",1,IF(IFNA(MATCH(""&$K$1:$K$10&"",A5,0)*($K$1:$K$10<>""),0)<>0,$J$1:$J$10,""))

Sheet: Sheet1

**Anirudh Chauhan** · 08-09-2019, 05:08 AM

Hi. Thanks for the reply. What if I dont have office 365. it doesnot work
Is there any way to achieve it through VBA code?

**KOKOSEK** · 08-09-2019, 05:23 AM

OK, so instead of TEXTJOIN you can use this function:

Please Login or Register  to view this content.

put it into Module in VBA and change formulas like below (B2):

Formula:

Please Login or Register  to view this content.

**Anirudh Chauhan** · 08-09-2019, 06:45 AM

It worked like a charm brother. thanks a ton

**JohannesB** · 03-07-2021, 04:05 AM

Hi, Its been a while since this thread was active, but i have a problem related to this discussion.
I use
=INDEX(J:J,MATCH(1,IFNA(MATCH("asterisk"'&K:K&"asterisk",A2,0),0),0)), for the same purpose as in the first post. My problem occurs if there is a blank cell in "output category" column, then the forumla calculates everything below the blank to be "0".

Anyone can help me with this?

Johannes

Categorization of the text with multiple keywords in excel

LinkBack

Thread Tools

Rate This Thread

Display

Categorization of the text with multiple keywords in excel

Re: Categorization of the text with multiple keywords in excel

Re: Categorization of the text with multiple keywords in excel

Re: Categorization of the text with multiple keywords in excel

Re: Categorization of the text with multiple keywords in excel

Re: Categorization of the text with multiple keywords in excel

Thread Information

Users Browsing this Thread

Similar Threads

Search for multiple keywords within a single text cell

[SOLVED] Find Multiple keywords from free text cell

[SOLVED] VBA looking for multiple keywords and return concatenated keywords, that were found

[SOLVED] Challenge for real excel experts!! complex categorization on multiple criteria

[SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords

Find multiple keywords/text and copy entire row into new spreadsheet?

Excel 2007 : Search for multiple keywords in a text cell; then look at another if not found

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions