VBA issue trying to match sets of data

[turbonerds]

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.

[Doug Robbins]

Assuming that there are column headers in A1 and B1 and that the number that you want to match is in B2, the following will display a message box telling you in which cell the numbers differ by just 1 digit in any position within the string:

[vba]Dim i As Long
Dim NumFind
With Worksheets(1).Range("A1")
NumFind = Val(Replace(.Offset(1, 1), "-", ""))
For i = 1 To .CurrentRegion.Rows.Count - 1
If Val(Replace(Val(Replace(.Offset(i, 0), "-", "")) - NumFind, "0", "")) = 1 Then
MsgBox "The nearest match is " & .Offset(i, 0) & " in cell A" & i + 1
Exit For
End If
Next i
End With
[/vba]

[judy56]

This tutorial for linear and 2D barcodes generation in Excel may help you. As far as I am concerned, it's easy to generate barcodes in Microsoft Excel with a Excel barcode addin.