1

At the moment I am running over a dictionary so that I can fill missing values in a table based on the stored keys within it. The problem is that my table has more values (keys) than the ones stored in the dictionary. In some cases the information in my table is similar to the a certain key in the Dictionary and i was wondering if it is possible to use a wildcard to retrieve the items. Example below:

Example records in dictionary:

  • Mercedes (key) car (item)
  • Kawasaki (key) motor (item)

Lets assume my table has the following entry:

  • Merc (key) but is missing the item, which should be "car" in this case.

Is it possible to make this happen? I am sorry if the example is bad, but I hope any of you can help me.

I found several articles on the topic:

Thank you in advance!

VBABegginer
  • 91
  • 1
  • 1
  • 7

2 Answers2

0

You can write a function which returns a boolean.

Public Function KeywordExists(ByVal Keyword As String) As Boolean
    Dim k As Variant
    For Each k In objDict.Keys  'Your dictionary object
        If Keyword Like "*" & k & "*" Then
            KeywordExists = True
            Exit For
        End If
    Next k
End Function

To call it:

If KeywordExists("Foo") then '...

Edit:

To return the key, simply change the return type to string.

Public Function StringKeyword(ByVal Keyword As String) As String
    Dim k As Variant
    For Each k In objDict.Keys  'Your dictionary object
        If Keyword Like "*" & k & "*" Then
            StringKeyword = K
            Exit For
        End If
    Next k
End Function

To call it:

Dim key As String
    key = StringKeyword("Foo")
Kostas K.
  • 8,293
  • 2
  • 22
  • 28
  • Hey @Kostas K., but this will not return the value of the key from the dictionary. It will just tell me whether or not there is something similar there – VBABegginer Nov 16 '17 at 13:43
  • See edit. Simply change the return type from boolean to string and return the key. – Kostas K. Nov 16 '17 at 14:03
  • Amazing! I will mark it as answered, but can I ask for something extra? In case I am looping through a table with a for loop and I need to use the for each within it, is there a way to improve the performance ? Because a loop in the loop makes it slow. Thank you for answering this one! @Kostas K. – VBABegginer Nov 16 '17 at 14:20
  • How many records does the outer loop contains? I'm afraid there isn't any other way using `Like`. Dictionary has an `.Exists` method but it only looks for whole keys (which works like the above loop but faster). – Kostas K. Nov 16 '17 at 15:23
  • Yea, I know about Exists, but sadly it doesnt help me in this case. At the moment The main loop has 12 000 records and the for each is going inside of it. But I was hoping that I can use it on more data, around 100 000 rows. In case you dont have any idea it is still okay. Thank you for helping me out with this one! @Kostas K. – VBABegginer Nov 16 '17 at 15:34
0

Left field answer: Don't use a Dictionary, and don't use like because the approach is just too slow and doesn't scale well. Try one of these instead:

  1. Use the Advanced Filter, which accepts wildcards. You'll have to dump the data into Excel. But then I assume the data is already in the grid somewhere. But the Advanced Filter is fast, and I actually use it behind the scenes in a PivotTable Filtering app I've built that lets users quicky filter PivotTables on multiple wildcards and complicated exclusions.
  2. Use a Binary Search algorithm on sorted data. I often find it easiest to do this in Excel by dumping the two lists to Excel, sorting them there, and then using the Double VLOOKUP trick I talk about here: http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/

Charles Williams has a great post here that might be useful, and should be read regardless: https://fastexcel.wordpress.com/2012/07/10/comparing-two-lists-vba-udf-shootout-between-linear-search-binary-search-collection-and-dictionary/

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27