[VBA] Compare list of keywords (single or multiple) against a list of poorly-inputted options

[RockandGrohl]

Hi everyone.

This is an infamous problem at my work where people just don't give a F about making sure data is input correctly.


We have options that disclose the ticket that people bought on a show or attraction in London, and there are any number of ways they can be input. For example:

King Tut - Senior Citizen 55yrs + admission
Tut Adult Admission
Adult Tutankhamun Entry Ticket
Tut Senior Citizen 55yrs + admission
Tut Senior Citizen 55yrs + admission
King Tut Senior Citizen 55yrs + Admission
Tut Senior Citizen 55yrs + admission
Tut Senior Citizen 55yrs + admission
King Tut - Senior Citizen 55yrs + admission
King Tut - Adult
King Tut - Senior Citizen 55yrs + Admission
King Tut - Adult Admission

As you can see it's many, many different ways to say this is a ticket for a product which is "Tutankhamun, Treasures of the Golden Pharoah"

In worksheet F, I have a list of the options in column K, and I want the product name placed in column L.

In worksheet Tr, I have a list of the products we sell in column B and a list of the keywords in column C. For example:

London & King Tutankhamun Exhibition Tut, Tutankhamun

Most of the time, the keyword will just be one single word, but where there's multiple it's because I want to catch out where people are using shorthand names etc.

So far, this is what my code does:

Sub Create_Click()

Dim WB As Workbook
Dim Lastrow, LastrowTR As Long
Dim Rng, cl As Range


Application.ScreenUpdating = False

Set F = Worksheets("Front")
Set Tr = Worksheets("Translation")

F.Range("A3").Activate
Tr.Activate
Range("C2").Activate

Do Until Cells(ActiveCell.Row, "A").Value = ""
Tr.Activate
Key = Cells(ActiveCell.Row, "C").Value
Tnam = Cells(ActiveCell.Row, "B").Value
F.Activate
    Do Until Cells(ActiveCell.Row, "A").Value = ""
    If UCase(Cells(ActiveCell.Row, "K").Value) Like UCase("*" & Key & "*") Then
    Cells(ActiveCell.Row, "L").Value = Tnam
    End If
    ActiveCell.Offset(1, 0).Activate
    Loop
    Range("A3").Activate
Tr.Activate
ActiveCell.Offset(1, 0).Activate
Loop

Application.ScreenUpdating = True

End Sub

So I'm holding each of the keywords as the key, and then scanning down the list and seeing if anything matches.

Problem is when I have multiple keywords, I need to get these split out somehow.

Does anyone know how I could search for "Tut", then "Tutankhamun", or only one if there is one keyword, or three, etc?

Thank you!

 

[DataBlake]

this looks like a job for dictionaries
Key and a value
so lets separate your needs you first want to identify the attraction, then the age of admission, then the discount if any right?
you can create a dictionary of keywords for each need

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com

So an example would be

dictA.add "Tut", "King Tut"
dictA.add "Tutankhamun", "King Tut"
dictA.add "King Toot", "King Tut"
dictA.add "Tomb Raider", "Lara Croft: Tomb Raider"
dictA.add "Lara Croft", "Lara Croft: Tomb Raider"
dictA.add "LCTR", "Lara Croft: Tomb Raider"

what this will allow you to do is create a means to unify multiple ways of saying one thing as well as separating what the needs are.
in most cases i just keep a sheet with keywords and their meaning and run a code to upload the sheet to dictionaries using columns a and b like below

Dim ary1 as variant
Dim i as long
Dim dictA as object

    ary1 = ActiveSheet.Range("A1").CurrentRegion.Value2
    Set dictA = CreateObject("scripting.dictionary")

    For i = 2 To UBound(ary1)
        If Not dictA.exists(ary1(i, 1)) Then dictA.Add ary1(i, 1), ary1(i, 2)
    Next i

once you have your (3) dictionaries you can accomplish virtually anything using the key(word) and its value but i'm unsure of the end goal so thats all i can help with.
but dictionaries will generally be looped through from top to bottom so keep what you want to search for first at the top of the dictionary

 

[RockandGrohl]

this looks like a job for dictionaries
Key and a value
so lets separate your needs you first want to identify the attraction, then the age of admission, then the discount if any right?
you can create a dictionary of keywords for each need

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com

So an example would be

dictA.add "Tut", "King Tut"
dictA.add "Tutankhamun", "King Tut"
dictA.add "King Toot", "King Tut"
dictA.add "Tomb Raider", "Lara Croft: Tomb Raider"
dictA.add "Lara Croft", "Lara Croft: Tomb Raider"
dictA.add "LCTR", "Lara Croft: Tomb Raider"

what this will allow you to do is create a means to unify multiple ways of saying one thing as well as separating what the needs are.
in most cases i just keep a sheet with keywords and their meaning and run a code to upload the sheet to dictionaries using columns a and b like below

Dim ary1 as variant
Dim i as long
Dim dictA as object

    ary1 = ActiveSheet.Range("A1").CurrentRegion.Value2
    Set dictA = CreateObject("scripting.dictionary")

    For i = 2 To UBound(ary1)
        If Not dictA.exists(ary1(i, 1)) Then dictA.Add ary1(i, 1), ary1(i, 2)
    Next i

once you have your (3) dictionaries you can accomplish virtually anything using the key(word) and its value but i'm unsure of the end goal so thats all i can help with.
but dictionaries will generally be looped through from top to bottom so keep what you want to search for first at the top of the dictionary

This does look like one way to do it, but is there a way to do it without adding the lines to the dictionary withoutVBA? I want to deliver this to someone where the "dictionary" definitions are added in the column next to the expected output line. Cheers

 

[DataBlake]

I want to deliver this to someone where the "dictionary" definitions are added in the column next to the expected output line. Cheers

yes that is what the second vba script does
it takes column B as the Value (or as you put it the definition: King Tut, Lara Croft: Tomb Raider) and assigns it the Key (Tut, Tutankhamun) of the active sheet
if you give me a workbook to use with examples i could definitely help you build these dictionaries.
i would also need the expected result of the examples.