Categorize Text Based on Criteria

[kumara_faith]

Hi,

I have the following table:

Book1
	C	D	E	F	G	H	I	J	K
4	Date	Notes	Check Notes	Category			Text Check	Text Category	Category
5	7/1/2022	Sales comission paid to JHG fully	Sales	Sales Refund Process			Sales	Sales Refund	Sales Refund Process
6	8/1/2022	Sales refund made to Michale 4487UY	Sales, Refund	Sales Refund Process			Refund	Sales Cancel	Sales Cancellation Process
7	9/1/2022	Sales cancellation completed for John 1147PO	Sales, Cancel	Sales Cancellation Process			Cancel	Sales Double	Sales Double Duplicate Process
8	10/1/2022	Journal entry completed for JH56664					Double	Sales Premium	Sales Premium Reversal Process
9	11/1/2022	Debit balance reversed on HG78787					Premium	Sales	Sales Refund Process
10	12/1/2022	Sales double comission canceled	Sales, Cancel, Double	Sales Double Duplicate Process
11	12/1/2022	Sales premium reversed on policy YU77878	Sales, Premium	Sales Premium Reversal Process
Sheet1

Cell Formulas
Range	Formula
E5:E11	E5	=TEXTJOIN(", ", TRUE, IF(COUNTIF(D5, "*"&$I$5:$I$9&"*"), $I$5:$I$9, ""))

Column D has the free form notes from the processor. I am trying to categorize these notes in certain category. First, I am narrowing it down by key words with a formula in column E. Next, in column F, I would like to categorize it based on the category in column K. Is there a way to use a formula to achieve this ? The correct result is in column F. Appreciate all the help.

 

[jtakw]

Hi,

Use F1 formula, but you Must Keep J9:K9 values at the Bottom of your table.
Otherwise use G1 formula, then your J9:K9 values can be anywhere within your table.

Book3.xlsx
	C	D	E	F	G	H	I	J	K
4	Date	Notes	Check Notes	Category			Text Check	Text Category	Category
5	1/7/2022	Sales comission paid to JHG fully	Sales	Sales Refund Process	Sales Refund Process		Sales	Sales Refund	Sales Refund Process
6	1/8/2022	Sales refund made to Michale 4487UY	Sales, Refund	Sales Refund Process	Sales Refund Process		Refund	Sales Cancel	Sales Cancellation Process
7	1/9/2022	Sales cancellation completed for John 1147PO	Sales, Cancel	Sales Cancellation Process	Sales Cancellation Process		Cancel	Sales Double	Sales Double Duplicate Process
8	1/10/2022	Journal entry completed for JH56664					Double	Sales Premium	Sales Premium Reversal Process
9	1/11/2022	Debit balance reversed on HG78787					Premium	Sales	Sales Refund Process
10	1/12/2022	Sales double comission canceled	Sales, Cancel, Double	Sales Double Duplicate Process	Sales Double Duplicate Process
11	1/12/2022	Sales premium reversed on policy YU77878	Sales, Premium	Sales Premium Reversal Process	Sales Premium Reversal Process
Sheet957

Cell Formulas
Range	Formula
F5:F11	F5	=IF(E5="","",LOOKUP(2,1/SEARCH(SUBSTITUTE(SUBSTITUTE(E5,",",""),"Cancel Double","Double"),J$5:J$9),K$5:K$9))
G5:G11	G5	=IF(E5="","",LOOKUP(2,1/SEARCH(SUBSTITUTE(SUBSTITUTE(E5&E5,",",""),"Cancel Double","Double"),J$5:J$9&J$5:J$9),K$5:K$9))

 

[kumara_faith]

Hi jtakw,

Thank you for the solution and appreciate it.

 

[jtakw]

You're welcome, thanks for the feedback.