How do I populate a column with key terms that are based on matches to several arrays?

I need to populate column H with several key terms (e.g. slides, guide, poster). I need the macro to look at column B for a match to a different set of terms, and if there is a match, populate it with the corresponding key term.

For example: column B has these values: Cell B2 = "product guide 2.0", B3 = "another-guide for product", B4 = "product-slides", B5 = "more_product_slides". The macro should look for matches to the terms "another-guide", "product guide" and populate the row in col H with "guide". It should also find matches to "product-slides" and "product_slides" and populate col H with "slides" in the row where there was a match.

The macro needs to be scalable to add more key terms and be able to handle arrays of varying sizes: 1-48 terms.

Here is an example of what planned to use. Note: it'd be great if i didn't have to update the range in the For loop and the variable in FindWhat().

ub SFDCCategorizeAssetType()


    Dim FindWhat, rngCell As Range, i As Integer
    FindWhat = Array( "booklet", "compliance-guide", "enrichment-guide", "field_guide", "fs_guide", "installation guide", "installation_guide", "installationguide", "installation-guide", "installguide", "install-guide", _
    "library-prep-guide", "operations_manual", "pm_guide", "pmguide", "procedure", "protocol", "reference guide", "reference-guide", "service guide", "service_guide", "serviceguide", "service-guide", "site-prep", _
    "software-guide", "software-guide", _
    "system_manual", "system-guide", "technical-guide", "-ug-", "user guide", "user_guide", "userguide", "user-guide", "analysis_guide", "app-guide", "compliance guide", "conversion_guide", _
    "customization_guide", "enrichment-guide", "maintenance_guide", _
    "preventive maintenance guide", "ref-guide", "safety-and-compliance-guide", "upgrade_guide")
    For i = 0 To 65
        For Each rngCell In Range("B2", Range("B" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(i)) <> 0 Then
                rngCell.Offset(0, 6) = "guide"
                  End If
        Next rngCell
    Next i

 FindWhat = Array("presentation", ".pptx")
    For i = 0 To 2
        For Each rngCell In Range("B2", Range("B" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(i)) <> 0 Then
                rngCell.Offset(0, 6) = slides
                  End If
        Next rngCell
    Next i
     
End Sub
calyx_terenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
The attached works for me.
28699071.xlsm
0
Russ SuterCommented:
From what I can see of your question you could accomplish what you need with a VLOOKUP formula and avoid macros altogether.
0
calyx_terenAuthor Commented:
Hi Martin, it works but because bFound is a boolean, I cant scale the macro. I need an if-elseif statement. I'm going to add several more arrays and thus need an elseif somewhere in there.

Russ, if there was a 1:1 relationship between a term and a key term, I could do that, but i need several terms to map to one key term.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Martin LissOlder than dirtCommented:
That's not true. The boolean is there only to prevent unnecessary looping if a value has already been found. Note that line 22 has no If statement so bFound is reset each time through the "For i" loop.

Here's code set up to work with 4 arrays
Sub SFDCCategorizeGuide()
    Dim FindWhat, rngCell As Range, i As Integer
    Dim FindWhat2
    Dim FindWhat3
    Dim FindWhat4
    Dim bFound As Boolean
    
    FindWhat = Array("booklet", "compliance-guide", "enrichment-guide", "field_guide", "fs_guide", "installation guide", "installation_guide", "installationguide", "installation-guide", "installguide", "install-guide", _
    "library-prep-guide", "operations_manual", "pm_guide", "pmguide", "procedure", "protocol", "reference guide", "reference-guide", "service guide", "service_guide", "serviceguide", "service-guide", "site-prep", _
    "software-guide", "software-guide", _
    "system_manual", "system-guide", "technical-guide", "troubleshooting", "-ug-", "user guide", "user_guide", "userguide", "user-guide", "analysis_guide", "app-guide", "compliance guide", "conversion_guide", _
    "customization_guide", "enrichment-guide", "maintenance_guide", _
    "preventive maintenance guide", "ref-guide", "safety-and-compliance-guide", "sampleprep_guide", "siteprepguide", "upgrade_guide")
    
    FindWhat2 = Array("this", "is", "a", "test")
    
    FindWhat3 = Array("some", "more")
    
    FindWhat4 = Array("visual", "basic")
    
    For i = 2 To Range("B1048576").End(xlUp).Row
        bFound = LookUp(FindWhat, "guide", Cells(i, 2))
        If Not bFound Then
            bFound = LookUp(FindWhat2, "blah", Cells(i, 2))
        End If
        If Not bFound Then
            bFound = LookUp(FindWhat3, "From FindWhat3", Cells(i, 2))
        End If
        If Not bFound Then
            bFound = LookUp(FindWhat4, "From FindWhat4", Cells(i, 2))
        End If
    Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
calyx_terenAuthor Commented:
My mistake. Thanks for the example.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
0
calyx_terenAuthor Commented:
Martin, the script runs without errors, but it doesn't populate column h if the terms aren't by themselves. For example, the macro identifies "compliance-guide" and populates the corresponding cell in col H with "guide", but it doesn't identify "product-compliance-guide".  So I'm really looking for a match to a string within a string.
0
Martin LissOlder than dirtCommented:
Change the following line in the LookUp function from

 If UCase(cel.Value) = UCase(Arr(lngEntry)) Then

to

If InStr(1, UCase(cel.Value), UCase(Arr(lngEntry))) > 0 Then
0
calyx_terenAuthor Commented:
Like magic! Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.