Option Explicit
\nType RankInfo
\nOffset As Integer
\nPercentage As Single
\nEnd Type<\/p>\n
Function FuzzyPercent(ByVal String1 As String, _
\nByVal String2 As String, _
\nOptional Algorithm As Integer = 3, _
\nOptional Normalised As Boolean = False) As Single
\n‘*************************************
\n‘** Return a % match on two strings **
\n‘*************************************
\nDim intLen1 As Integer, intLen2 As Integer
\nDim intCurLen As Integer
\nDim intTo As Integer
\nDim intPos As Integer
\nDim intPtr As Integer
\nDim intScore As Integer
\nDim intTotScore As Integer
\nDim intStartPos As Integer
\nDim strWork As String<\/p>\n
‘——————————————————-
\n‘– If strings havent been normalised, normalise them —
\n‘——————————————————-
\nIf Normalised = False Then
\nString1 = LCase$(Application.Trim(String1))
\nString2 = LCase$(Application.Trim(String2))
\nEnd If<\/p>\n
‘———————————————-
\n‘– Give 100% match if strings exactly equal —
\n‘———————————————-
\nIf String1 = String2 Then
\nFuzzyPercent = 1
\nExit Function
\nEnd If<\/p>\n
intLen1 = Len(String1)
\nintLen2 = Len(String2)<\/p>\n
‘—————————————-
\n‘– Give 0% match if string length < 2 —
\n‘—————————————-
\nIf intLen1 < 2 Then
\nFuzzyPercent = 0
\nExit Function
\nEnd If<\/p>\n
intTotScore = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘initialise total possible score
\nintScore = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘initialise current score<\/p>\n
‘——————————————————–
\n‘– If Algorithm = 1 or 3, Search for single characters —
\n‘——————————————————–
\nIf (Algorithm And 1) <> 0 Then
\nFuzzyAlg1 String1, String2, intScore, intTotScore
\nIf intLen1 < intLen2 Then FuzzyAlg1 String2, String1, intScore, intTotScore
\nEnd If<\/p>\n
‘———————————————————–
\n‘– If Algorithm = 2 or 3, Search for pairs, triplets etc. —
\n‘———————————————————–
\nIf (Algorithm And 2) <> 0 Then
\nFuzzyAlg2 String1, String2, intScore, intTotScore
\nIf intLen1 < intLen2 Then FuzzyAlg2 String2, String1, intScore, intTotScore
\nEnd If<\/p>\n
FuzzyPercent = intScore \/ intTotScore<\/p>\n
End Function
\nPrivate Sub FuzzyAlg1(ByVal String1 As String, _
\nByVal String2 As String, _
\nByRef Score As Integer, _
\nByRef TotScore As Integer)
\nDim intLen1 As Integer, intPos As Integer, intPtr As Integer, intStartPos As Integer<\/p>\n
intLen1 = Len(String1)
\nTotScore = TotScore + intLen1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘update total possible score
\nintPos = 0
\nFor intPtr = 1 To intLen1
\nintStartPos = intPos + 1
\nintPos = InStr(intStartPos, String2, Mid$(String1, intPtr, 1))
\nIf intPos > 0 Then
\nIf intPos > intStartPos + 3 Then\u00a0\u00a0\u00a0\u00a0 ‘No match if char is > 3 bytes away
\nintPos = intStartPos
\nElse
\nScore = Score + 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘Update current score
\nEnd If
\nElse
\nintPos = intStartPos
\nEnd If
\nNext intPtr
\nEnd Sub
\nPrivate Sub FuzzyAlg2(ByVal String1 As String, _
\nByVal String2 As String, _
\nByRef Score As Integer, _
\nByRef TotScore As Integer)
\nDim intCurLen As Integer, intLen1 As Integer, intTo As Integer, intPtr As Integer, intPos As Integer
\nDim strWork As String<\/p>\n
intLen1 = Len(String1)
\nFor intCurLen = 2 To intLen1
\nstrWork = String2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘Get a copy of String2
\nintTo = intLen1 – intCurLen + 1
\nTotScore = TotScore + Int(intLen1 \/ intCurLen)\u00a0 ‘Update total possible score
\nFor intPtr = 1 To intTo Step intCurLen
\nintPos = InStr(strWork, Mid$(String1, intPtr, intCurLen))
\nIf intPos > 0 Then
\nMid$(strWork, intPos, intCurLen) = String$(intCurLen, &H0) ‘corrupt found string
\nScore = Score + 1\u00a0\u00a0\u00a0\u00a0 ‘Update current score
\nEnd If
\nNext intPtr
\nNext intCurLen<\/p>\n
End Sub<\/p>\n
Function FuzzyVLookup(ByVal LookupValue As String, _
\nByVal TableArray As Range, _
\nByVal IndexNum As Integer, _
\nOptional NFPercent As Single = 0.05, _
\nOptional Rank As Integer = 1, _
\nOptional Algorithm As Integer = 3, _
\nOptional AdditionalCols As Integer = 0) As Variant
\n‘********************************************************************************
\n‘** Function to Fuzzy match LookupValue with entries in\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** column 1 of table specified by TableArray.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** TableArray must specify the top left cell of the range to be searched\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** The function stops scanning the table when an empty cell in column 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** is found.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** For each entry in column 1 of the table, FuzzyPercent is called to match\u00a0\u00a0 **
\n‘** LookupValue with the Table entry.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** ‘Rank’ is an optional parameter which may take any value > 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (default 1) and causes the function to return the ‘nth’ best\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 match (where ‘n’ is defined by ‘Rank’ parameter)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** If the ‘Rank’ match percentage < NFPercent (Default 5%), #N\/A is returned. **
\n‘** IndexNum is the column number of the entry in TableArray required to be\u00a0\u00a0\u00a0 **
\n‘** returned, as follows:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** If IndexNum > 0 and the ‘Rank’ percentage match is >= NFPercent\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (Default 5%) the column entry indicated by IndexNum is\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 returned.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** if IndexNum = 0 and the ‘Rank’ percentage match is >= NFPercent\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (Default 5%) the offset row (starting at 1) is returned.\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 This value can be used directly in the ‘Index’ function.\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** Algorithm can take one of the following values:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** Algorithm = 1:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 This algorithm is best suited for matching mis-spellings.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 For each character in ‘String1’, a search is performed on ‘String2’.\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 The search is deemed successful if a character is found in ‘String2’\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 within 3 characters of the current position.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 A score is kept of matching characters which is returned as a\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 percentage of the total possible score.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** Algorithm = 2:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 This algorithm is best suited for matching sentences, or\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 ‘firstname lastname’ compared with ‘lastname firstname’ combinations\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 A count of matching pairs, triplets, quadruplets etc. in ‘String1’ and **
\n‘**\u00a0\u00a0\u00a0\u00a0 ‘String2’ is returned as a percentage of the total possible.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** Algorithm = 3: Both Algorithms 1 and 2 are performed.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘********************************************************************************
\nDim R As Range<\/p>\n
Dim strListString As String
\nDim strWork As String<\/p>\n
Dim sngMinPercent As Single
\nDim sngWork As Single
\nDim sngCurPercent\u00a0 As Single
\nDim intBestMatchPtr As Integer
\nDim intRankPtr As Integer
\nDim intRankPtr1 As Integer
\nDim I As Integer<\/p>\n
Dim lEndRow As Long<\/p>\n
Dim udRankData() As RankInfo<\/p>\n
Dim vCurValue As Variant<\/p>\n
‘————————————————————–
\n‘–\u00a0\u00a0\u00a0 Validation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 —
\n‘————————————————————–<\/p>\n
LookupValue = LCase$(Application.Trim(LookupValue))<\/p>\n
If IsMissing(NFPercent) Then
\nsngMinPercent = 0.05
\nElse
\nIf (NFPercent <= 0) Or (NFPercent > 1) Then
\nFuzzyVLookup = “*** ‘NFPercent’ must be a percentage > zero ***”
\nExit Function
\nEnd If
\nsngMinPercent = NFPercent
\nEnd If<\/p>\n
If Rank < 1 Then
\nFuzzyVLookup = “*** ‘Rank’ must be an integer > 0 ***”
\nExit Function
\nEnd If<\/p>\n
ReDim udRankData(1 To Rank)<\/p>\n
lEndRow = TableArray.Rows.Count
\nIf VarType(TableArray.Cells(lEndRow, 1).Value) = vbEmpty Then
\nlEndRow = TableArray.Cells(lEndRow, 1).End(xlUp).Row
\nEnd If<\/p>\n
‘—————
\n‘– Main loop —
\n‘—————
\nFor Each R In Range(TableArray.Cells(1, 1), TableArray.Cells(lEndRow, 1))
\nvCurValue = “”
\nFor I = 0 To AdditionalCols
\nvCurValue = vCurValue & R.Offset(0, I).Text
\nNext I
\nIf VarType(vCurValue) = vbString Then
\nstrListString = LCase$(Application.Trim(vCurValue))<\/p>\n
‘————————————————
\n‘– Fuzzy match strings & get percentage match —
\n‘————————————————
\nsngCurPercent = FuzzyPercent(String1:=LookupValue, _
\nString2:=strListString, _
\nAlgorithm:=Algorithm, _
\nNormalised:=True)<\/p>\n
If sngCurPercent >= sngMinPercent Then
\n‘—————————
\n‘– Store in ranked array —
\n‘—————————
\nFor intRankPtr = 1 To Rank
\nIf sngCurPercent > udRankData(intRankPtr).Percentage Then
\nFor intRankPtr1 = Rank To intRankPtr + 1 Step -1
\nWith udRankData(intRankPtr1)
\n.Offset = udRankData(intRankPtr1 – 1).Offset
\n.Percentage = udRankData(intRankPtr1 – 1).Percentage
\nEnd With
\nNext intRankPtr1
\nWith udRankData(intRankPtr)
\n.Offset = R.Row
\n.Percentage = sngCurPercent
\nEnd With
\nExit For
\nEnd If
\nNext intRankPtr
\nEnd If<\/p>\n
End If
\nNext R<\/p>\n
If udRankData(Rank).Percentage < sngMinPercent Then
\n‘————————————–
\n‘– Return ‘#N\/A’ if below NFPercent —
\n‘————————————–
\nFuzzyVLookup = CVErr(xlErrNA)
\nElse
\nintBestMatchPtr = udRankData(Rank).Offset – TableArray.Cells(1, 1).Row + 1
\nIf IndexNum > 0 Then
\n‘———————————–
\n‘– Return column entry specified —
\n‘———————————–
\nFuzzyVLookup = TableArray.Cells(intBestMatchPtr, IndexNum)
\nElse
\n‘———————–
\n‘– Return offset row —
\n‘———————–
\nFuzzyVLookup = intBestMatchPtr
\nEnd If
\nEnd If
\nEnd Function
\nFunction FuzzyHLookup(ByVal LookupValue As String, _
\nByVal TableArray As Range, _
\nByVal IndexNum As Integer, _
\nOptional NFPercent As Single = 0.05, _
\nOptional Rank As Integer = 1, _
\nOptional Algorithm As Integer = 3) As Variant
\n‘********************************************************************************
\n‘** Function to Fuzzy match LookupValue with entries in\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** row 1 of table specified by TableArray.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** TableArray must specify the top left cell of the range to be searched\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** The function stops scanning the table when an empty cell in row 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** is found.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** For each entry in row 1 of the table, FuzzyPercent is called to match\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** LookupValue with the Table entry.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** ‘Rank’ is an optional parameter which may take any value > 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (default 1) and causes the function to return the ‘nth’ best\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 match (where ‘n’ is defined by ‘Rank’ parameter)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** If the ‘Rank’ match percentage < NFPercent (Default 5%), #N\/A is returned. **
\n‘** IndexNum is the row number of the entry in TableArray required to be\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** returned, as follows:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** If IndexNum > 0 and the ‘Rank’ percentage match is >= NFPercent\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (Default 5%) the row entry indicated by IndexNum is\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 returned.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** if IndexNum = 0 and the ‘Rank’ percentage match is >= NFPercent\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (Default 5%) the offset col (starting at 0) is returned.\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 This value can be used directly in the ‘OffSet’ function.\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** Algorithm can take one of the following values:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** Algorithm = 1:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 For each character in ‘String1’, a search is performed on ‘String2’.\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 The search is deemed successful if a character is found in ‘String2’\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 within 3 characters of the current position.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 A score is kept of matching characters which is returned as a\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 percentage of the total possible score.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** Algorithm = 2:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘**\u00a0\u00a0\u00a0\u00a0 A count of matching pairs, triplets, quadruplets etc. in ‘String1’ and **
\n‘**\u00a0\u00a0\u00a0\u00a0 ‘String2’ is returned as a percentage of the total possible.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘** Algorithm = 3: Both Algorithms 1 and 2 are performed.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 **
\n‘********************************************************************************
\nDim R As Range<\/p>\n
Dim strListString As String
\nDim strWork As String<\/p>\n
Dim sngMinPercent As Single
\nDim sngWork As Single
\nDim sngCurPercent\u00a0 As Single<\/p>\n
Dim intBestMatchPtr As Integer
\nDim intPtr As Integer
\nDim intRankPtr As Integer
\nDim intRankPtr1 As Integer<\/p>\n
Dim iEndCol As Integer<\/p>\n
Dim udRankData() As RankInfo<\/p>\n
Dim vCurValue As Variant
\n‘————————————————————–
\n‘–\u00a0\u00a0\u00a0 Validation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 —
\n‘————————————————————–
\nLookupValue = LCase$(Application.Trim(LookupValue))<\/p>\n
If IsMissing(NFPercent) Then
\nsngMinPercent = 0.05
\nElse
\nIf (NFPercent <= 0) Or (NFPercent > 1) Then
\nFuzzyHLookup = “*** ‘NFPercent’ must be a percentage > zero ***”
\nExit Function
\nEnd If
\nsngMinPercent = NFPercent
\nEnd If<\/p>\n
If Rank < 1 Then
\nFuzzyHLookup = “*** ‘Rank’ must be an integer > 0 ***”
\nExit Function
\nEnd If<\/p>\n
ReDim udRankData(1 To Rank)
\n‘**************************
\niEndCol = TableArray.Columns.Count
\nIf VarType(TableArray.Cells(1, iEndCol).Value) = vbEmpty Then
\niEndCol = TableArray.Cells(1, iEndCol).End(xlToLeft).Column
\nEnd If<\/p>\n
‘—————
\n‘– Main loop —
\n‘—————
\nFor Each R In Range(TableArray.Cells(1, 1), TableArray.Cells(1, iEndCol))
\nvCurValue = R.Value
\nIf VarType(vCurValue) = vbString Then
\nstrListString = LCase$(Application.Trim(vCurValue))<\/p>\n
‘————————————————
\n‘– Fuzzy match strings & get percentage match —
\n‘————————————————
\nsngCurPercent = FuzzyPercent(String1:=LookupValue, _
\nString2:=strListString, _
\nAlgorithm:=Algorithm, _
\nNormalised:=True)<\/p>\n
If sngCurPercent >= sngMinPercent Then
\n‘—————————
\n‘– Store in ranked array —
\n‘—————————
\nFor intRankPtr = 1 To Rank
\nIf sngCurPercent > udRankData(intRankPtr).Percentage Then
\nFor intRankPtr1 = Rank To intRankPtr + 1 Step -1
\nWith udRankData(intRankPtr1)
\n.Offset = udRankData(intRankPtr1 – 1).Offset
\n.Percentage = udRankData(intRankPtr1 – 1).Percentage
\nEnd With
\nNext intRankPtr1
\nWith udRankData(intRankPtr)
\n.Offset = R.Column
\n.Percentage = sngCurPercent
\nEnd With
\nExit For
\nEnd If
\nNext intRankPtr
\nEnd If<\/p>\n
End If
\nNext R<\/p>\n
If udRankData(Rank).Percentage < sngMinPercent Then
\n‘————————————–
\n‘– Return ‘#N\/A’ if below NFPercent —
\n‘————————————–
\nFuzzyHLookup = CVErr(xlErrNA)
\nElse
\nintBestMatchPtr = udRankData(Rank).Offset – TableArray.Cells(1, 1).Column + 1
\nIf IndexNum > 0 Then
\n‘———————————–
\n‘– Return row entry specified —
\n‘———————————–
\nFuzzyHLookup = TableArray.Cells(IndexNum, intBestMatchPtr)
\nElse
\n‘———————–
\n‘– Return offset col —
\n‘———————–
\nFuzzyHLookup = intBestMatchPtr
\nEnd If
\nEnd If
\nEnd Function<\/p><\/blockquote>\n
Above contains the three functions: FUZZYVLOOKUP, FUZZYHLOOKUP and FUZZYPERCENT.<\/p>\n
\n- FUZZYVLOOKUP ( LookupValue, TableArray, IndexNum, NFpercent, Rank, Algorithm, AdditionalCols)<\/li>\n
- FUZZYHLOOKUP ( LookupValue, TableArray, IndexNum, NFpercent, Rank, Algorithm, AdditionalCols)<\/li>\n
- FUZZYPERCENT (String1, String2, Algorithm, Normalised)<\/li>\n<\/ul>\n
Lookupvalue
\n<\/strong>The value to search in the first column of the table array<\/p>\nTablearray<\/strong>
\nOne or more columns of data. Use a reference to a range or a range name. The values in the first column of table array are the values searched by lookup value.<\/p>\nIndexnum
\n<\/strong>The column number in the table array from which the matching value must be returned. An index num of 1 returns the value in the first column in the table array; an index num of 2 returns the value in the second column in the table array, and so on. If index num is zero, the relative row number in the table array is returned.<\/p>\nNFpercent<\/strong>
\nThe Percentage value below which matching strings are deemed as not found. If no strings in the lookup table equal or exceed this matching percentage, #N\/A is returned.
\nThe higher the percentage specified, the higher the confidence level in the returned result.
\nDefault: 5%<\/p>\nRank<\/strong>
\nAn optional parameter which may take any value > 0 and causes the function to return the specified ranking best match.
\nDefault: 1<\/p>\nAlgorithm
\n<\/strong>Defines the algorithm to be used for matching strings. Valid values are 1, 2 or 3:
\nAlgorithm = 1
\nThis algorithm is best suited for matching mis-spellings.
\nFor each character in ‘String1’, a search is performed on ‘String2’.
\nThe search is deemed successful if a character is found in ‘String2’ within 3 characters of the current position.
\nA score is kept of matching characters which is returned as a percentage of the total possible score.
\nAlgorithm = 2
\nThis algorithm is best suited for matching sentences, or ‘firstname lastname’ compared with ‘lastname firstname’ combinations.
\nA count of matching pairs, triplets, quadruplets etc. in ‘String1’ and ‘String2’ is returned as a percentage of the total possible.
\nAlgorithm = 3: Both Algorithms 1 and 2 are performed.
\nDefault: 3<\/p>\nAdditionalcols<\/strong>
\nDefines the number of subsequent columns after the first within the table array to be concatenated with the first column prior to matching.
\nDefault: 0<\/p>\nString1
\n<\/strong>The first string to be matched. FuzzyVLookup will pass the lookup value supplied (but normalised) as this parameter.<\/p>\nString2
\n<\/strong>The second string to be matched. FuzzyVLookup will pass each of the lookup table strings supplied (but normalised) as this parameter.<\/p>\nAlgorithm<\/strong>
\nAlgorithm to be used. See FuzzyVLookup for further details.
\nDefault: 3<\/p>\nNormalised<\/strong>
\nBoolean value indicating whether the two supplied strings have been normalised. Normalised strings have had leading, trailing and multiple internal spaces removed, and have been converted to lowercase.
\nDefault: False<\/p>\n","protected":false},"excerpt":{"rendered":"A solution I use is to sweep an export of the database for duplicates using the following fuzzy logic match Excel vb macro. Filemaker is my database of choice these days but one of the major issues of database design I find difficult is the balance between freedom of entry and stopping duplicates being created […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[26,4],"tags":[149,79,78,117,150,120,27,119,121,30],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts\/22"}],"collection":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=22"}],"version-history":[{"count":0,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts\/22\/revisions"}],"wp:attachment":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=22"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=22"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=22"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}