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.

 

[tigerzen]

Got the functionality now, what needs to happen now is that the Column headed Category would show HR in C2, C3 would list Finance etc.

Item	Amount	Category
Electricity HR	$20			List
Water Finance	$50			HR
Electricity Finance	$35			Finance
Staff expenses HR	$40			Marketing
Water Marketing	$60

 

[StuLux]

Book1
	A	B	C	D	E
1	Item	Amount	Category
2	Electricity HR	20	HR		List
3	Water 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
C2:C6	C2	=INDEX(Table3[List],SUMPRODUCT(ISNUMBER(SEARCH(Table3[List],A2))*ROW($1:$3)))

 

[Peter_SSs]

@StuLux
I don't know exactly what is possible with the OP's data but that formula would fail for an item like "Highrise Marketing"

@tigerzen
Welcome to the MrExcel board!

You could try this.

23 03 08.xlsm
	A		C	D	E
1	Item		Category
2	Electricity HR		HR		List
3	Water Finance		Finance		HR
4	Electricity Finance		Finance		Finance
5	Staff expenses HR		HR		Marketing
6	Water Marketing		Marketing
7	Highrise Marketing		Marketing
8	HR Marketing		HR, Marketing
9	Electricity Workshop		unknown
Category

Cell Formulas
Range	Formula
C2:C9	C2	=TEXTJOIN(", ",,FILTER(E$3:E$5,ISNUMBER(SEARCH(" "&E$3:E$5&" "," "&A2&" ")),"unknown"))

Note that if it is not possible to have multiple categories within a single item like row 8 in my sample then the formula could be simplified to

=FILTER(E$3:E$5,ISNUMBER(SEARCH(" "&E$3:E$5&" "," "&A2&" ")),"unknown")

 

[tigerzen]

Thanks so much for the replies Stu and Peter. Both work fine but Peter you are correct with the Highrise entry, very good pickup! Also, love the brevity of your functions, this makes it easier for me to recreate when needed. Still have to get my head around the Filter function but it appears to be a very powerful feature.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

Both work fine

Just so long as you are aware that one looks for "words" in the Item whereas the other looks for "strings" in the Item. So particularly any short "string" in the List column (like HR) could possibly turn up within a longer "word" in an Item description and therefore give you a false result or an error.

 

[tigerzen]

You're welcome. Glad we could help. Thanks for the follow-up.


Just so long as you are aware that one looks for "words" in the Item whereas the other looks for "strings" in the Item. So particularly any short "string" in the List column (like HR) could possibly turn up within a longer "word" in an Item description and therefore give you a false result or an error.

Thank you for pointing that out, very useful to know.

 

[tigerzen]

@StuLux
I don't know exactly what is possible with the OP's data but that formula would fail for an item like "Highrise Marketing"

@tigerzen
Welcome to the MrExcel board!

You could try this.

23 03 08.xlsm
	A		C	D	E
1	Item		Category
2	Electricity HR		HR		List
3	Water Finance		Finance		HR
4	Electricity Finance		Finance		Finance
5	Staff expenses HR		HR		Marketing
6	Water Marketing		Marketing
7	Highrise Marketing		Marketing
8	HR Marketing		HR, Marketing
9	Electricity Workshop		unknown
Category

Cell Formulas
Range	Formula
C2:C9	C2	=TEXTJOIN(", ",,FILTER(E$3:E$5,ISNUMBER(SEARCH(" "&E$3:E$5&" "," "&A2&" ")),"unknown"))

Note that if it is not possible to have multiple categories within a single item like row 8 in my sample then the formula could be simplified to

=FILTER(E$3:E$5,ISNUMBER(SEARCH(" "&E$3:E$5&" "," "&A2&" ")),"unknown")

I've been using the second formula but there is a slight problem if you use dynamic range names. The formula returns #SPILL because there will be blank cells in the range name. Is there a way to circumvent this besides using CTRL SHIFT ENTER? I am setting this up for an inexperienced EXCEL user and don't want to complicate things too much. Interestingly if you use the first formula with TEXTJOIN you do not get a SPILL error. I tend to use dynamic range names a bit so am wondering how we get around the blank cells in the range name.

 

[Peter_SSs]

Perhaps you could be a bit more specific and give some concrete (but dummy) examples of your data and expected results and details of your 'dynamic range name' & how it is defined?
It would also help if you could clarify whether it is possible that two (or more) values from your 'List' could occur in an item name - like my example in row 8 of post #4.
If that is possible, then clarification of exactly what result(s) you would want would also be helpful.

 

[tigerzen]

Perhaps you could be a bit more specific and give some concrete (but dummy) examples of your data and expected results and details of your 'dynamic range name' & how it is defined?
It would also help if you could clarify whether it is possible that two (or more) values from your 'List' could occur in an item name - like my example in row 8 of post #4.
If that is possible, then clarification of exactly what result(s) you would want would also be helpful.

Sure, no problems. Below is what I get, having been through the data it is most unlikely that 2 or more list values can occur simultaneously.
As can be seen from this example I receive a Spill error but the correct answer is produced if Ctrl+Shift+Enter is used on the formula.
List formula is =OFFSET(Sheet1!$E$3,0,0,COUNTA(Sheet1!$E:$E),1)

Item	Amount	Category
Electricity HR	$20	#SPILL!		List
Finance	$50	#SPILL!		HR
Electricity Finance	$35	#SPILL!		Finance
Staff expenses HR	$40	#SPILL!		Marketing
Water Marketing	$60	Marketing
		0