Trouble matching sets of data through VBA

Hello everyone! I'm a new user to this forum and a new VBA coder in Excel as well. I love learning about code and enjoy seeing the end result when everything works out.

So I'm working on a new project for work and I've come to a road block...

In column A, I have a bunch of barcode numbers (the actual data set can be up to 1000 different barcodes). I need to code a macro to match one predetermined separate barcode (for example 7089-5687-9083) to the barcodes in column A and then find the closest match.

The rule is that there will always just be one barcode in column A that is only off by one digit. This is the closest match. Any one digit could be off and it's not always the digit in the same order.

In the example below, I need the macro to take predetermined barcode 7089-5682-9023 from Column B and match it to the closest barcode in Column A. In this case, the closest match would be 7089-5782-9023 (the barcode has a 7 in it instead of a 6):

Column A
7089-7496-5464
7089-5782-9023
6089-8846-9832
6089-4562-1515
7089-7951-9493

Column B
7089-5682-9023

Result: 7089-5782-9023 and 7089-5682-9023 are the closest match.

I was thinking about using the MATCH function but I am under the impression that it will look at each barcode as a number, rather than a specific data set. In other words, if 7089-5687-9083 is what I am trying to match, I don't want the macro to assume that 708,956,879,083 is the same as 7089-5687-9083. Especially because the number that is off could either be a few digits higher or lower than what the true value should be.

I was also thinking about using the MID function to go through each number in each string of numbers, but I'm not exactly sure how to do this.

Any help or guidance would be greatly appreciated. I also look forward to learning more about VBA on this forum.

Hi Turbonerds,
try it

Please Login or Register  to view this content.