kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 922
- Office Version
- 365
Hi,
I have the following table:
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.
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.