Excel to find key words and paste the same in Criteria column

[tinferns]

Team.. Need your help again. I believe it is possible for Excel to find key words as described by user and paste the same in the dedicated cell. And I also believe this can be done without using a Macro. Please help out on the below.. I prefer not to use Macro's if possible please.

Keyword and their expected output:

Key Words (NOT case sensitive): (This is just an example Key Word list.. actual one is approx. 20 items/values)	Result
Flight	Flight
Flights	Flight
Ticket	Air Ticket
Air Ticket	Air Ticket
Air Tickets	Air Ticket
PNR	PNR
PNR's	PNR
PNRs	PNR

Below is an example (In some cases there are 2 key words used, Excel can choose the first key word in such cases)

Description	Criteria		Expected Result
ABC bought a flight ticket paying XXX amount			Flight
XYZ bought 3 Air Tickets paying xxx amount			Air Ticket
CDE bought 1 Air Ticket vide PNR - XXXXXXX			Air Ticket

Hope to hear from you soon. Thanks.. M

 

[GraH]

Hi, maybe this works (depending on your version)

Book1
	A	B	C	D	E	F	G	H
1	Description	Criteria		Key Words	Result
2	ABC bought a flight ticket paying XXX amount	Flight		Flight	Flight		In Name Manager	Formula
3	XYZ bought 3 Air Tickets paying xxx amount	Air Ticket		Flights	Flight		vXML	="<text><a>"&SUBSTITUTE(Sheet1!$A1;" ";"</a><a>")&"</a></text>"
4	CDE bought 1 Air Ticket vide PNR - XXXXXXX	Air Ticket		Ticket	Air Ticket		vPosKeyWord	=AGGREGATE(15;6;MATCH(tKeyWords[Key Words];FILTERXML(vXML;"//a");0);1)
5				Tickets	Air Ticket
6				PNR	PNR
7				PNR's	PNR
8				PNRs	PNR
9
10
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(tKeyWords[Result],MATCH(FILTERXML(vXML,"/text/a["&vPosKeyWord&"]"),tKeyWords[Key Words],0))

 

[GraH]

Or Power Query solution
- Loading both tables to PQ
- Save tKeyWords as connection only (jst used as lookup table)
- Convert the text range to a table named tText, and apply the following M-code

let
    Source = Excel.CurrentWorkbook(){[Name="tText"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    WordsAsTable = Table.AddColumn(#"Changed Type", "Words", each Table.FromList(Text.Split(Text.Lower([Description])," "), Splitter.SplitByNothing(),{"Words"})),
    MergeKeyWords = Table.AddColumn(WordsAsTable, "MatchKeyWords", each Table.NestedJoin([Words], {"Words"}, tKeyWords, {"Key Words"}, "tKeyWords", JoinKind.Inner)),
    CombineMatches = Table.AddColumn(MergeKeyWords, "ExtractKeyWords", each Table.Combine([MatchKeyWords][tKeyWords])[Result]),
    ExtractResults = Table.TransformColumns(CombineMatches, {"ExtractKeyWords", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    RemoveHelpers = Table.RemoveColumns(ExtractResults,{"Words", "MatchKeyWords"})
in
    RemoveHelpers

Book1
	A	B	C	D
1	Description		Key Words	Result
2	ABC bought a flight ticket paying XXX amount		Flight	Flight
3	XYZ bought 3 Air Tickets paying xxx amount		Flights	Flight
4	CDE bought 1 Air Ticket vide PNR - XXXXXXX		Ticket	Air Ticket
5			Tickets	Air Ticket
6			PNR	PNR
7			PNR's	PNR
8			PNRs	PNR
9
10
11	Description	ExtractKeyWords
12	ABC bought a flight ticket paying XXX amount	Flight;Air Ticket
13	XYZ bought 3 Air Tickets paying xxx amount	Air Ticket
14	CDE bought 1 Air Ticket vide PNR - XXXXXXX	Air Ticket;PNR
PQ

 

[tinferns]

Hi GraH.. Thanks for your quick responses. I am not so familiar with Power Query, so I prefer going with Option 1.

I didn't quite understand the Name Manager and the formulas'. Where do I paste those formulas ?

Can I keep the key words in a different sheet or file ?

Please advise.

Thanks in advance.

M

 

[GraH]

Hi, in the final formula replace the arguments vXML and vPosKeyWord by the formulas I've given.
Using the name manager avonds having to repeat long formula Parts amongst others.
You can have your Keywords on another sheet. With PQ in can even be another workbook.

 

[Peter_SSs]

@tinferns
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Since some of your texts contain more than one key word, can you confirm exactly what result you want (eg. first key word, last key word, any key word, all key words)?

 

[GraH]

@tinferns
Since some of your texts contain more than one key word, can you confirm exactly what result you want (eg. first key word, last key word, any key word, all key words)?

True, the given list may be too short to ensure my formula works in all cases at hand.

So maybe this revisited formula might be better suited:

Book1
	A	B	C	D	E
1	Description	Result		Key Words	Result
2	ABC bought a flight ticket paying XXX amount	Flight		Flight	Flight
3	XYZ bought 3 Air Tickets paying xxx amount	Air Ticket		Flights	Flight
4	CDE bought 1 Air Ticket vide PNR - XXXXXXX	Air Ticket		Ticket	Air Ticket
5				Air Ticket	Air Ticket
6				Air Tickets	Air Ticket
7				PNR	PNR
8				PNR's	PNR
9				PNRs	PNR
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(tKeyWords[Result],AGGREGATE(15,6,(ROW(tKeyWords[Key Words])-ROW(tKeyWords[[#Headers],[Key Words]]))/(ISNUMBER(SEARCH(tKeyWords[Key Words],A2))),1))

 

[tinferns]

Thanks GraH for Option 3 .. I tried the above Index formula in B2 as suggested. Got an error as attached.

As mentioned in my first post, I am okay if excel picks up the first value that matches key values. For eg. in the below example there are 2 key words. Its okay for the formula to pick up the first key word

CDE bought 1 Air Ticket vide PNR - XXXXXXX

Here there are 2 Key words 1) Air Ticket and 2) PNR. Its okay if Excel picks up Air Ticket.

If it's complex to create with a formula (I am guessing it is), I am ok to work with VBA (Macros) if that will make things simple. Please decide and advise.

Thanks,

M

 

[GraH]

Hello tinferns, notice I used a table for your keywords. Select a cell in that range, press CTRL+T and name it tKeyWords. See if that works. I can't help with VBA.

 

[Peter_SSs]

I am okay if excel picks up the first value that matches key values.

Does that imply that it would also be okay to pick up the last one or all of them? If so, here are some formulas that should do that. (I do note that the 'All Key Words' formula results in a duplicate value in row 15 because the text contains 'Ticket' and 'Air Ticket', both of which are key Words.)

BTW, thanks for updating your profile.

20 10 04.xlsm
	A	B	C	D	E
1	Key Words	Result
2	Flight	Flight
3	Flights	Flight
4	Ticket	Air Ticket
5	Air Ticket	Air Ticket
6	Air Tickets	Air Ticket
7	PNR	PNR
8	PNR's	PNR
9	PNRs	PNR
10
11
12	Description	Criteria		Last Key Word	All Key Words
13	ABC bought a flight ticket paying XXX amount			Air Ticket	Flight, Air Ticket
14	XYZ bought 3 Air Tickets paying xxx amount			Air Ticket	Air Ticket
15	CDE bought 1 Air Ticket vide PNR - XXXXXXX			PNR	Air Ticket, Air Ticket, PNR
Key Words

Cell Formulas
Range	Formula
D13:D15	D13	=LOOKUP(9.99E+307,SEARCH(" "&$A$2:$A$9&" "," "&A13&" "),$B$2:$B$9)
E13:E15	E13	=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(" "&$A$2:$A$9&" "," "&A13&" ")),B$2:B$9,""))