Skip to content
The Chat Archive
gpt-3.5-turbo-0125· OpenAI WildChat-4.8M (AI2)

I am using a function with a VBA code to get a close match and not an exact match. The…

  1. User

    I am using a function with a VBA code to get a close match and not an exact match. The results it gives me are too many. How can I further adjust the function to get me a closer match.

    Sub MatchAndDisplay() Dim auditSheet As Worksheet Dim allocSheet As Worksheet Dim returnSheet As Worksheet Dim auditValue As String Dim allocCell As Range Dim returnCell As Range Dim auditRow As Long Dim newRow As Long Dim similarityThreshold As Long

    Set auditSheet = ThisWorkbook.Sheets("Key Audit")
    Set allocSheet = ThisWorkbook.Sheets("Key Allocation")
    Set returnSheet = ThisWorkbook.Sheets("Key Return")
    
    auditValue = auditSheet.Range("K1").Value
    similarityThreshold = 3 ' Adjust this value to set the similarity threshold
    
    newRow = auditSheet.Cells(auditSheet.Rows.Count, 11).End(xlUp).row + 1
    
    For Each allocCell In allocSheet.Range("G2:G" & allocSheet.Cells(allocSheet.Rows.Count, 7).End(xlUp).row)
        If Levenshtein(auditValue, allocCell.Value) <= similarityThreshold Then
            auditRow = newRow
            auditSheet.Cells(newRow, 11).Value = allocCell.Offset(0, -6).Value ' Column A
            auditSheet.Cells(newRow, 12).Value = allocCell.Offset(0, -1).Value ' Column F
            auditSheet.Cells(newRow, 13).Value = allocCell.Value ' Column G
            auditSheet.Cells(newRow, 14).Value = "Key Allocation" ' Sheet name
            newRow = newRow + 1
        End If
    Next allocCell
    
    For Each returnCell In returnSheet.Range("G2:G" & returnSheet.Cells(returnSheet.Rows.Count, 7).End(xlUp).row)
        If Levenshtein(auditValue, returnCell.Value) <= similarityThreshold Then
            auditRow = newRow
            auditSheet.Cells(newRow, 11).Value = returnCell.Offset(0, -6).Value ' Column A
            auditSheet.Cells(newRow, 12).Value = returnCell.Offset(0, -1).Value ' Column F
            auditSheet.Cells(newRow, 13).Value = returnCell.Value ' Column G
            auditSheet.Cells(newRow, 14).Value = "Key Return" ' Sheet name
            newRow = newRow + 1
        End If
    Next returnCell
    

    End Sub Function Levenshtein(s1 As String, s2 As String) As Long Dim i As Long Dim j As Long Dim cost As Long Dim d() As Long

    Dim s1_len As Long
    Dim s2_len As Long
    
    s1_len = Len(s1)
    s2_len = Len(s2)
    ReDim d(0 To s1_len, 0 To s2_len)
    
    For i = 0 To s1_len
        d(i, 0) = i
    Next
    
    For j = 0 To s2_len
        d(0, j) = j
    Next
    
    For i = 1 To s1_len
        For j = 1 To s2_len
            If Mid(s1, i, 1) = Mid(s2, j, 1) Then
                cost = 0
            Else
                cost = 1
            End If
            
            d(i, j) = Application.WorksheetFunction.Min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + cost)
        Next
    Next
    
    Levenshtein = d(s1_len, s2_len)
    

    End Function

  2. Assistant

    To get a closer match, you can adjust the similarityThreshold value in your VBA code.

    One way to do this is to gradually decrease the similarityThreshold value to see the impact on the number of matches. Start by reducing the threshold by 1 and running the code again to see if the results are closer to what you are looking for.

    You can also try different values for the similarityThreshold to find the optimum value that gives you the desired level of similarity in the matches.

    Additionally, you can consider implementing other string matching algorithms or techniques to improve the accuracy of the matching process, such as Jaro-Winkler distance, cosine similarity, or other fuzzy matching algorithms. These algorithms may provide more nuanced similarity measures and help you get closer matches.

  3. User

    how and where do I reduse the threshold

  4. Assistant

    You can reduce the similarityThreshold value directly in your VBA code. In your MatchAndDisplay sub, you have specified the initial similarityThreshold value as 3. You can adjust this value within the code to experiment with different thresholds.

    Here's the portion of your code where you can adjust the similarityThreshold value:

    similarityThreshold = 3 ' Adjust this value to set the initial similarity threshold
    
    ' Inside the For Each loop for allocCell and returnCell
    If Levenshtein(auditValue, allocCell.Value) <= similarityThreshold Then
        ' Your code for processing the matched value
    End If
    
    If Levenshtein(auditValue, returnCell.Value) <= similarityThreshold Then
        ' Your code for processing the matched value
    End If
    

    To adjust the similarityThreshold value, you can simply change the number 3 to the desired lower value. For example, if you want to decrease the threshold to 2:

    similarityThreshold = 2 ' Adjust this value to set the lower similarity threshold
    

    After making this adjustment, run your VBA code to see if the new threshold value provides you with closer matches. You can continue to adjust the threshold value until you find the desired level of similarity in the matches.

  5. User

    The similiarity is no where near close

  6. Assistant

    If adjusting the similarityThreshold value is not providing the desired level of similarity in the matches, there are a few other strategies you can try to improve the matching process:

    1. Implement a different string matching algorithm: Instead of using the Levenshtein distance algorithm, you can try other algorithms such as Jaro-Winkler distance, cosine similarity, or a combination of different algorithms to see if they provide better match results.

    2. Preprocess the strings: Before applying the matching algorithm, you can preprocess the strings by removing stopwords, punctuation, or performing stemming to normalize the text data. This can help improve the accuracy of the matching process.

    3. Adjust the matching criteria: Instead of solely relying on the string similarity threshold, you can consider other factors such as the length of the strings, the presence of certain keywords, or other contextual information to determine if a match is valid.

    4. Manual review and correction: If the automated matching process is not providing satisfactory results, you may need to manually review and correct the matches to ensure accuracy. This manual review process can also help you identify patterns or issues that can be addressed in the automated matching algorithm.

    By exploring these additional strategies, you may be able to improve the accuracy and closeness of the matches in your VBA code.

  7. User

    is there a way to search within, a bit like 123 or 123* or *123

  8. Assistant

    Yes, you can modify your VBA code to implement wildcard or partial matching within the Levenshtein distance algorithm. This will allow you to search for strings that contain a specific sequence of characters within them, similar to using wildcards like *123*, 123*, or *123.

    Here's how you can modify your code to include wildcard matching:

    1. Update the Levenshtein function to include wildcard checks within the comparison:
    Function LevenshteinWildcards(s1 As String, s2 As String) As Long
        ' Your Levenshtein distance logic here with wildcard checks
    End Function
    
    1. Update your MatchAndDisplay sub to call the LevenshteinWildcards function for wildcard matching:
    For Each allocCell In allocSheet.Range("G2:G" & allocSheet.Cells(allocSheet.Rows.Count, 7).End(xlUp).Row)
        If WildcardMatch(auditValue, allocCell.Value) Then
            ' Your code for processing the wildcard match
        End If
    Next allocCell
    
    1. Implement the logic inside the LevenshteinWildcards function to handle wildcard matching based on your specific requirements. This may involve checking if a specific pattern or sequence is present within the strings being compared.

    By incorporating wildcard matching logic within your Levenshtein distance function, you can search for partial matches or sequences within strings in your VBA code. This can help you achieve the desired level of flexibility in matching criteria to find closer and more relevant matches.

  9. User

    Can you write the code for me, please.

  10. Assistant

    Certainly! Here is an example of how you can modify your VBA code to include wildcard matching within the Levenshtein distance algorithm:

    Sub MatchAndDisplayWithWildcards()
        Dim auditSheet As Worksheet
        Dim allocSheet As Worksheet
        Dim returnSheet As Worksheet
        Dim auditValue As String
        Dim allocCell As Range
        Dim returnCell As Range
        Dim auditRow As Long
        Dim newRow As Long
        Dim wildString As String
        
        Set auditSheet = ThisWorkbook.Sheets("Key Audit")
        Set allocSheet = ThisWorkbook.Sheets("Key Allocation")
        Set returnSheet = ThisWorkbook.Sheets("Key Return")
    
        auditValue = auditSheet.Range("K1").Value
        newRow = auditSheet.Cells(auditSheet.Rows.Count, 11).End(xlUp).Row + 1
    
        ' Define the wildcard string pattern you want to search for
        wildString = "*" & "123" & "*"
    
        For Each allocCell In allocSheet.Range("G2:G" & allocSheet.Cells(allocSheet.Rows.Count, 7).End(xlUp).Row)
            If WildcardMatch(auditValue, allocCell.Value, wildString) Then
                auditRow = newRow
                auditSheet.Cells(newRow, 11).Value = allocCell.Offset(0, -6).Value ' Column A
                auditSheet.Cells(newRow, 12).Value = allocCell.Offset(0, -1).Value ' Column F
                auditSheet.Cells(newRow, 13).Value = allocCell.Value ' Column G
                auditSheet.Cells(newRow, 14).Value = "Key Allocation" ' Sheet name
                newRow = newRow + 1
            End If
        Next allocCell
    End Sub
    
    Function WildcardMatch(s1 As String, s2 As String, wildString As String) As Boolean
        Dim compareStr As String
        
        ' Check if s2 contains the wildcard string pattern within it
        compareStr = Replace(wildString, "*", "")
        
        If InStr(1, s2, compareStr, vbTextCompare) > 0 Then
            ' Perform Levenshtein distance check on s1 and s2 
            If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                WildcardMatch = True
            End If
        End If
    End Function
    
    Function Levenshtein(s1 As String, s2 As String) As Long
        ' Your existing Levenshtein distance function code goes here
        ' Keep this function as it is
    End Function
    

    In this code snippet, the WildcardMatch function checks if a specific wildcard string pattern (in this case, "123") is present within the string being checked. If the pattern is found, it then performs a Levenshtein distance check to determine if the strings are a close enough match.

    You can adjust the wildcard string pattern ("123") and the Levenshtein distance threshold value as needed to customize the matching criteria. Running this modified code will help you search for partial matches or sequences within your strings.

  11. User

    can the wild card patterns be several types like 123, or 123 or 123

  12. Assistant

    Yes, you can modify the WildcardMatch function to handle multiple wildcard patterns such as *123, 123*, or *123*. Here's an updated version of the WildcardMatch function that can check for multiple wildcard patterns:

    Function WildcardMatch(s1 As String, s2 As String) As Boolean
        Dim wildString As Variant
        Dim compareStr As String
        
        wildString = Array("*123", "123*", "*123*")
        
        For Each pattern In wildString
            compareStr = Replace(pattern, "*", "")
            
            If (Left(pattern, 1) = "*" And Right(pattern, 1) = "*") Or (Left(pattern, 1) <> "*" And Right(pattern, 1) = "*") Then
                ' Check for wildcard pattern in the middle or end of the string
                If InStr(1, s2, compareStr, vbTextCompare) > 0 Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            ElseIf Left(pattern, 1) = "*" Then
                ' Check for wildcard pattern at the beginning of the string
                If Right(s2, Len(compareStr)) = compareStr Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            ElseIf Right(pattern, 1) = "*" Then
                ' Check for wildcard pattern at the end of the string
                If Left(s2, Len(compareStr)) = compareStr Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            End If
        Next pattern
    End Function
    

    In this updated version of the WildcardMatch function, the wildString array contains the three different wildcard patterns (123, 123, 123). The function iterates over each pattern and checks if the specific wildcard match occurs at the beginning, end, or within the string. The function then performs a Levenshtein distance check to determine if the strings are a close enough match based on the specified threshold.

    You can adjust the wildcard patterns and the Levenshtein distance threshold as needed to customize the matching criteria in your VBA code.

  13. User

    I am not searching for 123. It was just an exampl

  14. Assistant

    If you are looking to search for multiple wildcard patterns within your strings, you can modify the WildcardMatch function to handle a list of different wildcard patterns. Here's how you can update the function to search for various wildcard patterns:

    Function WildcardMatch(s1 As String, s2 As String) As Boolean
        Dim wildStrings As Variant
        Dim compareStr As String
        Dim pattern As Variant
        
        wildStrings = Array("*abc", "def*", "*ghi*", "*jkl*")
    
        For Each pattern In wildStrings
            compareStr = Replace(pattern, "*", "")
            
            If (Left(pattern, 1) = "*" And Right(pattern, 1) = "*") Or (Left(pattern, 1) <> "*" And Right(pattern, 1) = "*") Then
                ' Check for wildcard pattern in the middle or end of the string
                If InStr(1, s2, compareStr, vbTextCompare) > 0 Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            ElseIf Left(pattern, 1) = "*" Then
                ' Check for wildcard pattern at the beginning of the string
                If Right(s2, Len(compareStr)) = compareStr Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            ElseIf Right(pattern, 1) = "*" Then
                ' Check for wildcard pattern at the end of the string
                If Left(s2, Len(compareStr)) = compareStr Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            End If
        Next pattern
    End Function
    

    In the updated WildcardMatch function, the wildStrings array contains multiple wildcard patterns (abc, def, ghi, jkl). The function will iterate over each pattern and check for matches at the beginning, end, or within the string, based on the pattern's wildcards. The Levenshtein distance check is performed for each matching pattern to determine if the strings are close enough based on the specified threshold.

    You can customize the wildStrings array with your desired wildcard patterns, and adjust the Levenshtein distance threshold as needed to enhance the string matching in your VBA code.

  15. User

    what about if the seach value contains numbers or a combination of numbers & letters

  16. Assistant

    When searching for wildcard patterns in strings that can contain numbers, letters, or a combination of both, you can modify the WildcardMatch function to handle such scenarios. You can adjust the pattern matching logic to consider alphanumeric characters in the search value and wildcard patterns. Here's an example of how you can update the function:

    Function WildcardMatch(s1 As String, s2 As String) As Boolean
        Dim wildStrings As Variant
        Dim compareStr As String
        Dim pattern As Variant
        
        wildStrings = Array("*12abc", "def*34", "*ghi56*", "*j7kl*89")
    
        For Each pattern In wildStrings
            compareStr = Replace(pattern, "*", "")
            
            If (Left(pattern, 1) = "*" And Right(pattern, 1) = "*") Or (Left(pattern, 1) <> "*" And Right(pattern, 1) = "*") Then
                ' Check for wildcard pattern in the middle or end of the string
                If InStr(1, s2, compareStr, vbTextCompare) > 0 Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            ElseIf Left(pattern, 1) = "*" Then
                ' Check for wildcard pattern at the beginning of the string
                If Right(s2, Len(compareStr)) = compareStr Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            ElseIf Right(pattern, 1) = "*" Then
                ' Check for wildcard pattern at the end of the string
                If Left(s2, Len(compareStr)) = compareStr Then
                    If Levenshtein(s1, s2) <= 3 Then ' Adjust the Levenshtein distance threshold as needed
                        WildcardMatch = True
                        Exit Function
                    End If
                End If
            End If
        Next pattern
    End Function
    

    In this example, the WildCardMatch function is set up to handle wildcard patterns like "12abc", "def34", "ghi56", and "j7kl89". It checks for these patterns within the search value (s2) that can contain numbers, letters, or a combination of both. The function performs the Levenshtein distance check to determine if the strings are a close enough match based on the specified threshold.

    You can adjust the wildStrings array with your specific alphanumeric wildcard patterns and customize the Levenshtein distance threshold as needed for accurate string matching in your VBA code.