Fuzzy Matching - new version plus explanation

[al_b_cnu]

It has been a while since I originally posted my Fuzzy matching UDF’s on the board, and several variants have appeared subsequently.

I thought it time to ‘put the record straight’ & post a definitive version which contains slightly more efficient code, and better matching algorithms, so here it is.

Firstly, I must state that the Fuzzy matching algorithms are very CPU hungry, and should be used sparingly. If for instance you require to lookup a match for a string which starts with, contains or ends with a specified value, this can be performed far more efficiently using the MATCH function:

Fuzzy Examples.xls
	A	B	C	D	E
1			Starts With	Ends	Contains
2			Bill	jelen	Bill
3	Mr Bill Jelen		4	3	3
4	Bill Jelen
5	Joe Bloggs
6	Fred Smith
MATCH Example

... Continued ...

 

[banier]

Hi Alan,

Just wanted to write to say a massive thank you for writing/posting/improving/answering queries on your fuzzy lookup function. It is and you are awesome.
I'm in the early stages on creating the lookup spreadsheets I require but with amount of data I need to analyse excel is starting to slow down considerably.
My task invloves the check on the latest requirements spec against the previous 5 versions of similar specs, looking for equivelent requirements, that maybe written slightly differently. Therefore I have 5x lookup processes running, and with it looking up paragraph style data, it's taking some considerable time for excel to do its thing....!
I'm assuming, from reading previous threads that Algorithm 2 is best for 'sentence matching', but can you suggest ways to speed up excel in doing it's calculation or for a more efficient way to repeativily check the same lookupvalue against different tablearrays?

Also does the lookup range need to be in ascending order with fuzzyvlookup function, like the usual vlookup functions?
Any other suggestions you may have would be greatly received.

Thanks - much appreicated

Dan

 

[David_UK]

The code is not Normal Visual Basic!

It is Excel Visual Basic for Applications (Excel VBA)

It must be stored in a Standard code module, like: Module1, as is!

Alt + F11
Toolbar: Insert - Module
Paste code in the Editor window for that module!

Click the top-most-Right Close "X" to return to the sheet.

Thanks, I'm getting the error message #value in my result... Do you know what's causing this?

 

[bobo999]

I'd just like to say a BIG thank you. This is extremely useful and has saved countless hours for many people. There is one tweak I was wondering that you could help with.

I am using this to perform fuzzy matches on operating systems.

Namely Windows Server 2003 "Whatever"
Windows Server 2008 R2, Data center, etc.

As it stands sometimes the algorithms return 2003, when the original data set is 2008, and vice versa.
I will need to implement some sort of weighting into the original algorithm but was wondering if it's already been done?
For example if the string contains 2003, then the resulting string MUST also have that string.

Thanks.

 

[drgs]

As an alternative to FuzzyAlg2, here is a function which is very similar in structure, but somewhat different in principle. Two main differences:
Early stopping -- if, for example, when searching for bits of strings of size intPtr = 2, and none are found, there is no point in moving on to intPtr = 3...
Similarity is calculated by squaring the share of largest matched strings.

As with FuzzyAlg2, SimilarText is not communative
SimilarText(str1, str2) <> SimilarText(str2, str1)


"fuzzymatching" (len 13), compared to
"fuxxymatchen" (len 12)
would give
fu __ ymatch _ n _


similarity = 2^2+0^2+6^2+0^2+1^2+0^2 / 13^2 = 0.2426
here weight = 2 by default, with weight = 1.25, similarity would be 0.51

Public Function SimilarText(ByVal str1 As String, _
                            ByVal str2 As String, _
                            Optional ByVal dblWeight As Double = 2) As Double
    Dim lngLen1 As Long
    Dim lngLen2 As Long
    Dim lngLenMin As Long
    Dim i As Integer
    Dim j As Integer
    Dim lngLast As Long
    Dim blnStop As Boolean
    Dim dblSum As Double




    lngLen1 = Len(str1)
    lngLen2 = Len(str2)
    If lngLen1 = 0 Or lngLen2 = 0 Then
        SimilarText = 0
        Exit Function
    End If
    If lngLen1 < lngLen2 Then
        lngLenMin = lngLen1
    Else
        lngLenMin = lngLen2
    End If
    For i = 1 To lngLenMin
        lngLast = -i
        blnStop = True
        For j = 1 To lngLen1 - i + 1
            If InStr(str2, Mid$(str1, j, i)) > 0 Then
                If i > 1 Then
                    dblSum = dblSum + (i - (lngLast + i - j + Abs(lngLast + i - j)) / 2) * (i ^ (dblWeight - 1) - (i - 1) ^ (dblWeight - 1))
                Else
                    dblSum = dblSum + 1
                End If
                If j - lngLast = 1 Then
                    blnStop = False
                End If
                lngLast = j
            End If
        Next j
        If blnStop Then
            Exit For
        End If
    Next i
    SimilarText = dblSum / lngLen1 ^ dblWeight
End Function

 

[drgs]

Anyway, cant help but notice how similarity scores can be inconsistent, maybe a place for improvement...

Sub test()
    Dim str1 As String
    Dim str2 As String
    
    str1 = "fuzzymatching"
    str2 = "matchingfuzzy"
    MsgBox FuzzyPercent(str1, str2, 1) 'Similarity = 0% (?)
    MsgBox FuzzyPercent(str1, str2, 2) 'Similarity = 54%






    str1 = "abcdefghijklmnopqrstuvwxyz"
    str2 = " bcdefghijklmnopqrstuvwxyz"
    MsgBox FuzzyPercent(str1, str2, 1) 'Similarity = 0%
    MsgBox FuzzyPercent(str1, str2, 2) 'Similarity = 61,5%


    str1 = "abcdefghijklmnopqrstuvwxyz"
    str2 = "a cdefghijklmnopqrstuvwzyz"
    MsgBox FuzzyPercent(str1, str2, 1) 'Similarity = 92%
    MsgBox FuzzyPercent(str1, str2, 2) 'Similarity = 42%
End Sub

That said, there are times when you have to send for the ‘big boys’.

Option Explicit
Type RankInfo
    Offset As Integer
    Percentage As Single
End Type

Function FuzzyPercent(ByVal String1 As String, _
                      ByVal String2 As String, _
                      Optional Algorithm As Integer = 3, _
                      Optional Normalised As Boolean = False) As Single
'*************************************
'** Return a % match on two strings **
'*************************************
Dim intLen1 As Integer, intLen2 As Integer
Dim intCurLen As Integer
Dim intTo As Integer
Dim intPos As Integer
Dim intPtr As Integer
Dim intScore As Integer
Dim intTotScore As Integer
Dim intStartPos As Integer
Dim strWork As String

'-------------------------------------------------------
'-- If strings havent been normalised, normalise them --
'-------------------------------------------------------
If Normalised = False Then
    String1 = LCase$(Application.Trim(String1))
    String2 = LCase$(Application.Trim(String2))
End If

'----------------------------------------------
'-- Give 100% match if strings exactly equal --
'----------------------------------------------
If String1 = String2 Then
    FuzzyPercent = 1
    Exit Function
End If

intLen1 = Len(String1)
intLen2 = Len(String2)

'----------------------------------------
'-- Give 0% match if string length < 2 --
'----------------------------------------
If intLen1 < 2 Then
    FuzzyPercent = 0
    Exit Function
End If

intTotScore = 0                   'initialise total possible score
intScore = 0                      'initialise current score

'--------------------------------------------------------
'-- If Algorithm = 1 or 3, Search for single characters --
'--------------------------------------------------------
If (Algorithm And 1) <> 0 Then
    FuzzyAlg1 String1, String2, intScore, intTotScore
    If intLen1 < intLen2 Then FuzzyAlg1 String2, String1, intScore, intTotScore
End If

'-----------------------------------------------------------
'-- If Algorithm = 2 or 3, Search for pairs, triplets etc. --
'-----------------------------------------------------------
If (Algorithm And 2) <> 0 Then
    FuzzyAlg2 String1, String2, intScore, intTotScore
    If intLen1 < intLen2 Then FuzzyAlg2 String2, String1, intScore, intTotScore
End If

FuzzyPercent = intScore / intTotScore

End Function
Private Sub FuzzyAlg1(ByVal String1 As String, _
                      ByVal String2 As String, _
                      ByRef Score As Integer, _
                      ByRef TotScore As Integer)
Dim intLen1 As Integer, intPos As Integer, intPtr As Integer, intStartPos As Integer

intLen1 = Len(String1)
TotScore = TotScore + intLen1              'update total possible score
intPos = 0
For intPtr = 1 To intLen1
    intStartPos = intPos + 1
    intPos = InStr(intStartPos, String2, Mid$(String1, intPtr, 1))
    If intPos > 0 Then
        If intPos > intStartPos + 3 Then     'No match if char is > 3 bytes away
            intPos = intStartPos
        Else
            Score = Score + 1          'Update current score
        End If
    Else
        intPos = intStartPos
    End If
Next intPtr
End Sub
Private Sub FuzzyAlg2(ByVal String1 As String, _
                        ByVal String2 As String, _
                        ByRef Score As Integer, _
                        ByRef TotScore As Integer)
Dim intCurLen As Integer, intLen1 As Integer, intTo As Integer, intPtr As Integer, intPos As Integer
Dim strWork As String

intLen1 = Len(String1)
For intCurLen = 2 To intLen1
    strWork = String2                          'Get a copy of String2
    intTo = intLen1 - intCurLen + 1
    TotScore = TotScore + Int(intLen1 / intCurLen)  'Update total possible score
    For intPtr = 1 To intTo Step intCurLen
        intPos = InStr(strWork, Mid$(String1, intPtr, intCurLen))
        If intPos > 0 Then
            Mid$(strWork, intPos, intCurLen) = String$(intCurLen, &H0) 'corrupt found string
            Score = Score + 1     'Update current score
        End If
    Next intPtr
Next intCurLen

End Sub

Function FuzzyVLookup(ByVal LookupValue As String, _
                      ByVal TableArray As Range, _
                      ByVal IndexNum As Integer, _
                      Optional NFPercent As Single = 0.05, _
                      Optional Rank As Integer = 1, _
                      Optional Algorithm As Integer = 3, _
                      Optional AdditionalCols As Integer = 0) As Variant
'********************************************************************************
'** Function to Fuzzy match LookupValue with entries in                        **
'** column 1 of table specified by TableArray.                                 **
'** TableArray must specify the top left cell of the range to be searched      **
'** The function stops scanning the table when an empty cell in column 1       **
'** is found.                                                                  **
'** For each entry in column 1 of the table, FuzzyPercent is called to match   **
'** LookupValue with the Table entry.                                          **
'** 'Rank' is an optional parameter which may take any value > 0               **
'**        (default 1) and causes the function to return the 'nth' best        **
'**         match (where 'n' is defined by 'Rank' parameter)                   **
'** If the 'Rank' match percentage < NFPercent (Default 5%), #N/A is returned. **
'** IndexNum is the column number of the entry in TableArray required to be    **
'** returned, as follows:                                                      **
'** If IndexNum > 0 and the 'Rank' percentage match is >= NFPercent            **
'**                 (Default 5%) the column entry indicated by IndexNum is     **
'**                 returned.                                                  **
'** if IndexNum = 0 and the 'Rank' percentage match is >= NFPercent            **
'**                 (Default 5%) the offset row (starting at 1) is returned.   **
'**                 This value can be used directly in the 'Index' function.   **
'**                                                                            **
'** Algorithm can take one of the following values:                            **
'** Algorithm = 1:                                                             **
'**     This algorithm is best suited for matching mis-spellings.              **
'**     For each character in 'String1', a search is performed on 'String2'.   **
'**     The search is deemed successful if a character is found in 'String2'   **
'**     within 3 characters of the current position.                           **
'**     A score is kept of matching characters which is returned as a          **
'**     percentage of the total possible score.                                **
'** Algorithm = 2:                                                             **
'**     This algorithm is best suited for matching sentences, or               **
'**     'firstname lastname' compared with 'lastname firstname' combinations   **
'**     A count of matching pairs, triplets, quadruplets etc. in 'String1' and **
'**     'String2' is returned as a percentage of the total possible.           **
'** Algorithm = 3: Both Algorithms 1 and 2 are performed.                      **
'********************************************************************************
Dim R As Range

Dim strListString As String
Dim strWork As String

Dim sngMinPercent As Single
Dim sngWork As Single
Dim sngCurPercent  As Single
Dim intBestMatchPtr As Integer
Dim intRankPtr As Integer
Dim intRankPtr1 As Integer
Dim I As Integer

Dim lEndRow As Long

Dim udRankData() As RankInfo

Dim vCurValue As Variant

'--------------------------------------------------------------
'--    Validation                                            --
'--------------------------------------------------------------

LookupValue = LCase$(Application.Trim(LookupValue))

If IsMissing(NFPercent) Then
    sngMinPercent = 0.05
Else
    If (NFPercent <= 0) Or (NFPercent > 1) Then
        FuzzyVLookup = "*** 'NFPercent' must be a percentage > zero ***"
        Exit Function
    End If
    sngMinPercent = NFPercent
End If

If Rank < 1 Then
    FuzzyVLookup = "*** 'Rank' must be an integer > 0 ***"
    Exit Function
End If

ReDim udRankData(1 To Rank)

lEndRow = TableArray.Rows.Count
If VarType(TableArray.Cells(lEndRow, 1).Value) = vbEmpty Then
    lEndRow = TableArray.Cells(lEndRow, 1).End(xlUp).Row
End If

'---------------
'-- Main loop --
'---------------
For Each R In Range(TableArray.Cells(1, 1), TableArray.Cells(lEndRow, 1))
    vCurValue = ""
    For I = 0 To AdditionalCols
        vCurValue = vCurValue & R.Offset(0, I).Text
    Next I
    If VarType(vCurValue) = vbString Then
        strListString = LCase$(Application.Trim(vCurValue))
        
        '------------------------------------------------
        '-- Fuzzy match strings & get percentage match --
        '------------------------------------------------
        sngCurPercent = FuzzyPercent(String1:=LookupValue, _
                                     String2:=strListString, _
                                     Algorithm:=Algorithm, _
                                     Normalised:=True)
        
        If sngCurPercent >= sngMinPercent Then
            '---------------------------
            '-- Store in ranked array --
            '---------------------------
            For intRankPtr = 1 To Rank
                If sngCurPercent > udRankData(intRankPtr).Percentage Then
                    For intRankPtr1 = Rank To intRankPtr + 1 Step -1
                        With udRankData(intRankPtr1)
                            .Offset = udRankData(intRankPtr1 - 1).Offset
                            .Percentage = udRankData(intRankPtr1 - 1).Percentage
                        End With
                    Next intRankPtr1
                    With udRankData(intRankPtr)
                        .Offset = R.Row
                        .Percentage = sngCurPercent
                    End With
                    Exit For
                End If
            Next intRankPtr
        End If
        
    End If
Next R

If udRankData(Rank).Percentage < sngMinPercent Then
    '--------------------------------------
    '-- Return '#N/A' if below NFPercent --
    '--------------------------------------
    FuzzyVLookup = CVErr(xlErrNA)
Else
    intBestMatchPtr = udRankData(Rank).Offset - TableArray.Cells(1, 1).Row + 1
    If IndexNum > 0 Then
        '-----------------------------------
        '-- Return column entry specified --
        '-----------------------------------
        FuzzyVLookup = TableArray.Cells(intBestMatchPtr, IndexNum)
    Else
        '-----------------------
        '-- Return offset row --
        '-----------------------
        FuzzyVLookup = intBestMatchPtr
    End If
End If
End Function
Function FuzzyHLookup(ByVal LookupValue As String, _
                      ByVal TableArray As Range, _
                      ByVal IndexNum As Integer, _
                      Optional NFPercent As Single = 0.05, _
                      Optional Rank As Integer = 1, _
                      Optional Algorithm As Integer = 3) As Variant
'********************************************************************************
'** Function to Fuzzy match LookupValue with entries in                        **
'** row 1 of table specified by TableArray.                                    **
'** TableArray must specify the top left cell of the range to be searched      **
'** The function stops scanning the table when an empty cell in row 1          **
'** is found.                                                                  **
'** For each entry in row 1 of the table, FuzzyPercent is called to match      **
'** LookupValue with the Table entry.                                          **
'** 'Rank' is an optional parameter which may take any value > 0               **
'**        (default 1) and causes the function to return the 'nth' best        **
'**         match (where 'n' is defined by 'Rank' parameter)                   **
'** If the 'Rank' match percentage < NFPercent (Default 5%), #N/A is returned. **
'** IndexNum is the row number of the entry in TableArray required to be       **
'** returned, as follows:                                                      **
'** If IndexNum > 0 and the 'Rank' percentage match is >= NFPercent            **
'**                 (Default 5%) the row entry indicated by IndexNum is        **
'**                 returned.                                                  **
'** if IndexNum = 0 and the 'Rank' percentage match is >= NFPercent            **
'**                 (Default 5%) the offset col (starting at 0) is returned.   **
'**                 This value can be used directly in the 'OffSet' function.  **
'**                                                                            **
'** Algorithm can take one of the following values:                            **
'** Algorithm = 1:                                                             **
'**     For each character in 'String1', a search is performed on 'String2'.   **
'**     The search is deemed successful if a character is found in 'String2'   **
'**     within 3 characters of the current position.                           **
'**     A score is kept of matching characters which is returned as a          **
'**     percentage of the total possible score.                                **
'** Algorithm = 2:                                                             **
'**     A count of matching pairs, triplets, quadruplets etc. in 'String1' and **
'**     'String2' is returned as a percentage of the total possible.           **
'** Algorithm = 3: Both Algorithms 1 and 2 are performed.                      **
'********************************************************************************
Dim R As Range

Dim strListString As String
Dim strWork As String

Dim sngMinPercent As Single
Dim sngWork As Single
Dim sngCurPercent  As Single

Dim intBestMatchPtr As Integer
Dim intPtr As Integer
Dim intRankPtr As Integer
Dim intRankPtr1 As Integer

Dim iEndCol As Integer

Dim udRankData() As RankInfo

Dim vCurValue As Variant
'--------------------------------------------------------------
'--    Validation                                            --
'--------------------------------------------------------------
LookupValue = LCase$(Application.Trim(LookupValue))

If IsMissing(NFPercent) Then
    sngMinPercent = 0.05
Else
    If (NFPercent <= 0) Or (NFPercent > 1) Then
        FuzzyHLookup = "*** 'NFPercent' must be a percentage > zero ***"
        Exit Function
    End If
    sngMinPercent = NFPercent
End If

If Rank < 1 Then
    FuzzyHLookup = "*** 'Rank' must be an integer > 0 ***"
    Exit Function
End If

ReDim udRankData(1 To Rank)
'**************************
iEndCol = TableArray.Columns.Count
If VarType(TableArray.Cells(1, iEndCol).Value) = vbEmpty Then
    iEndCol = TableArray.Cells(1, iEndCol).End(xlToLeft).Column
End If

'---------------
'-- Main loop --
'---------------
For Each R In Range(TableArray.Cells(1, 1), TableArray.Cells(1, iEndCol))
    vCurValue = R.Value
    If VarType(vCurValue) = vbString Then
        strListString = LCase$(Application.Trim(vCurValue))
        
        '------------------------------------------------
        '-- Fuzzy match strings & get percentage match --
        '------------------------------------------------
        sngCurPercent = FuzzyPercent(String1:=LookupValue, _
                                     String2:=strListString, _
                                     Algorithm:=Algorithm, _
                                     Normalised:=True)
        
        If sngCurPercent >= sngMinPercent Then
            '---------------------------
            '-- Store in ranked array --
            '---------------------------
            For intRankPtr = 1 To Rank
                If sngCurPercent > udRankData(intRankPtr).Percentage Then
                    For intRankPtr1 = Rank To intRankPtr + 1 Step -1
                        With udRankData(intRankPtr1)
                            .Offset = udRankData(intRankPtr1 - 1).Offset
                            .Percentage = udRankData(intRankPtr1 - 1).Percentage
                        End With
                    Next intRankPtr1
                    With udRankData(intRankPtr)
                        .Offset = R.Column
                        .Percentage = sngCurPercent
                    End With
                    Exit For
                End If
            Next intRankPtr
        End If
        
    End If
Next R

If udRankData(Rank).Percentage < sngMinPercent Then
    '--------------------------------------
    '-- Return '#N/A' if below NFPercent --
    '--------------------------------------
    FuzzyHLookup = CVErr(xlErrNA)
Else
    intBestMatchPtr = udRankData(Rank).Offset - TableArray.Cells(1, 1).Column + 1
    If IndexNum > 0 Then
        '-----------------------------------
        '-- Return row entry specified --
        '-----------------------------------
        FuzzyHLookup = TableArray.Cells(IndexNum, intBestMatchPtr)
    Else
        '-----------------------
        '-- Return offset col --
        '-----------------------
        FuzzyHLookup = intBestMatchPtr
    End If
End If
End Function

Three Fuzzy matching UDF’s are available, FUZZYVLOOKUP, FUZZYHLOOKUP and FUZZYPERCENT.

FUZZYVLOOKUP is the ‘fuzzy’ equivalent of ‘VLOOKUP and has the following parameters:

Lookupvalue
The value to search in the first column of the table array

Tablearray
One 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.

Indexnum
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.

NFpercent
The 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.
The higher the percentage specified, the higher the confidence level in the returned result.
Default: 5%

Rank
An optional parameter which may take any value > 0 and causes the function to return the specified ranking best match.
Default: 1

Algorithm
Defines the algorithm to be used for matching strings. Valid values are 1, 2 or 3:
Algorithm = 1
This algorithm is best suited for matching mis-spellings.
For each character in 'String1', a search is performed on 'String2'.
The search is deemed successful if a character is found in 'String2' within 3 characters of the current position.
A score is kept of matching characters which is returned as a percentage of the total possible score.
Algorithm = 2
This algorithm is best suited for matching sentences, or 'firstname lastname' compared with 'lastname firstname' combinations.
A count of matching pairs, triplets, quadruplets etc. in 'String1' and 'String2' is returned as a percentage of the total possible.
Algorithm = 3: Both Algorithms 1 and 2 are performed.
Default: 3

Additionalcols
Defines the number of subsequent columns after the first within the table array to be concatenated with the first column prior to matching.
Default: 0

FUZZYHLOOKUP is the ‘fuzzy’ equivalent of ‘HLOOKUP and has the following parameters, descriptions as for FuzzyVLookup:
LookupValue
TableArray
IndexNum
NFPercent
Rank
Algorithm

FUZZYPERCENT is at the heart of FuzzyVLookup and FuzzyHLookup, and returns the percentage match of the two strings supplied. This UDF can be called independently. It has the following parameters:

String1
The first string to be matched. FuzzyVLookup will pass the lookup value supplied (but normalised) as this parameter.

String2
The second string to be matched. FuzzyVLookup will pass each of the lookup table strings supplied (but normalised) as this parameter.

Algorithm
Algorithm to be used. See FuzzyVLookup for further details.
Default: 3

Normalised
Boolean 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.
Default: False


EXAMPLES.

In the following example sheet,
Column A is the lookup table
Column B contain lookup values
Column C contains FuzzyVLookup formulae using algorithm 1
Column D contains FuzzyVLookup formulae using algorithm 2
Column E contains FuzzyVLookup formulae using algorithm 3.
Column F contains the FuzzyPercent result of column B versus column C using algorithm 1
Column G contains the FuzzyPercent result of column B versus column D using algorithm 2
Column H contains the FuzzyPercent result of column B versus column E using algorithm 3

******** ******************** ************************************************************************><center>

Microsoft Excel - Fuzzy Examples.xls ___Running: 11.0 : OS = Windows XP <tbody> </tbody>
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout <form name="formCb605117"><input *******="window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);" type="button" value="Copy Formula" name="btCb942116"></form> <tbody> </tbody>
<select onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name="sltNb447362"><option value="=fuzzyvlookup($B3,$A:$A,1,,,C$2)" selected="">C3</option><option value="=fuzzyvlookup($B3,$A:$A,1,,,D$2)">D3</option><option value="=fuzzyvlookup($B3,$A:$A,1,,,E$2)">E3</option><option value="=fuzzypercent($B3,C3,F$2)">F3</option><option value="=fuzzypercent($B3,D3,G$2)">G3</option><option value="=fuzzypercent($B3,E3,H$2)">H3</option><option value="=fuzzyvlookup($B4,$A:$A,1,,,C$2)">C4</option><option value="=fuzzyvlookup($B4,$A:$A,1,,,D$2)">D4</option><option value="=fuzzyvlookup($B4,$A:$A,1,,,E$2)">E4</option><option value="=fuzzypercent($B4,C4,F$2)">F4</option><option value="=fuzzypercent($B4,D4,G$2)">G4</option><option value="=fuzzypercent($B4,E4,H$2)">H4</option><option value="=fuzzyvlookup($B5,$A:$A,1,,,C$2)">C5</option><option value="=fuzzyvlookup($B5,$A:$A,1,,,D$2)">D5</option><option value="=fuzzyvlookup($B5,$A:$A,1,,,E$2)">E5</option><option value="=fuzzypercent($B5,C5,F$2)">F5</option><option value="=fuzzypercent($B5,D5,G$2)">G5</option><option value="=fuzzypercent($B5,E5,H$2)">H5</option><option value="=fuzzyvlookup($B6,$A:$A,1,,,C$2)">C6</option><option value="=fuzzyvlookup($B6,$A:$A,1,,,D$2)">D6</option><option value="=fuzzyvlookup($B6,$A:$A,1,,,E$2)">E6</option><option value="=fuzzypercent($B6,C6,F$2)">F6</option><option value="=fuzzypercent($B6,D6,G$2)">G6</option><option value="=fuzzypercent($B6,E6,H$2)">H6</option></select> = <input size="80" value="=fuzzyvlookup($B3,$A:$A,1,,,C$2)" name="txbFb150492"> <tbody> <form name="formFb202339"></form> </tbody>
	<center>A</center>	<center>B</center>	<center>C</center>	<center>D</center>	<center>E</center>	<center>F</center>	<center>G</center>	<center>H</center>
<center>1</center>			FuzzyVLookup - Algorithm	FuzzyPercent - Algorithm
<center>2</center>			1	2	3	1	2	3
<center>3</center>	Paul McCartney	George Bush	George Harris	George Harris	George Harris	66.67%	42.86%	51.52%
<center>4</center>	George Harris	Gorge hrs	George Harris	George Harris	George Harris	54.55%	21.05%	33.33%
<center>5</center>	Ringo Starr	Starr,Ringo	Chuck Norris	Ringo Starr	Ringo Starr	17.39%	44.44%	31.03%
<center>6</center>	John Lennon	Bush, George	Bruce Lee	George Harris	George Harris	25.00%	31.91%	26.39%
<center>7</center>	Tony Blair
<center>8</center>	George Clooney
<center>9</center>	Chuck Norris
<center>10</center>	Claude Van Damme
<center>11</center>	Bruce Lee
Fuzzy Match Examples <tbody> </tbody>

<tbody>
</tbody>

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

formula in C3 and copied down and across to E6 is =fuzzyvlookup($B3,$A:$A,1,,,C$2)
Formula in F3 and copied down and across to H6 is =fuzzypercent($B3,C3,F$2)

 

[winstond]

Hi,

I've spent about an hour trying to get this to work. I have all my cells in the right place, but keep getting an mudRankData - function not defined error message. Any idea what might be causing this? Lookup data starts in A:3

I'm pasting your code into Excel 2011 VBA Macro on MacOS.

		1	2	3	1	2	3
Abbotskerswell Cricket Club	53 Degrees
Abingdon Vale Cricket Club	53 Degrees
Accrington Cricket Club	ABC
Adlington Cricket Club	ABC
Aigburth Cricket Club	ABC
Alconbury Sports & Social Club	ABC
Alderley Edge Cricket Club	ABC
Alexandra Park Cricket Club	ABC
Alfreton Cricket Club	ABC
Allscott Cricket Club	ABC
Alnwick Cricket Club	ABC
Alrewas Cricket Club	ABC
Alvechurch & Hopwood Cricket Club	ABC
Alveley Cricket Club	ABC
Annfield Plain Cricket Club Ltd	ABC
Ashington Cricket Club	ABC
Ashton on Mersey Cricket & Tennis Club	Aberdeen Exhibition Centre
Ashton-Under-Lyne Cricket,Bowling & Tennis Club	Aberdeen Exhibition Centre
Astley Bridge Cricket Club	Aberdeen Exhibition Centre
Aston Manor Cricket Club	Aberdeen Exhibition Centre
Astro Equipment & Maintenance Services	Aberdeen Exhibition Centre
Astro-Bar	Aberystwyth Arts Centre
Atherton Cricket Club	Academy Leicester
Attock Community Sports & Cricket Club	Academy Leicester
Audley Cricket Club	Academy Leicester
Babbacombe Cricket Club	Academy Leicester
Bacup Cricket Club	Academy Leicester
Banbury Cricket Club	Academy Leicester
Barlaston Cricket Club	Academy Leicester
Barnard Castle Cricket Club	Academy Leicester
Barnards Green Cricket Club	Academy Leicester
Barnes Criket Club	Academy Leicester
Barnes Squash Club	Academy Leicester
Barnt Green Cricket Club	Alban Arena
Barnton Cricket Club	Alban Arena
Barrow Cricket Club	Alexandra Palace
Barrow Cricket Club (Chester)	Alexandra Palace

<colgroup><col><col><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>

 

[al_b_cnu]

Hi Winstond

Assuming that you copied the code in its entirity to a new module, I'm guessing that the issue is 'MacOS' here - it seems to be complaining about a Type declaration (which is after the comments but before the subs / functions):

Type RankInfo
    Offset As Long
    Percentage As Single
End Type

Dim mudRankData() As RankInfo

Hmm where to go from here? - maybe a cut down version for MacOS?

 

[winstond]

As i'm relatively new to VB. I'm assuming your saying this ownt work on MacOS without major modifications?

 

[al_b_cnu]

Afraid so, but fear not, it isnt too big a job to produce a simplified version - the harness would need rewriting which I'd unfortunately not be able to test

 

[winstond]

OK, If you write it.. I can test it