Categorise according to key words in item description

[tigerzen]

Hi all, I would like to automatically populate a cell based on key words in the item description. The item description would read something like Electricity HR or Fees Marketing. I have a list on the spreadsheet listing the various cost centres such as HR, Marketing etc. So the formula needs to scan the cell containing the item description and then search for one of the cost centres. It then populates a cell with HR etc. The list of cost centres can change so it would be preferable if the formula was range-based or was capable of adjusting to the various cost centres.
I hope this makes sense, I have downloaded the XL2BB add-in but I don't seem to have full functionality so I can't illustrate by way of an example.

 

[Peter_SSs]

Please use Mini Sheet. We cannot see the formulas in what you have posted, nor the column/row labels.

tigerzen.xlsm
	E
1
2
3	HR
4	Finance
5	Marketing
6
Sheet1

tigerzen.xlsm
	A	B	C	D	E
1	Item	Amount	Category
2	Electricity HR	20	HR		List
3	Finance	50	Finance		HR
4	Electricity Finance	35	Finance		Finance
5	Staff expenses HR	40	HR		Marketing
6	Water Marketing	60	Marketing
Sheet2

Cell Formulas
Range	Formula
E3:E5	E3	=OFFSET(Sheet1!$E$3,0,0,COUNTA(Sheet1!$E:$E),1)
C2:C6	C2	=FILTER(E$3#,ISNUMBER(SEARCH(" "&E$3# &" "," "&A2&" ")),"unknown")
Dynamic array formulas.

 

[tigerzen]

Sorry, this is the mini sheet version of what I had.

Excel range.xlsx
	A	B	C	D	E	F
1	Item	Amount	Category
2	Electricity HR	$20	#SPILL!		List
3	Finance	$50	#SPILL!		HR
4	Electricity Finance	$35	#SPILL!		Finance
5	Staff expenses HR	$40	#SPILL!		Marketing
6	Water Marketing	$60	Marketing
7			0
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=FILTER(list,ISNUMBER(SEARCH(list,A2)))

Named Ranges
Name	Refers To	Cells
list	=OFFSET(Sheet1!$E$3,0,0,COUNTA(Sheet1!$E:$E),1)	C2:C6

 

[tigerzen]

Please use Mini Sheet. We cannot see the formulas in what you have posted, nor the column/row labels.

tigerzen.xlsm
	E
1
2
3	HR
4	Finance
5	Marketing
6
Sheet1

tigerzen.xlsm
	A	B	C	D	E
1	Item	Amount	Category
2	Electricity HR	20	HR		List
3	Finance	50	Finance		HR
4	Electricity Finance	35	Finance		Finance
5	Staff expenses HR	40	HR		Marketing
6	Water Marketing	60	Marketing
Sheet2

Cell Formulas
Range	Formula
E3:E5	E3	=OFFSET(Sheet1!$E$3,0,0,COUNTA(Sheet1!$E:$E),1)
C2:C6	C2	=FILTER(E$3#,ISNUMBER(SEARCH(" "&E$3# &" "," "&A2&" ")),"unknown")
Dynamic array formulas.

Hi Peter,

Not sure if this is a solution but when I download this I get a #REF error in the formula cells. I've tried to use the spill operator on a brand new sheet and I still get a #REF error.

 

[Peter_SSs]

Not sure if this is a solution but when I download this I get a #REF error in the formula cells

Yes, I forgot about you using a dynamic named range and thought that you were using that OFFSET formula in the worksheet like column E of post 11.

Do you need to have/use that dynamic named range? Could you avoid setting that up and use something like this instead?

tigerzen.xlsm
	E
1
2
3	HR
4	Finance
5	Marketing
6
7
Sheet1

tigerzen.xlsm
	A	B	C
1	Item	Amount	Category
2	Electricity HR	20	HR
3	Finance	50	Finance
4	Electricity Finance	35	Finance
5	Staff expenses HR	40	HR
6	Water Marketing	60	Marketing
Sheet3

Cell Formulas
Range	Formula
C2:C6	C2	=LET(r,Sheet1!E$3:E$100,LST,FILTER(r,r<>""),FILTER(LST,ISNUMBER(SEARCH(" "&LST&" "," "&A2&" ")),"unknown"))

 

[tigerzen]

Thanks Peter, your last solution works fine and no I don't have to use that particular named range. What typically happens is that every month different categories of transactions appear and the dynamic range would be a way of capturing them and automatically updating the category field.

The last solution works well but will take some getting use to as I haven't used the LET function before, what does the LST segment represent in the formula?
Also, why does the category list have to go on another sheet (Sheet 1 ), can't it sit on the main sheet?

 

[Peter_SSs]

Also, why does the category list have to go on another sheet (Sheet 1 )

It doesn't, but because you were using XL2BB with Table Only and not Mini Sheet we could not see the sheet names or row/column labels so had to guess all those things.
When you said that your dynamic named range was set by "=OFFSET(Sheet1!$E$3,0,0,COUNTA(Sheet1!$E:$E),1)" I made the assumption/guess that it was on a different sheet.

what does the LST segment represent

It represents the "list" in your dynamic named range. If you don't have that dynamic named range anymore (or now get rid of it) we could use the name "list" in my formula too. I just did not want to risk using the same name in my formula as an already existing named range.

will take some getting use to as I haven't used the LET function before

The formula can be written without LET, but using LET makes it shorter and more efficient.

Anyway, if everything is on a single sheet, with the list of categories in column E (but there could be more/less categories each month** and there could be blank cells in the list) and the dynamic named range is removed from the Name Manager, could this work for you?

I have allowed for up to 100 categories in column E but you could make that more or less.

tigerzen.xlsm
	A	B	C	D	E
1	Item	Amount	Category
2	Electricity HR	20	HR		List
3	Finance	50	Finance		HR
4	Electricity Finance	35	Finance		Finance
5	Staff expenses HR	40	HR
6	Water Marketing	60	Marketing
7
8					Marketing
9
Sheet4

Cell Formulas
Range	Formula
C2:C6	C2	=LET(r,E$3:E$102,list,FILTER(r,r<>""),FILTER(list,ISNUMBER(SEARCH(" "&list&" "," "&A2&" ")),"unknown"))

** Regarding the list in column E

What typically happens is that every month different categories of transactions appear

Does that mean that you are altering the column E list every month? If so, why not just make column E a fixed list of all possible categories & only amend it if a new category is introduced some time in the future?

 

[tigerzen]

It doesn't, but because you were using XL2BB with Table Only and not Mini Sheet we could not see the sheet names or row/column labels so had to guess all those things.
When you said that your dynamic named range was set by "=OFFSET(Sheet1!$E$3,0,0,COUNTA(Sheet1!$E:$E),1)" I made the assumption/guess that it was on a different sheet.


It represents the "list" in your dynamic named range. If you don't have that dynamic named range anymore (or now get rid of it) we could use the name "list" in my formula too. I just did not want to risk using the same name in my formula as an already existing named range.


The formula can be written without LET, but using LET makes it shorter and more efficient.

Anyway, if everything is on a single sheet, with the list of categories in column E (but there could be more/less categories each month** and there could be blank cells in the list) and the dynamic named range is removed from the Name Manager, could this work for you?

I have allowed for up to 100 categories in column E but you could make that more or less.

tigerzen.xlsm
	A	B	C	D	E
1	Item	Amount	Category
2	Electricity HR	20	HR		List
3	Finance	50	Finance		HR
4	Electricity Finance	35	Finance		Finance
5	Staff expenses HR	40	HR
6	Water Marketing	60	Marketing
7
8					Marketing
9
Sheet4

Cell Formulas
Range	Formula
C2:C6	C2	=LET(r,E$3:E$102,list,FILTER(r,r<>""),FILTER(list,ISNUMBER(SEARCH(" "&list&" "," "&A2&" ")),"unknown"))

** Regarding the list in column E

Does that mean that you are altering the column E list every month? If so, why not just make column E a fixed list of all possible categories & only amend it if a new category is introduced some time in the future?

Thanks Peter for the clarifications, it really helps to gain an understanding of how this works. I'll play around with the options and test them.
In regards to the column E list, it is mainly fixed but there are always new items arising each period, from what I can see sometimes it's 2 or 3, at other times it's at least 5.

 

[Peter_SSs]

there are always new items arising each period, from what I can see sometimes it's 2 or 3, at other times it's at least 5.

As I said, I have allowed for 100 entries in the range E3:E102 so anything that you add anywhere in that range should work. If you have more than 100, just change the E$3:E$102 range in the formula to something bigger.