Compare ID's and find closest match for each section

[marrott2]

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

[xladept]

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

Please Login or Register  to view this content.

[marrott2]

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?

[xladept]

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!

[marrott2]

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

[xladept]

This should light it up:

Please Login or Register  to view this content.

[marrott2]

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.

[xladept]

You're welcome! I'll be here.

[marrott2]

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?

Please Login or Register  to view this content.

Mentor Matcher.xlsm

Thanks again