Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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?
    thanks
    christine

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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):
    <pre>=nearmatch(A1,B1:B10,6)</pre>

    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.

    <pre>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</pre>


    This second one, takes the same form
    <pre>=nearmatch2(A1,B1:B10,6)</pre>

    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"

    <pre>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</pre>


    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:
    <pre>=index(B1:b10,nearmatch2(A1,B1:B10,6))</pre>


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

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

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

    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
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •