Match limited # characters in cell not exact (Excel 97)

Topic

New Reply

I have 2 sources of data that I am trying to match. One is from an Access database, the other from an interface to a company system. Both contain unique case ID fields. The fields could be returned in formats that are not exactly the same. E.g. db= 0123408AUG03 sys= 1234-08AUG03. I am creating a sheet that will list everything from the sys, while returning specific information from the database if the IDs match up. The problem is that the formulas I am trying to use rely on an exact, less than, or more than match. I’ve already written some formulas that remove leading zeros and eliminate the dash, if there is one. I now want the evaluation to do something like this: If there are at least 6 consecutive characters that match, return the specified information from the database. I want to use a formula like this because even with the elimination of the leading zeros and the dashs, I still don’t get exact matches using the INDEX and MATCH formulas. Got any ideas?

Reply | Quote

Replies

Not sure if this is what you are looking for. I can see a lot of problems with finding the WRONG item. This code works like match. it takes the form (for example):

=nearmatch(A1,B1:B10,6)

A1 is the value to lookup
b1:b10 is the range
6 is the number of characters to “near match”
It starts with the value in A1 and takes the first 6 characters and looks through each item in the list. If it finds one, it exits giving the row number of the “near match”. If it doesn’t find any it gets the 2-7 characters and looks in each value in col B, then tries with 3-8, etc until the does the “last-6” – last character. if it finds none it gives a #N/A error.

Option Explicit
Function NearMatch(vLookupValue, rng As Range, iNumChars)
    Dim x As Integer
    Dim sSub As String
    Set rng = rng.Columns(1)
    For x = 1 To Len(vLookupValue) - iNumChars + 1
        sSub = Mid(vLookupValue, x, iNumChars)
        For NearMatch = 1 To rng.Cells.Count
            If InStr(rng.Cells(NearMatch), sSub)  0 Then _
                Exit Function
        Next
    Next
    NearMatch = CVErr(xlErrNA)
End Function

This second one, takes the same form

=nearmatch2(A1,B1:B10,6)

A1 is the value to lookup
b1:b10 is the range
6 is the MINIMUM number of characters to “near match”
This starts out checking the entire string (like the MATCH functionality), if it does NOT find an exact match, it takes all but the last character and tries to find a match, it then tries all but the first character, then all but last 2 then, al but 1st and last, then all but first 2, then the missing 3 char variants, then 4 until it gets to the MINIMUM number of characters. If all fail #N/A. It quits once it finds a “near match”

option explicit
Function NearMatch2(vLookupValue, rng As Range, iMinChars)
    Dim x As Integer
    Dim i As Integer
    Dim sSub As String
    Set rng = rng.Columns(1)
    For i = Len(vLookupValue) To iMinChars Step -1
        For x = 1 To Len(vLookupValue) - i + 1
            sSub = Mid(vLookupValue, x, i)
            For NearMatch2 = 1 To rng.Cells.Count
                If InStr(rng.Cells(NearMatch2), sSub)  0 Then _
                    Exit Function
            Next
        Next
    Next
    NearMatch2 = CVErr(xlErrNA)
End Function

This second will find the substring match with the MAX of characters, the first will ONLY use the value you gave it.

The “near match item” can be obtained easily using index:

=index(B1:b10,nearmatch2(A1,B1:B10,6))

so you can compare / “see” if the match is acceptable.

Steve

Reply | Quote

Hi Steve

Sorry it took so long to get back to you, this looks interesting, I will certainly have a close look at and see if it is likely to work for me.

Many Thanks

Braddy

Reply | Quote