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-gui
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
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-gui
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
From what I can see of your question you could accomplish what you need with a VLOOKUP formula and avoid macros altogether.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My mistake. Thanks for the example.
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
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
ASKER
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"
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
If UCase(cel.Value) = UCase(Arr(lngEntry)) Then
to
If InStr(1, UCase(cel.Value), UCase(Arr(lngEntry))) > 0 Then
ASKER
Like magic! Thanks.
28699071.xlsm