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

E

Excel Learner007

New Member
Joined
Feb 26, 2011
Messages
18
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.

OtherCPUMemoryDiskDatabaseStorage
aixcorepagingmountmssqlStorage
bioswaitheapdataoracleSpace
clusterCachepagefilesystemsql
esxdelayramdrivesybase
ratehandlerswapnfssybdump
linuxperformancetempread
nodesprocessorthresholdstorage
slowqueuetimeoutwrite
unixstatsvol
windows
vmware
vsphere
vmtools
 
Sort by date Sort by votes
Excel Learner007 said:
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?
Click to expand...
You already asked that a few minutes ago, and I already answered that in my previous reply (post 28).
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
VBA Code: 
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 And Range("E" & i).Value = "" Then
                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 And Range("E" & i).Value = "" Then
                    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 And Range("E" & i).Value = "" Then
                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 And Range("E" & i).Value = "" Then
                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 And Range("E" & i).Value = "" Then
                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 And Range("E" & i).Value = "" Then
                Range("E" & i).Value = " Storage"
            End If
part7:
        Next j
    Next i

    For i = 2 To mainLastrow 'Fills in blank E column cells with N/A
        If Range("E" & i).Value = "" Then
            Range("E" & i).Value = "N/A"
        End If
        
        Range("D" & i).Value = Format(Range("B" & i), "mmmm") 'Looks at Date and pulls the month
    Next i
    
    Columns("E:D").AutoFit
End Sub

The changes your requested have been included.
 
Upvote 0
DacEasy said:
VBA Code: 
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 And Range("E" & i).Value = "" Then
                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 And Range("E" & i).Value = "" Then
                    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 And Range("E" & i).Value = "" Then
                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 And Range("E" & i).Value = "" Then
                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 And Range("E" & i).Value = "" Then
                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 And Range("E" & i).Value = "" Then
                Range("E" & i).Value = " Storage"
            End If
part7:
        Next j
    Next i

    For i = 2 To mainLastrow 'Fills in blank E column cells with N/A
        If Range("E" & i).Value = "" Then
            Range("E" & i).Value = "N/A"
        End If
       
        Range("D" & i).Value = Format(Range("B" & i), "mmmm") 'Looks at Date and pulls the month
    Next i
   
    Columns("E:D").AutoFit
End Sub

The changes your requested have been included.
Click to expand...
 

Attachments

  • forBuildingCategoryFromShortDescriptions2.jpg
    forBuildingCategoryFromShortDescriptions2.jpg
    55.6 KB · Views: 4
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,216,115
Messages
6,128,915
Members
449,478
Latest member
Davenil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top