VBA Fuzzy Lookup (Find) - Case Sensitive Issue

[hamidxa]

Hi,

I have a function that performs a "fuzzy" lookup/find against a set of data to find matching values.

For example, for each cell in Field 1, the value in that cell is looked up against the entire range of data in Field 2,
and where there is a "Fuzzy" Find/match, that value is populated on the same row in the column named "FUZZY FIND".
I have a separate function that calculates a similarity score in column D.

To illustrate this: Cell A2 = the number 123, well, 123 also exists in Field2 (B5), so on Cell C2 (FUZZY FIND column), the value 123 appears which is a 100% match.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]FIELD1[/TD]
[TD]FIELD2[/TD]
[TD]FUZZY FIND[/TD]
[TD]SIMILARITY SCORE[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]this is pretty awesome[/TD]
[TD]123[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]7899[/TD]
[TD]UPPERCASE[/TD]
[TD]789[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]hello[/TD]
[TD]hell[/TD]
[TD]hell[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]this is awesome[/TD]
[TD]123[/TD]
[TD]this is pretty awesome[/TD]
[TD]68%[/TD]
[/TR]
[TR]
[TD]ALEX[/TD]
[TD]789[/TD]
[TD]ALEX[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]uppercase[/TD]
[TD]karl[/TD]
[TD][/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]alex[/TD]
[TD]ALEX[/TD]
[TD][/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]


The problem I am running into however is that my FuzzyFind function is case sensitive, whereas I would like it to ignore the case -- or perhaps consider all of the data to be upper or lower case, if that's the easier solution.

For example, in cell A6 I have the name "ALEX" appear, which also appears in Field 2 exactly as "ALEX", so in cell C6, the corresponding Fuzzy Find = "ALEX", which is rated as a 100% match.

However, in cell A8, the name "alex" appears as lower case, of which there is no lowercase variant in Column 2, so in cell C8, my Fuzzy Find value is simply blank, with a 0% match rate.

On the other hand, I would like "alex" and "ALEX" to both be treated equally, and correspond to a 100% match.

I've played around with the code in the function used to generate the values in Col C, but can't figure out a way to get it to either ignore case or normalize the case (all UPPER or lower) against both columns being evaluated.

I can't add attachments, but below is the same code I used against the same dataset in the above table that I hope someone can take a look at, and lend a helping hand.

Function FuzzyFind(lookup_value As String, tbl_array As Range) As StringDim i As Integer, str As String, Value As String
Dim a As Integer, b As Integer, cell As Variant
For Each cell In tbl_array
  str = cell
  For i = 1 To Len(lookup_value)
    If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then
      a = a + 1
      cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999)
    End If
  Next i
  a = a - Len(cell)
  If a > b Then
    b = a
    Value = str
  End If
  a = 0
Next cell
FuzzyFind = Value
End Function

Thanks in advance!

 

[MARK858]

Possibly...

Function FuzzyFind(lookup_value As String, tbl_array As Range) As String
    Dim i As Integer, str As String, Value As String
    Dim a As Integer, b As Integer, cell As Variant
    For Each cell In tbl_array
        str = cell
        For i = 1 To Len(lookup_value)
            If InStr(UCase(cell), Mid(UCase(lookup_value), i, 1)) > 0 Then
                a = a + 1
                cell = Mid(UCase(cell), 1, InStr(UCase(cell), Mid(UCase(lookup_value), i, 1)) - 1) & Mid(UCase(cell), InStr(UCase(cell), Mid(UCase(lookup_value), i, 1)) + 1, 9999)
            End If
        Next i
        a = a - Len(cell)
        If a > b Then
            b = a
            Value = str
        End If
        a = 0
    Next cell
    FuzzyFind = Value
End Function

 

[MARK858]

Please post links if you are going to cross-post as per the forum rules

https://www.excelforum.com/excel-pr...a-fuzzy-lookup-find-case-sensitive-issue.html