Loop Vlookup

[leeandoona]

Hi all. I'm stuck trying to get either VBA or excel to perform a Vlookup on a single column/range of data that contains more than one text instance and loop the vlookup on that cells text. What I essentially want to do is to Vlookup each cell's text entry word by word. So if the cell contains say 3 words "Hat Jumper Teeth" I want Vlookup to first look up the word 'Hat', then look up the word 'Jumper' and lastly the word 'Teeth' and when (if) if finds a match in another sheet, to return what that match is. Ideally when its found a match it will stop looking at the other words in that cell.

The purpose of the exercise is I'm trying to categorise a vast list of product data into a small set of sub categories based on product style. This way I can then group products into a range which will make my life a whole lot easier with managing my product catalogues!

Any ideas how this works? I tried a previously posted thread about the sunject but the code was slightly different and wanted to find blank entries. I also tried making the text to columns but this then gets very labor intensive as I'm dealing with many thousands of entries.

Thanks all!

 

[leeandoona]

Hello again,

If you are worried about performance, you might want to get rid of both the "Eric Help" function and the UDF. I could write a macro that you would call when you need it. It would read down your column and fill in the matches.

And you've come across one common issue: data that almost matches. If that's an issue to you beyond cleaning your data, I can point you to some links about fuzzy matching. I've never personally tried to build something.

Hi Eric. I ran both versions ("Eric Help" function and your cool UDF). Oddly I found the "Eric Help" method was less of a drag on performance. When I ran this over some test data that was MUCH larger, it really did start to cause a lag. I do have a whole bunch of other lookups on the test data, so I disabled these and re-ran but performance was still a bit wobbly. I'm running this type of data periodically so performance isn't going to be a BIG issue, however that said a macro might be a more robust solution.

I've got FuzzyLookup installed and I've used it with some success. I've found so far that sometimes though, it's better to use a simple FIND-REPLACE or SUBSTITUTE on a check column and then use that as most of the text entries that return #N/A or blanks turn out to be simple plurals "Cats" or have been hyphenated "Cats-Tails" and when I've used Fuzzy to write stuff it ended up being a little too vaigue and would often end up bringing back "Womens" for a fuzzy match on "mens" for example....kind of frustrating! It's great on mis-spellings though and fuzzy finds some time consuming common mistakes and even keystroke errors like "qwomens" or "qomens" which I thought was pretty impressive! Anyway I digress!! I think a macro would be cool. I recorded what the "Eric Help" looked like to try and fathom how to build write it properly....like I say though, I'm still learning!

Cheers

 

[Eric W]

Here's a macro that should do all the lookups in one shot. Follow the instructions in post #8 to install this on a Module in your sheet. Change the parts in red to match your sheet. Then go back to Excel, press Alt-F8 to open the macro selector, pick MyVlookup and click Run.

Public Sub MyVlookup()
Dim InputCol As String, OutputCol As String, MyDict As Object
Dim MyData As Variant, i As Long, Words As Variant, w As Variant

    Application.ScreenUpdating = False

    InputCol = "A1"
    OutputCol = "B1"
    MyData = Sheets("Sheet2").Range("F1:G10").Value
    
    Set MyDict = CreateObject("Scripting.Dictionary")
    On Error Resume Next
    For i = LBound(MyData) To UBound(MyData)
        MyDict.Add CStr(MyData(i, 1)), CStr(MyData(i, 2))
    Next i
    
    MyData = Range(InputCol & ":" & Left(InputCol, 1) & Cells(Rows.Count, Left(InputCol, 1)).End(xlUp).Row).Value
    For i = LBound(MyData) To UBound(MyData)
        Words = Split(MyData(i, 1), " ")
        MyData(i, 1) = "Not found"
        For Each w In Words
            If MyDict.exists(w) Then
                MyData(i, 1) = MyDict(w)
                Exit For
            End If
        Next w
    Next i
    
    Range(OutputCol & ":" & Left(OutputCol, 1) & Cells(Rows.Count, Left(InputCol, 1)).End(xlUp).Row).Value = MyData
    Application.ScreenUpdating = True
        
End Sub

Let me know how this works!

 

[leeandoona]

Hi Eric. This looks pretty neat and tidy! (unlike anything I end up splurging together!). Sorry for the late reply, I'll try this this afternoon and let you know how this works. Thanks a million for putting in such effort to help me its so very much appreciated!

Lee

 

[leeandoona]

Here's a macro that should do all the lookups in one shot. Follow the instructions in post #8 to install this on a Module in your sheet. Change the parts in red to match your sheet. Then go back to Excel, press Alt-F8 to open the macro selector, pick MyVlookup and click Run.

Public Sub MyVlookup()
Dim InputCol As String, OutputCol As String, MyDict As Object
Dim MyData As Variant, i As Long, Words As Variant, w As Variant

    Application.ScreenUpdating = False

    InputCol = "A1"
    OutputCol = "B1"
    MyData = Sheets("Sheet2").Range("F1:G10").Value
    
    Set MyDict = CreateObject("Scripting.Dictionary")
    On Error Resume Next
    For i = LBound(MyData) To UBound(MyData)
        MyDict.Add CStr(MyData(i, 1)), CStr(MyData(i, 2))
    Next i
    
    MyData = Range(InputCol & ":" & Left(InputCol, 1) & Cells(Rows.Count, Left(InputCol, 1)).End(xlUp).Row).Value
    For i = LBound(MyData) To UBound(MyData)
        Words = Split(MyData(i, 1), " ")
        MyData(i, 1) = "Not found"
        For Each w In Words
            If MyDict.exists(w) Then
                MyData(i, 1) = MyDict(w)
                Exit For
            End If
        Next w
    Next i
    
    Range(OutputCol & ":" & Left(OutputCol, 1) & Cells(Rows.Count, Left(InputCol, 1)).End(xlUp).Row).Value = MyData
    Application.ScreenUpdating = True
        
End Sub

Let me know how this works!

Eric, just an update. This worked very well. There was hardly any performance lag at all. In fact when I ran this over some test data of 25,000+ lines it worked in about 1-2 seconds. BRILLIANT! I compared this to my recorded and edited macro carrying out the Eric Help method and it ran in at about 10+ seconds on the same test data!! I'm impressed and ever so thankful for all your dvise and work on this, it's just fantastic. I'll post my 'megaformula' and resulting macro which uses this when I've got it ironed out. It's simply the doing the same function but is using SWITCH to check instances of IF/THEN before doing this cool VLookup Loop. It should be useful for anyone wanting to lookup a range of text values and perform lookups based on the data returned.

Thanks again! Just amazing!!!

 

[Eric W]

You make me blush!

I'm very glad it works for you!