I am using a function with a VBA code to get a close match and not an exact match. The…
- 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 returnCellEnd 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
- Assistant
To get a closer match, you can adjust the
similarityThresholdvalue in your VBA code.One way to do this is to gradually decrease the
similarityThresholdvalue 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
similarityThresholdto 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.
- User
how and where do I reduse the threshold
- Assistant
You can reduce the
similarityThresholdvalue directly in your VBA code. In yourMatchAndDisplaysub, you have specified the initialsimilarityThresholdvalue 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
similarityThresholdvalue: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 IfTo adjust the
similarityThresholdvalue, you can simply change the number3to 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 thresholdAfter 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.
- User
The similiarity is no where near close
- Assistant
If adjusting the
similarityThresholdvalue 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:-
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.
-
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.
-
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.
-
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.
-
- User
is there a way to search within, a bit like 123 or 123* or *123
- 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:
- Update the
Levenshteinfunction 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- Update your
MatchAndDisplaysub to call theLevenshteinWildcardsfunction 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- Implement the logic inside the
LevenshteinWildcardsfunction 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.
- Update the
- User
Can you write the code for me, please.
- 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 FunctionIn this code snippet, the
WildcardMatchfunction 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.
- User
can the wild card patterns be several types like 123, or 123 or 123
- Assistant
Yes, you can modify the
WildcardMatchfunction to handle multiple wildcard patterns such as*123,123*, or*123*. Here's an updated version of theWildcardMatchfunction 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 FunctionIn this updated version of the
WildcardMatchfunction, thewildStringarray 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.
- User
I am not searching for 123. It was just an exampl
- Assistant
If you are looking to search for multiple wildcard patterns within your strings, you can modify the
WildcardMatchfunction 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 FunctionIn the updated
WildcardMatchfunction, thewildStringsarray 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
wildStringsarray with your desired wildcard patterns, and adjust the Levenshtein distance threshold as needed to enhance the string matching in your VBA code. - User
what about if the seach value contains numbers or a combination of numbers & letters
- Assistant
When searching for wildcard patterns in strings that can contain numbers, letters, or a combination of both, you can modify the
WildcardMatchfunction 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 FunctionIn this example, the
WildCardMatchfunction 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
wildStringsarray with your specific alphanumeric wildcard patterns and customize the Levenshtein distance threshold as needed for accurate string matching in your VBA code.