Assign a number to items from a keyword in the name

**samuelabyrd** · 01-24-2013, 10:52 AM

I have 30,000 rows of data about products purchased. They have descriptions, for example, "yellow latex exam glove" or "trash can liner 24x48" in column K. I'd like to assign a number (1-9) to each item, by identifying a single keyword in each description. The keyword ties back to a legend, indicating which number (1-9), to place on the same row, but in column P.
Thank you.

Legend
Keyword Code
Glove 1
Liner 2
Towel 3
Paper 4
Disenfect 5
Tissue 6
Bag 7
Cleanser 8
Pad 9

**Cofad** · 01-24-2013, 11:08 AM

Is the legend the same for each row of data?

**samuelabyrd** · 01-24-2013, 11:51 AM

Yes, the legend is the same for all rows of data.

So regardless of the row and content of the source data that I'd like to code with a number (1-9), it will reference the same legend.

Thank you.

**Cofad** · 01-24-2013, 12:05 PM

Try this user defined function. Paste it into a module in the vba editor and then call it on the worksheet. It needs the cell with item description as the function argument:

Public Function AssignNumber(target As Range) As String

    Dim legend(1 To 9) As String
    Dim i As Integer
    
    legend(1) = "Glove"
    legend(2) = "Liner"
    legend(3) = "Towel"
    legend(4) = "Paper"
    legend(5) = "Disinfect"
    legend(6) = "Tissue"
    legend(7) = "Bag"
    legend(8) = "Cleanser"
    legend(9) = "Pad"
    
    AssignNumber = "No reference found!"
    
    For i = 1 To UBound(legend)

        If InStr(target.Value, legend(i)) Then
        
            AssignNumber = i
        
        End If
    
    Next

End Function

**K m** · 01-24-2013, 12:20 PM

Cofad:
Is your solution, case neutral?

**Cofad** · 01-24-2013, 12:26 PM

K m,

Nope sure isn't. This one is case neutral though:

Public Function AssignNumber(target As Range) As String

    Dim legend(1 To 9) As String
    Dim i As Integer
    
    legend(1) = "Glove"
    legend(2) = "Liner"
    legend(3) = "Towel"
    legend(4) = "Paper"
    legend(5) = "Disinfect"
    legend(6) = "Tissue"
    legend(7) = "Bag"
    legend(8) = "Cleanser"
    legend(9) = "Pad"
    
    AssignNumber = "No reference found!"
    
    For i = 1 To UBound(legend)

        If InStr(UCase(target.Value), UCase(legend(i))) Then
        
            AssignNumber = i
        
        End If
    
    Next

End Function

Thanks for spotting that!

**samuelabyrd** · 01-24-2013, 12:45 PM

Thank you for the help. I'm not sure I'm clear on what portions of the code I need to edit. Please advise.

**Cofad** · 01-24-2013, 12:50 PM

Originally Posted by samuelabyrd

Thank you for the help. I'm not sure I'm clear on what portions of the code I need to edit. Please advise.

Are you asking where to place the code in the workbook?

1. Open the VBA editor. Not 100% sure how to do that in excel 2003, but try using alt+F11; otherwise, google how to open it for your version.
2. Insert a new module. Google for instructions for this as well.
3. Paste the above code into the module.
4. Type in the formula "= AssignNumber(CellNumberGoesHere)" into the cell and it should assign the id number.

Edit: fixed typo in line #4 as per following reply

**samuelabyrd** · 01-24-2013, 01:16 PM

4. AssignName should read AssignNumber.

I got it! Good to go. Thank you for all your help!

**samuelabyrd** · 01-24-2013, 01:23 PM

1 follow-up question. How do I alter the code so it will function if the legend contains elements that are 2 digits? Expanding from 1-9 to 1-99?

**samuelabyrd** · 01-24-2013, 01:36 PM

I figured it out. Change "Dim i" to "Dim ii"

**Cofad** · 01-24-2013, 02:07 PM

No, you would have to resize the legend array and intialize it to the proper values

    
    Dim legend(1 To 99) As String
    Dim i As Integer
    
    legend(1) = "Glove"
    legend(2) = "Liner"
    legend(3) = "Towel"
    legend(4) = "Paper"
    legend(5) = "Disinfect"
    legend(6) = "Tissue"
    legend(7) = "Bag"
    legend(8) = "Cleanser"
    legend(9) = "Pad"
    .....
    .....
    legend(99) = "Whatever you want here"
    
    AssignNumber = "No reference found!"
    
    For i = 1 To UBound(legend)

        If InStr(UCase(target.Value), UCase(legend(i))) Then
        
            AssignNumber = i
        
        End If
    
    Next

End Function

Assign a number to items from a keyword in the name

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Re: Assign a number to items from a keyword in the name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions