Find "brand" keywords in free text fields

[TrimFunction]

background: I'm building a reference file of branded product names. My aim is to have a clean list that can be used to search through a free text fields from a CRM system.

I want to find brand-names, but do not want common words like "account" or "management", since that will return almost every entry. the list of terms i search for is usually about 300+, using {=INDEX($B$2:$B$300,MATCH(1,COUNTIF(I3,"*"&$A$2:$A$300&"*"),0))}.

Question: What is the best way to "scrub" out the brand names from free text?

my current process is start with the list:

Original Product Name

Accounts Receivable, JD Edwards EnterpriseOne

IBM Kenexa Employee Assessments

Catalog Management, PeopleSoft

SAP Ariba Invoice Management Text to columns, sort, then manually read through to determine which are "brand" names: Column A Column B Accounts SKIP-Common Ariba USE Assessments SKIP-Common Catalog SKIP-Common Edwards USE Employee SKIP-Common EnterpriseOne USE IBM USE Invoice SKIP-Common JD USE Kenexa USE Management SKIP-Common Management, SKIP-Common PeopleSoft USE Receivable, SKIP-Common SAP USE <colgroup><col><col></colgroup><tbody> </tbody> I've been using countifs to determine how frequent the word occurs and LEN to quickly go through short words, but this is not efficient. Is there a better way to do this?

<colgroup><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Is there a better way to do this?

Probably.

But from what you have posted, I can't really tell what you have, where, and exactly what you are trying to do.

Can you give us some small dummy sample data and the expected results and clearly show or describe where various data is and where the results should go?
Better (clearer) ways to present your sample data are given in a link in my signature block below.

 

[sandy666]

How about PowerQuery (Get&Transform) ?

Table1		Table2 (user defined)		Result table
Product name		Common		Keywords
Accounts Receivable, JD Edwards EnterpriseOne		Accounts		Ariba
IBM Kenexa Employee Assessments		Assessments		Edwards
Catalog Management, PeopleSoft		Catalog		EnterpriseOne
SAP Ariba Invoice Management		Employee		IBM
		Invoice		JD
		Management		Kenexa
		Receivable		PeopleSoft
				SAP

Table1: source
Table2: user-defined words that should not be taken into account

for table1:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,",","",Replacer.ReplaceText,{"Product name"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Product name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Product name")
in
    #"Split Column by Delimiter"[/SIZE]

for result table:

[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"Product name"},Table2,{"Common"},"Table3",JoinKind.LeftAnti),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Product name"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Product name", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Product name", "Keywords"}})
in
    #"Renamed Columns"[/SIZE]

example file

 

[sandy666]

I forgot about duplicates, so

for result table

[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"Product name"},Table2,{"Common"},"Table3",JoinKind.LeftAnti),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Product name"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Product name", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Product name", "Keywords"}})
in
    #"Renamed Columns"[/SIZE]