filling cell with a specific text if another cell contains any of the specific keywords

[Excel Learner007]

Hi Everyone,
We have a huge sheet (almost 40k Rows). Now, there is a column "Short Description" and based on the keywords in that column, we need to categorize it as either CPU, Memory, Disk, Database or Storage. Below is the categorization. Would be great if someone can help.

Other	CPU	Memory	Disk	Database	Storage
aix	core	paging	mount	mssql	Storage
bios	wait	heap	data	oracle	Space
cluster	Cache	page	filesystem	sql
esx	delay	ram	drive	sybase
rate	handler	swap	nfs	sybdump
linux	performance	temp	read
nodes	processor	threshold	storage
slow	queue	timeout	write
unix	stats		vol
windows
vmware
vsphere
vmtools

 

[Joe4]

I would make your list above a two column lookup reference table, i.e. Value and Category.
Then you can use a VLOOKUP function to look up the category for each record in your main table.
See here: MS Excel: How to use the VLOOKUP Function (WS)

 

[Excel Learner007]

I would make your list above a two column lookup reference table, i.e. Value and Category.
Then you can use a VLOOKUP function to look up the category for each record in your main table.
See here: MS Excel: How to use the VLOOKUP Function (WS)

I'm trying it using IF , below is the formula.. If I do it only for 1 item, say CPU, it works, but won't if I will add all, maybe I'm not doing it right, can you correct me?

=IF(OR(ISNUMBER(SEARCH("core",I2)),ISNUMBER(SEARCH("wait",I2)),ISNUMBER(SEARCH(“Cache”,I2)), ISNUMBER(SEARCH(“delay”,I2)), ISNUMBER(SEARCH(“handler”,I2)), ISNUMBER(SEARCH(“performance”,I2)), ISNUMBER(SEARCH(“processor”,I2)), ISNUMBER(SEARCH(“queue”,I2)), ISNUMBER(SEARCH(“stats”,I2))),"CPU",(OR(ISNUMBER(SEARCH("paging",I2)),ISNUMBER(SEARCH("heap",I2)),ISNUMBER(SEARCH(“page”,I2)), ISNUMBER(SEARCH(“ram”,I2)), ISNUMBER(SEARCH(“swap”,I2)), ISNUMBER(SEARCH(“temp”,I2)), ISNUMBER(SEARCH(“threshold”,I2)), ISNUMBER(SEARCH(“timeout”,I2)),"MEMORY",(OR(ISNUMBER(SEARCH("mount",I2)),ISNUMBER(SEARCH("data",I2)),ISNUMBER(SEARCH(“filesystem”,I2)), ISNUMBER(SEARCH(“drive”,I2)), ISNUMBER(SEARCH(“nfs”,I2)), ISNUMBER(SEARCH(“read”,I2)), ISNUMBER(SEARCH(“storage”,I2)), ISNUMBER(SEARCH(“write”,I2)), ISNUMBER(SEARCH(“volume”,I2)),"DISK",(OR(ISNUMBER(SEARCH("mssql",I2)),ISNUMBER(SEARCH("oracle",I2)),ISNUMBER(SEARCH(“sql”,I2)), ISNUMBER(SEARCH(“sybase”,I2)), ISNUMBER(SEARCH(“sybdump”,I2)),”DATABASE”,(OR ISNUMBER(SEARCH(“Storage”,I2)), ISNUMBER(SEARCH(“space”,I2)), “STORAGE", “NA”))

 

[Joe4]

I would never attempt to use a formula like that. That is a behemoth and a nightmare, and will be a pain to maintain if you ever have additions/removals from your data lists.

Like I said, I would recommend creating a two column lookup table that starts like this:

And then use a LOOKUP value to find the Category to return, i.e.

=VLOOKUP(I2,A:B,2,0)

 

[Excel Learner007]

I would never attempt to use a formula like that. That is a behemoth and a nightmare, and will be a pain to maintain if you ever have additions/removals from your data lists.

Like I said, I would recommend creating a two column lookup table that starts like this:
View attachment 59635

And then use a LOOKUP value to find the Category to return, i.e.

=VLOOKUP(I2,A:B,2,0)

this didn't work, as Vlookup will search for exact match, cell I2 can contain more than one word, for example " Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03gmt_snppum04"

 

[Joe4]

this didn't work, as Vlookup will search for exact match, cell I2 can contain more than one word, for example " Backup failed for below devices SYD01 :Daily_Backup_App_scsydprpsnt03gmt_snppum04"

OK, since you did not provide any sample data in your original post, that criteria was not clear.

Not sure the best way to go about that. If it were me, I might look at a VBA solution, but still having that two column reference table to look for.

And does the possibility exist that TWO or more of those words might appear in a single entry?

 

[Excel Learner007]

OK, since you did not provide any sample data in your original post, that criteria was not clear.

Not sure the best way to go about that. If it were me, I might look at a VBA solution, but still having that two column reference table to look for.

And does the possibility exist that TWO or more of those words might appear in a single entry?

Yes, there is a possibility that 2 or more words might appear in the cell

 

[Joe4]

Would you consider a VBA solution?

 

[Excel Learner007]

Would you consider a VBA solution?

I can, would it work if I have to extract new data every week for different regions? the format of the data would remain the same.

Below are the columns, the last 2 in bold is what I have to insert, Month should fetch the month name from date column and Category should fetch the Category from the grid based on the keywords in Short Description column, if no matching keyword is found it should say "Not Found"

Thanks again for a quick response and help!!

Ticket Number

Ticket Status

Assigned To

Account Name

Date

Ticket Priority

Short Description

Assignment Group

Month

Category

 

[Joe4]

I assume that already know how to return the Month from the Date (you could simply just return the whole date and format the columns to "mmmm" or you could use the TEXT function).
Can you provide more details where everything exists?
Specifically,
- What is the sheet name that this data will appear on?
- What range exactly, is the header row you show in your previous post located?
- Is your list of categories to look up in the same file?
- What is the name of the sheet this category list appears on?