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

 

[jtakw]

Thanks, it works! For now, the level of details we are looking for can be solved using this solution. Much Appreciated

You're welcome.

 

[Excel Learner007]

You are welcome.

It may be a little while, as it may take some time to build it (and I got some things going on at work too).

Sure, please take your time. It's a pleasure talking to you

 

[Joe4]

OK, so I created a User Defined Function in VBA to use that looks like this:

Function FindKeywords(str As String, keyRng As Range) As String
'   str = string you want to look up
'   keyRng = the left-most column of the keyword range

    Dim cell As Range
    Dim cat As String
    Dim lastCat As String
    
'   Loop through all values in keyword range
    For Each cell In keyRng
'       Check to see if keyword is found in string
        If InStr(str, cell.Value) > 0 Then
            If cell.Offset(0, 1) <> lastCat Then
                cat = cat & cell.Offset(0, 1) & ", "
                lastCat = cell.Offset(0, 1)
            End If
        End If
    Next cell
            
'   Check to see if any category
    If cat = "" Then
        FindKeywords = "NA"
    Else
        FindKeywords = Left(cat, Len(cat) - 2)
    End If
    
End Function

Then you would use it like any other Excel function.
So place this formula in cell J2 and copy down for all data rows:

=FindKeywords(G2,Keyword_List!A$2:A$47)

Note that for the Keyword range, you are only including the first column (the values you are looking for).

 

[DacEasy]

I tried solving your issue with a macro. It also updates if keywords are in multiple categories. You can update the keywords on the category sheet or update the code to pull from a different spreadsheet.

Sub forBuildingCategoryFromShortDescriptions()
    Dim mainLastrow As Long, catLastrow As Long, catWords As Range, i As Long, j As Long
    Sheets("Category").Select 'I placed the categories on a 2nd sheet.  Select the Category sheet'
    
    With ActiveSheet 'Find the number of rows on the Category Sheet.
        catLastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With
     
    Set catWords = Range("A1:F" & catLastrow) 'Creates a range for the categories.
        
    Sheets("MainSheet").Select 'Select the MainSheet
    With ActiveSheet
        mainLastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With
    
    For i = 2 To mainLastrow 'Loop through all the rows on the MainSheet sheet
        For j = 2 To catLastrow 'Loop through all the rows of the Category sheet
            
            'The below will set the case of the data to lowercase and check to see if column C(Short Description)
            'contains the keywords from Category Sheet.  If the keyword exist Column E is assigned the right value.
            'If no keyword is found, it leaves it blank. You can always add, remove, or reassign keywords on the Category sheet.

            If catWords.Columns(1).Rows(j).Value = "" Then
                GoTo part2
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(1).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Other"
            End If
            
part2: 'Since the columns lengths on the Category Sheet vary, I used goto to exit the if statement when blanks appears in the columns
            If catWords.Columns(2).Rows(j).Value = "" Then
                GoTo part3
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(2).Rows(j).Value)) > 0 Then
                    Range("E" & i).Value = Range("E" & i).Value & " CPU"
            End If
            
part3:
            If catWords.Columns(3).Rows(j).Value = "" Then
                GoTo part4
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(3).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Memory"
            End If
            
part4:
            If catWords.Columns(4).Rows(j).Value = "" Then
                GoTo part5
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(4).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Disk"
            End If            
part5:
            If catWords.Columns(5).Rows(j).Value = "" Then
                GoTo part6
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(5).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Database"
            End If            
part6:
            If catWords.Columns(6).Rows(j).Value = "" Then
                GoTo part7
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(6).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Storage"
            End If
part7:
        Next j
    Next i
End Sub

 

[Excel Learner007]

OK, so I created a User Defined Function in VBA to use that looks like this:

Function FindKeywords(str As String, keyRng As Range) As String
'   str = string you want to look up
'   keyRng = the left-most column of the keyword range

    Dim cell As Range
    Dim cat As String
    Dim lastCat As String
   
'   Loop through all values in keyword range
    For Each cell In keyRng
'       Check to see if keyword is found in string
        If InStr(str, cell.Value) > 0 Then
            If cell.Offset(0, 1) <> lastCat Then
                cat = cat & cell.Offset(0, 1) & ", "
                lastCat = cell.Offset(0, 1)
            End If
        End If
    Next cell
           
'   Check to see if any category
    If cat = "" Then
        FindKeywords = "NA"
    Else
        FindKeywords = Left(cat, Len(cat) - 2)
    End If
   
End Function

Then you would use it like any other Excel function.
So place this formula in cell J2 and copy down for all data rows:

=FindKeywords(G2,Keyword_List!A$2:A$47)

Note that for the Keyword range, you are only including the first column (the values you are looking for).

Hi Joe,

It's just putting NA for all the rows, I think we'll need to include the Category column as well for it to extract the category and put it in the cell.

 

[Joe4]

It's just putting NA for all the rows, I think we'll need to include the Category column as well for it to extract the category and put it in the cell.

No, we don't.

This is the part that looks for that value:

cell.Offset(0, 1)

As long as this lookup range is a two column range, where the value you are matching on is in the first column, and the category is in the second column, it will work (I actually tested it out on the same data you provided and it worked).

If it is not working, you probably applied the formula incorrectly. If you show me screen shots of your data, your lookup range, and the formula you tried, I can probably spot the error pretty quickly.
Note that you will need to lock down the lookup range in your formula, otherwise it will shift as you copy your formula down, i.e.

=FindKeywords(G2,Keyword_List!A$2:A$47)

The dollar signs in front of the row references lock those row numbers in so that the formula doesn't change them as you copy the formula down.

 

[Excel Learner007]

I tried solving your issue with a macro. It also updates if keywords are in multiple categories. You can update the keywords on the category sheet or update the code to pull from a different spreadsheet.

Sub forBuildingCategoryFromShortDescriptions()
    Dim mainLastrow As Long, catLastrow As Long, catWords As Range, i As Long, j As Long
    Sheets("Category").Select 'I placed the categories on a 2nd sheet.  Select the Category sheet'
   
    With ActiveSheet 'Find the number of rows on the Category Sheet.
        catLastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With
    
    Set catWords = Range("A1:F" & catLastrow) 'Creates a range for the categories.
       
    Sheets("MainSheet").Select 'Select the MainSheet
    With ActiveSheet
        mainLastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With
   
    For i = 2 To mainLastrow 'Loop through all the rows on the MainSheet sheet
        For j = 2 To catLastrow 'Loop through all the rows of the Category sheet
           
            'The below will set the case of the data to lowercase and check to see if column C(Short Description)
            'contains the keywords from Category Sheet.  If the keyword exist Column E is assigned the right value.
            'If no keyword is found, it leaves it blank. You can always add, remove, or reassign keywords on the Category sheet.

            If catWords.Columns(1).Rows(j).Value = "" Then
                GoTo part2
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(1).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Other"
            End If
           
part2: 'Since the columns lengths on the Category Sheet vary, I used goto to exit the if statement when blanks appears in the columns
            If catWords.Columns(2).Rows(j).Value = "" Then
                GoTo part3
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(2).Rows(j).Value)) > 0 Then
                    Range("E" & i).Value = Range("E" & i).Value & " CPU"
            End If
           
part3:
            If catWords.Columns(3).Rows(j).Value = "" Then
                GoTo part4
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(3).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Memory"
            End If
           
part4:
            If catWords.Columns(4).Rows(j).Value = "" Then
                GoTo part5
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(4).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Disk"
            End If           
part5:
            If catWords.Columns(5).Rows(j).Value = "" Then
                GoTo part6
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(5).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Database"
            End If           
part6:
            If catWords.Columns(6).Rows(j).Value = "" Then
                GoTo part7
            ElseIf InStr(1, (LCase$(Range("C" & i).Value)), LCase$(catWords.Columns(6).Rows(j).Value)) > 0 Then
                Range("E" & i).Value = Range("E" & i).Value & " Storage"
            End If
part7:
        Next j
    Next i
End Sub

Hi,

Thanks a ton for your help, it seems to be working, just need a small change, if no keyword is found, can it be filled with "N/A" instead of a blank?
Also, it should only fill 1 category, maybe, based on the 1st keyword found. Lastly, can you add a code to extract the month name from the date column and put it in a column next to Category? (Column K in my Sheet)

 

[Joe4]

Thanks a ton for your help, it seems to be working, just need a small change, if no keyword is found, can it be filled with "N/A" instead of a blank?

If you only want the first category, then I would recommend using jtakw's formula approach instead. It already does that, requires no VBA, and is probably more efficient than using VBA code loops.

Also, it should only fill 1 category, maybe, based on the 1st keyword found. Lastly, can you add a code to extract the month name from the date column and put it in a column next to Category? (Column K in my Sheet)

If your date is in column E, then you can just use this formula to return the Month name:

=TEXT(E2,"mmmm")

 

[Excel Learner007]

No, we don't.

This is the part that looks for that value:

cell.Offset(0, 1)

As long as this lookup range is a two column range, where the value you are matching on is in the first column, and the category is in the second column, it will work (I actually tested it out on the same data you provided and it worked).

If it is not working, you probably applied the formula incorrectly. If you show me screen shots of your data, your lookup range, and the formula you tried, I can probably spot the error pretty quickly.
Note that you will need to lock down the lookup range in your formula, otherwise it will shift as you copy your formula down, i.e.

=FindKeywords(G2,Keyword_List!A$2:A$47)

The dollar signs in front of the row references lock those row numbers in so that the formula doesn't change them as you copy the formula down.

 

[Excel Learner007]

Seems to be working now.. Thanks!
Can we modify it to just look for the 1st keyword and fill the column with only 1 Category?