+ Reply to Thread
Results 1 to 9 of 9

Compare ID's and find closest match for each section

Hybrid View

  1. #1
    Registered User
    Join Date
    05-24-2011
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    27

    Compare ID's and find closest match for each section

    I am trying to build a program to compare one main ID to a list and find the closest match overall.

    If the main ID is 3:16:376:000:045 and I have a list of similar format ID's I want to compare each section (between each ":") and find the closest match.

    For example: I have the above main ID and I want to compare it to 3:16:376:100:045 and 3:16:376:000:046 with the highest priority match going from left to right. Meaning that the least priority for a match is on the far right, the last 3 digits of the ID. It would compare the first digit and if it has a match to continue on to the next section of digits. If those are a match to move on to the next set of digits... etc

    Any help is appreciated

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare ID's and find closest match for each section

    This might do it - where do you want the result?

    I used Column M with subject in row 3 and objects in rows 9,10 and 11

    Sub Match4(Subject As String): Dim Object() As String, BestMatch As Range
    Dim r As Long, n As Long, x As Long, I As Integer
     
    r = 9: Do Until Range("M" & r + n) = "": n = n + 1: Loop
    ReDim Object(n + 1, 1)
    For n = 1 To UBound(Object) - 1
    Object(n, 0) = Range("M" & r + n - 1)
    If Subject = Object(n, 0) Then Object(n, 1) = Len(Subject): GoTo GetNext
    For x = 1 To Len(Subject)
    If Mid(Object(n, 0), 1, x) <> Mid(Subject, 1, x) Then Object(n, 1) = x - 1
    Next x
    GetNext: Next n
    For n = 1 To UBound(Object) - 1
    If Object(n, 1) > I Then
    I = Object(n, 1)
    Set BestMatch = Range("M" & r + n - 1)
    End If: Next n
    
    End Sub
    
    Sub Test4(): Dim S As String
    S = Range("M" & 3): Match4 (S)
    End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    05-24-2011
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: Compare ID's and find closest match for each section

    Thanks for the reply. Just have a few questions.
    1. Where is the Object located in the spreadsheet. And is the object the original to be compared to the others?
    2. What if any output is there to indicate a best match?

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare ID's and find closest match for each section

    This should light it up:

    Sub Match4(Subject As String): Dim Object() As String, BestMatch As Range
    Dim r As Long, n As Long, x As Long, I As Integer
     
    r = 9: Do Until Range("M" & r + n) = "": n = n + 1: Loop
    ReDim Object(n + 1, 1)
    For n = 1 To UBound(Object) - 1
    Object(n, 0) = Range("M" & r + n - 1)
    If Subject = Object(n, 0) Then Object(n, 1) = Len(Subject): GoTo GetNext
    For x = 1 To Len(Subject)
    If Mid(Object(n, 0), 1, x) <> Mid(Subject, 1, x) Then
    Object(n, 1) = x - 1: GoTo GetNext: End If
    Next x
    GetNext: Next n
    For n = 1 To UBound(Object) - 1
    If Object(n, 1) > I Then
    I = Object(n, 1)
    Set BestMatch = Range("M" & r + n - 1)
    End If: Next n
    BestMatch.Interior.ColorIndex = 6
    End Sub

  5. #5
    Registered User
    Join Date
    05-24-2011
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: Compare ID's and find closest match for each section

    Great. Thanks for the help. It works like a charm. Now all I need to do is implement it into the larger scheme of things.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare ID's and find closest match for each section

    The subject is the original that is compared to the objects. The objects, below the subject, are carried in a 2D Array, the second dimension to hold the count(Length of the matching string). The best match is pinpointed but, as I said "where do you want it" - we could highlight it and/or post it where you want!

  7. #7
    Registered User
    Join Date
    05-24-2011
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: Compare ID's and find closest match for each section

    Maybe for right now a highlight would be the easiest. Once I figure out 100% what is going on then I can tweak it.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare ID's and find closest match for each section

    You're welcome! I'll be here.

  9. #9
    Registered User
    Join Date
    05-24-2011
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: Compare ID's and find closest match for each section

    xladept: some of the conditions of the project changed and I'm stuck trying to figure out how some of your code works. Now instead of having just one ID check and the program be done. There will be a list of initial ID's to check against a list for best match. (I will attach an example file to show) If there isn't a match it will be moved to a different sheet. If there is a match, the best match will be moved (and the space it occupied deleted) to the cell next to the Mentor or initial ID.

    How would you suggest I modify your code beyond what I have done already so that after the first Mentor has a match, it will move onto the next until all either have a match found or are moved to a separate list?

    Sub Match4(Subject As String): Dim Object() As String, BestMatch As Range
    Dim r As Long, n As Long, x As Long, I As Integer
     
    'r determines where the static id to match is found?
    'the do until makes the program loop until there are no more id's to match to
    
    r = 2: Do Until Range("O" & r + n) = "": n = n + 1: Loop
    ReDim Object(n + 1, 1)
    For n = 1 To UBound(Object) - 1
    Object(n, 0) = Range("O" & r + n - 1)
    If Subject = Object(n, 0) Then Object(n, 1) = Len(Subject): GoTo GetNext
    
    'actual matching process for individual id's
    For x = 1 To Len(Subject)
    If Mid(Object(n, 0), 1, x) <> Mid(Subject, 1, x) Then
    Object(n, 1) = x - 1: GoTo GetNext: End If
    Next x
    GetNext: Next n
    For n = 1 To UBound(Object) - 1
    If Object(n, 1) > I Then
    I = Object(n, 1)
    Set BestMatch = Range("O" & r + n - 1)
    End If: Next n
    
    'if no match is found go to secondary matching protocal
    On Error GoTo err1:
    
    'if there is a match this is the section of code to move to a matched list
    Range("N" & r) = BestMatch
    BestMatch.Delete Shift:=xlUp
    
    'end of normal code and after where error code should be placed
    Exit Sub
    
    
    'this is where code should go for moving ID to new list for random match
    err1: MsgBox ("No match found")
    Worksheets("Geographic only").Range("A" & 1) = Range("M" & 2)
    Range("M" & 2).Delete Shift:=xlUp
    
    End Sub
    
    Sub Test4(): Dim S As String
    S = Range("M" & 2): Match4 (S)
    
    End Sub
    Mentor Matcher.xlsm

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1