categorize text strings by keywords and their synonyms with textjoin, if, countif

[Qcarper]

Hi,


I'm trying to automatically categorize a large amount of products by finding keywords in the product descriptions. Using a textjoin, if, count if fucntion works perfectly because if mutiple keywords match, it indexes all of them.
But these products come from a range of different suppliers often using slightly different words for the same product, that's why for most keywords i wrote some synonyms behind them, but i dont want to index all of these synonyms if they match, just the first keyword.


=TEXTJOIN(", ",TRUE,IF(COUNTIF($B$9:$B$12,"*"&$F$9:$F$11&"*"),$F$9:$I$11,""))

This table has thousands of products and more products are added regularly so a dynamic solution for this would be perfect.

could someone help me with this?

 

[Fluff]

Hi & welcome to MrExcel.
Can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Qcarper]

Hi & welcome to MrExcel.
Can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Table - Data insert.xlsx
	B	C	D	E	F	G	H	I
5				=TEXTJOIN(", ",TRUE,IF(COUNTIF($B$9:$B$12,"*"&$F$9:$F$11&"*"),$F$9:$I$11,""))
6	now
7
8	Item description	category			Keyword list
9	This item has a synonym a1 and keyword b	Synonym a1, 0, keyword b			keyword a	Synonym a1	Synonym a2
10	Red Synonym b2	0, Synonym b2			keyword b	Synonym b1	Synonym b2	Synonym b3
11	Blue and white synonym c1 with a square synonym b3	0, Synonym b3, Synonym c1			keyword c	Synonym c1	Synonym c2	Synonym c3
12	Item without a know keyword	0
13
14
15	my whish
16
17	Item description	category			Keyword list
18	This item has a synonym a1 and keyword b	Keyword a, keyword b			keyword a	Synonym a1	Synonym a2	Synonym a3
19	Red Synonym b2	Keyword b			keyword b	Synonym b1	Synonym b2	Synonym b3
20	Blue and white synonym c1 with a square synonym b3	Keyword c, keyword b			keyword c	Synonym c1	Synonym c2	Synonym c3
21	Item without a know keyword
Sheet1

Cell Formulas
Range	Formula
C9:C12	C9	=TEXTJOIN(", ",TRUE,IF(COUNTIF($B9,"*"&$F$9:$I$11&"*"),$F$9:$I$11,""))

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	B	C	D	E	F	G	H	I
8	Item description	category			Keyword list
9	This item has a synonym a1 and keyword b	keyword a, keyword b			keyword a	Synonym a1	Synonym a2	Synonym a3
10	Red Synonym b2 keyword b	keyword b			keyword b	Synonym b1	Synonym b2	Synonym b3
11	Blue and white synonym c1 with a square synonym b3	keyword b, keyword c			keyword c	Synonym c1	Synonym c2	Synonym c3
12	Item without a know keyword
13
Lists

Cell Formulas
Range	Formula
C9:C12	C9	=TEXTJOIN(", ",TRUE,IF(MMULT(--(ISNUMBER(SEARCH($F$9:$I$11,B9))),SEQUENCE(COLUMNS($F$9:$I$9),,,0))>0,$F$9:$F$11,""))

 

[Qcarper]

Thank you very much for your reply.

But something seems to be wrong, it definitely works in the XL2BB example you sent. But when I paste it to my own worksheet this happens:

even though the formula is the same:

For the actual data model i need this for it means this XD

it goes on for a little while...

Do you perhaps know what could cause this?

 

[Fluff]

It's because you have a blank cell in the F9:I11, try it like this instead

=TEXTJOIN(", ",TRUE,IF(MMULT(($F$9:$I$11<>"")*(ISNUMBER(SEARCH($F$9:$I$11,B9))),SEQUENCE(COLUMNS($F$9:$I$9),,,0))>0,$F$9:$F$11,""))

 

[Qcarper]

It's because you have a blank cell in the F9:I11, try it like this instead

=TEXTJOIN(", ",TRUE,IF(MMULT(($F$9:$I$11<>"")*(ISNUMBER(SEARCH($F$9:$I$11,B9))),SEQUENCE(COLUMNS($F$9:$I$9),,,0))>0,$F$9:$F$11,""))

Oh my god u absolut madlad!!
thank you so much, this is going to save me so many hours!

 

[Fluff]

You're welcome & thanks for the feedback.