Compare Two Lists

[nniedzielski]

I am using V Lookup to compare two lists:

Range("D2").Formula = "=VLOOKUP(B2,'Yard Check'!A:C,3,FALSE)"

I am having a few issues with the comparing of the lists, I have two lists where sometimes the trailer numbers can vary enough to throw off the formula, and I'm wondering how I can code so the Macro finds the trailers to be the same:

Examples:

List A List B
C53106 53106
E001 1
JM122 122
58672S 58672
UMXU830389 830389

Right now when we run the VLookup its returning #N/A, but they are actually the same, how can I get excel VBA to recognize that these are the same? I have tried using "True" instead of "False" but found that didn't really solve the problem.

thank you,

 

[DanteAmor]

The "Yard" sheet had no spaces in the name, so the Apostrophe was not necessary, but "Master Export" has a space, now it is necessary.
Must be:
'Master Export'!

Try this

Sub compareLists_7()
  Dim lr As Long, i As Long, sh As Worksheet
  Dim cad1 As String, cad2 As String, cad3 As String
  Dim formula1 As String, formula2 As String, formula3 As String, formulaZ As String
  Set sh = Sheets("Master Export")
  lr = sh.Range("B" & Rows.Count).End(xlUp).Row
  cad1 = "'" & sh.Name & "'!" & sh.Range("$C$2:$C$" & lr).Address
  cad2 = "'" & sh.Name & "'!" & sh.Range("$B$2:$b$" & lr).Address
  cad3 = "'" & sh.Name & "'!" & sh.Range("$B$2:$C$" & lr).Address
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    formula1 = "=IFERROR(IFERROR(INDEX(" & cad1 & ",Z_Z_Z),Y_Y_Y),""kill"")"
    formulaZ = "SUMPRODUCT((IF(--ISNUMBER(FIND(" & cad2 & ",A" & i & ")),LEN(" & cad2 & "))=X_X_X)*(ROW(" & cad2 & ")))-1"
    formula2 = "(MAX(IF(--ISNUMBER(FIND(" & cad2 & ",A" & i & ")),LEN(" & cad2 & "))))"
    formula3 = "VLOOKUP(" & """*""" & "&A" & i & "&" & """*""" & "," & cad3 & ",2,0)"
    Range("D" & i).FormulaArray = formula1
    Range("D" & i).Replace "Z_Z_Z", formulaZ
    Range("D" & i).Replace "X_X_X", formula2
    Range("D" & i).Replace "Y_Y_Y", formula3
  Next
End Sub

 

[nniedzielski]

perfect, thanks Dante

 

[DanteAmor]

Again with pleasure.