CODEWITHSUNDEEP
vbaexcellookup
LostVBAGuy
LostVBAGuy

Reputation: 23

VBA trying to match a full string to a partial string to look up the corresponding value

So I know that based on the title most of you are probably sitting there wondering what I am talking about.

Essentially, I have a list of about 30,000 items where there is an office code for over 300 offices and I need to break them down by region. Unfortunately, I have run into an issue with actually looking them up.

In order to give you an idea of what I am talking about, here is an example that I mocked up. Company name has obviously been changed and all of the codes are randomly generated. https://i.sstatic.net/r7m8k.png

The issue that I have run into has to do with the formatting of these names.

All offices are identified by the company name followed by the office code and then a string of letters/numbers or even sentences that identify other things.

Unfortunately, these codes are highly inconsistent. I toyed around with vlookup and the search functions to return to specific integers but due to how the data is arranged, the results were not reliable. Many of the office codes are 3 characters long while some are 6 and some are even separated by dashes (which unfortunately made it impossible for me to look up by dashes using a combination of search, left, and vlookup).

As a result, I am trying to figure out how to make a function in VBA that will look at the data and find the best match amongst the list of offices.

I was really hoping that someone here could give me an idea. I tried using fuzzy lookup but unfortunately it was not returning anything that I was really able to use.

Basically, I am trying to figure something out that will search each line against the list of available offices and select the best fit based on the first series of characters in order.

So IKEA-OEE-ZOPJSK would not be matched with IKEA-ZOP-OEE because the entry it was looking up was not IKEA-OEE.

Thanks for any and all advice!

Upvotes: 2

Views: 1037

Answers (2)

Alex Meehan
Alex Meehan

Reputation: 1

I think I understand your issue. And something like the below function should work, I've set it out to work like a vlookup, but it will evaluate the table array to see if it's the search value is found earliest in the lookup value and return whichever is.

It also works on partial strings, so using your above example, IKEA-ZOP-OEE, with the table array OEE, ZOP. ZOP would be returned as it's earliest in the string:

Public Function fxFirstReturn(lookup_value As Range, table_array As Range, column_index_num As Long) As String
Dim i As Long, j(0 To 2) As Long

For i = 1 To table_array.Rows.Count
  j(2) = InStr(1, table_array.Cells(i, 1), lookup_value)
  If (j(2) < j(0) Or j(0) = 0) And j(2) > 0 Then
    j(0) = j(2)
    j(1) = i
  End If
Next

If j(1) = 0 Then
  fxFirstReturn = "Error"
Else
  fxFirstReturn = table_array.Cells(j(1), column_index_num)
End If

End Function

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

So assume you data starts in A2, since you did not include column or row markers. This formula would go into B2.

Your lookup table is in C2:D9.

=INDEX(D:D,AGGREGATE(15,6,ROW($C$2:$C$9)/(ISNUMBER(SEARCH($C$2:$C$9,A2))),1))

It will look up the whole, so IKEA-OEE-ZOPJSK will match to IKEA-OEE and not IKEA-ZOP-OEE

Upvotes: 1

Related Questions