Keyword lookup from a list of keywords in a column of text descriptions

[swmicros]

I have an Excel database of invoice data with a text column for the description of the work performed. I need to identify (T/F in a column?) any invoice that has any keywords from a list in the description column. I have not yet created the list of keywords as I am not sure how they should be arranged, your suggestion will be welcomed.

How would I do that?

Simplified version of what I am after:

Book4
	A	B	C	D	E
1	Invoice	Description	T/F		Keyword list
2	15000	Sorted apples and put into boxes for Bill	F		register, cash, bagged, bag, bagging
3	15001	Worked the register for Suzy, who was out today for vacation.	T
4	15002	Worked produce with Bill	F
5	15003	Stocked shelves during night shift to help Ken	F
6	15004	helped at the cash register as per Bob	T
7	15005	brought in carts from parking lot on Friday	F
8	15006	Stocked produce shelves with Javier	F
9	15007	bagging at register, with Suzy	T
10	15008	Filled in in bakery dept on Sat, with Mary	F
Sheet1

 

[Coyotex3]

Hi & Welcome to Mr.Excel

Does this work

=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($K$2,", "),B2:B10)),1)

Book1
	A	B	C	D
1	Invoice	Description	T/F
2	15000	Sorted apples and put into boxes for Bill	F	FALSE
3	15001	Worked the register for Suzy, who was out today for vacation.	T	TRUE
4	15002	Worked produce with Bill	F	FALSE
5	15003	Stocked shelves during night shift to help Ken	F	FALSE
6	15004	helped at the cash register as per Bob	T	TRUE
7	15005	brought in carts from parking lot on Friday	F	FALSE
8	15006	Stocked produce shelves with Javier	F	FALSE
9	15007	bagging at register, with Suzy	T	TRUE
10	15008	Filled in in bakery dept on Sat, with Mary	F	FALSE
Sheet2

Cell Formulas
Range	Formula
D2:D10	D2	=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($K$2,", "),B2:B10)),1)
Dynamic array formulas.

Edit: This option might not work as well as I expected. Looking for a more robust solution

 

[swmicros]

Hi & Welcome to Mr.Excel

Does this work

=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($K$2,", "),B2:B10)),1)

Book1
	A	B	C	D
1	Invoice	Description	T/F
2	15000	Sorted apples and put into boxes for Bill	F	FALSE
3	15001	Worked the register for Suzy, who was out today for vacation.	T	TRUE
4	15002	Worked produce with Bill	F	FALSE
5	15003	Stocked shelves during night shift to help Ken	F	FALSE
6	15004	helped at the cash register as per Bob	T	TRUE
7	15005	brought in carts from parking lot on Friday	F	FALSE
8	15006	Stocked produce shelves with Javier	F	FALSE
9	15007	bagging at register, with Suzy	T	TRUE
10	15008	Filled in in bakery dept on Sat, with Mary	F	FALSE
Sheet2

Cell Formulas
Range	Formula
D2:D10	D2	=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($K$2,", "),B2:B10)),1)
Dynamic array formulas.

Edit: This option might not work as well as I expected. Looking for a more robust solution

To be clear, Col C in my example is where this unknown formula should go, it is NOT part of the data. Sorry, didn't make that clear. Also the list of keywords can be whatever format is best suited for the task. I am thinking the TEXTSPLIT command may be unnecessary if the keyword list is a column instead of a comma delimited cell, correct?

 

[Coyotex3]

In that case we would just place the formula in Column C instead.

Book1
	A	B	C
1	Invoice	Description	T/F
2	15000	Sorted apples and put into boxes for Bill	FALSE
3	15001	Worked the register for Suzy, who was out today for vacation.	TRUE
4	15002	Worked produce with Bill	FALSE
5	15003	Stocked shelves during night shift to help Ken	FALSE
6	15004	helped at the cash register as per Bob	TRUE
7	15005	brought in carts from parking lot on Friday	FALSE
8	15006	Stocked produce shelves with Javier	FALSE
9	15007	bagging at register, with Suzy	TRUE
10	15008	Filled in in bakery dept on Sat, with Mary	FALSE
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($K$2,", "),B2:B10)),1)
Dynamic array formulas.

However as I put on my Edit above, this formula is not truly accurate. If one of the descriptions were to include cash, bagged, bag, or bagging but does not include the word 'Register" it would return a False. Like here where the second to last row says "Bagging the cash, with Suzy". I assume you would still want this row to return TRUE:

Book1
	A	B	C
1	Invoice	Description	T/F
2	15000	Sorted apples and put into boxes for Bill	FALSE
3	15001	Worked the register for Suzy, who was out today for vacation.	TRUE
4	15002	Worked produce with Bill	FALSE
5	15003	Stocked shelves during night shift to help Ken	FALSE
6	15004	helped at the cash register as per Bob	TRUE
7	15005	brought in carts from parking lot on Friday	FALSE
8	15006	Stocked produce shelves with Javier	FALSE
9	15007	bagging the cash, with Suzy	FALSE
10	15008	Filled in in bakery dept on Sat, with Mary	FALSE
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($K$2,", "),B2:B10)),1)
Dynamic array formulas.

 

[swmicros]

In that case we would just place the formula in Column C instead.

Book1
	A	B	C
1	Invoice	Description	T/F
2	15000	Sorted apples and put into boxes for Bill	FALSE
3	15001	Worked the register for Suzy, who was out today for vacation.	TRUE
4	15002	Worked produce with Bill	FALSE
5	15003	Stocked shelves during night shift to help Ken	FALSE
6	15004	helped at the cash register as per Bob	TRUE
7	15005	brought in carts from parking lot on Friday	FALSE
8	15006	Stocked produce shelves with Javier	FALSE
9	15007	bagging at register, with Suzy	TRUE
10	15008	Filled in in bakery dept on Sat, with Mary	FALSE
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($K$2,", "),B2:B10)),1)
Dynamic array formulas.

However as I put on my Edit above, this formula is not truly accurate. If one of the descriptions were to include cash, bagged, bag, or bagging but does not include the word 'Register" it would return a False. Like here where the second to last row says "Bagging the cash, with Suzy". I assume you would still want this row to return TRUE:

Book1
	A	B	C
1	Invoice	Description	T/F
2	15000	Sorted apples and put into boxes for Bill	FALSE
3	15001	Worked the register for Suzy, who was out today for vacation.	TRUE
4	15002	Worked produce with Bill	FALSE
5	15003	Stocked shelves during night shift to help Ken	FALSE
6	15004	helped at the cash register as per Bob	TRUE
7	15005	brought in carts from parking lot on Friday	FALSE
8	15006	Stocked produce shelves with Javier	FALSE
9	15007	bagging the cash, with Suzy	FALSE
10	15008	Filled in in bakery dept on Sat, with Mary	FALSE
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($K$2,", "),B2:B10)),1)
Dynamic array formulas.

MrExcelTestExample.xlsx
	A	B	C	D
1	Invoice	Description	Keyword Match	Keyword List
2	15000	Sorted apples and put into boxes for Bill	FALSE	cash, register, bagged, bagging
3	15001	Worked the register for Suzy, who was out today for vacation.	FALSE
4	15002	Worked produce with Bill	FALSE
5	15003	Stocked shelves during night shift to help Ken	FALSE
6	15004	helped at the cash register as per Bob	TRUE
7	15005	brought in carts from parking lot on Friday	FALSE
8	15006	Stocked produce shelves with Javier	FALSE
9	15007	bagging at register, with Suzy	FALSE
10	15008	Filled in in bakery dept on Sat, with Mary	FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=CHOOSECOLS(ISNUMBER(SEARCH(TEXTSPLIT($D$2,", "),B2:B10)),1)
Dynamic array formulas.

You are correct, multiple matches should still result in TRUE. However, I can't seem to get the same results as you. It it is only matching on the keyword cash, not any any of the others in the list.

 

[AhoyNC]

Maybe list your keywords in a column.

Book5
	A	B	C	D	E
1	Invoice	Description	T/F		Keyword list
2	15000	Sorted apples and put into boxes for Bill	F		register
3	15001	Worked the register for Suzy, who was out today for vacation.	T		cash
4	15002	Worked produce with Bill	F		bagged
5	15003	Stocked shelves during night shift to help Ken	F		bag
6	15004	helped at the cash register as per Bob	T		bagging
7	15005	brought in carts from parking lot on Friday	F
8	15006	Stocked produce shelves with Javier	F
9	15007	bagging at register, with Suzy	T
10	15008	Filled in in bakery dept on Sat, with Mary	F
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=IF(SUM(--ISNUMBER(SEARCH($E$2:$E$6,$B2)))>0,"T","F")
C10	C10	=IF(SUM(--ISNUMBER(SEARCH($E$2:$E$6,B10)))>0,"T","F")

 

[AhoyNC]

In my post#6 above just copy or drag the formula in C2 down. Formula in C10 copied over from a test I did (it's the same as C2).

 

[Peter_SSs]

I am thinking the TEXTSPLIT command may be unnecessary if the keyword list is a column instead of a comma delimited cell, correct?

Correct and I agree with @AhoyNC that the column list would be a better idea.

However, if you are looking for key words then I think you will need a bit more than has been suggested. For example, I have used the post #6 suggestion in column C below and to me the results in rows 11:13 may not be correct because the formula has checked for "text strings" not for "words". If you are looking for "words" then you could try the column D formula.

24 01 13.xlsm
	B	C	D	E
1	Description	T/F		Keyword list
2	Sorted apples and put into boxes for Bill	F	F	register
3	Worked the register for Suzy, who was out today for vacation.	T	T	cash
4	Worked produce with Bill	F	F	bagged
5	Stocked shelves during night shift to help Ken	F	F	bag
6	helped at the cash register as per Bob	T	T	bagging
7	brought in carts from parking lot on Friday	F	F
8	Stocked produce shelves with Javier	F	F
9	bagging at register, with Suzy	T	T
10	Filled in in bakery dept on Sat, with Mary	F	F
11	Registered my hours worked	T	F
12	Restocked the cashew bin	T	F
13	Displayed new handbags	T	F
Keywords

Cell Formulas
Range	Formula
C2:C13	C2	=IF(SUM(--ISNUMBER(SEARCH($E$2:$E$6,$B2)))>0,"T","F")
D2:D13	D2	=IF(COUNT(SEARCH(" "&E$2:E$6&" "," "&B2&" "))>0,"T","F")

 

[swmicros]

Peter - Good catch, yes that is ultimately what I will want a whole word type search, not a string search - thanks.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.