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]

But ... do you have a problem?

 

[nniedzielski]

yes, when i tried to copy it down in VBA it said it could not due to changing the array, essentially id like to copy it down and then cut and paste values in if possible

 

[DanteAmor]

Run the macro and then simply drag the array formula.

 

[nniedzielski]

Yes, but i want that to happen in VBA

 

[DanteAmor]

Try this

Sub compareLists()
  Dim lr As Long, sh As Worksheet, cad1 As String, cad2 As String, i As Long
  Set sh = Sheets("Yard")
  lr = sh.Range("A" & Rows.Count).End(xlUp).Row
  cad1 = "'" & sh.Name & "'!" & sh.Range("$C$2:$C$" & lr).Address
  cad2 = "'" & sh.Name & "'!" & sh.Range("$A$2:$A$" & lr).Address
  For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
    Range("D" & i).FormulaArray = _
      Replace(Replace("=INDEX(#,SUMPRODUCT((IF(--ISNUMBER(FIND(@,B" & i & "))," & _
      "LEN(@))=(MAX(IF(--ISNUMBER(FIND(@,B" & i & ")),LEN(@)))))*(ROW(@)))-1)", "#", cad1), "@", cad2)
  Next
End Sub

 

[nniedzielski]

Hi Dante-

Your formula has been working, however i have been pouring over the 2 lists the last day to see which numbers did not get found by the formula

The trailers above were what is in the yard list, and the list on the right is the Hoja2, but all of these and ones just like it returned #VALUE! instead, is there a way to strengthen the formula to catch instances like these? These are very common in the list.

 

[DanteAmor]

Try this

{=IFERROR(INDEX(Yard!$C$2:$C$6,SUMPRODUCT((IF(--ISNUMBER(FIND(Yard!$A$2:$A$6,B7)),LEN(Yard!$A$2:$A$6))=(MAX(IF(--ISNUMBER(FIND(Yard!$A$2:$A$6,B7)),LEN(Yard!$A$2:$A$6)))))*(ROW(Yard!$A$2:$A$6)))-1),VLOOKUP("*"&B7&"*",Yard!$A$2:$C$6,3,0))}

 

[nniedzielski]

how would that translate to VBA?

 

[DanteAmor]

Try this

Sub compareLists()
  Dim lr As Long, sh As Worksheet, cad1 As String, cad2 As String, cad3 As String, i As Long
  Set sh = Sheets("Yard")
  lr = sh.Range("A" & Rows.Count).End(xlUp).Row
  cad1 = "'" & sh.Name & "'!" & sh.Range("$C$2:$C$" & lr).Address
  cad2 = "'" & sh.Name & "'!" & sh.Range("$A$2:$A$" & lr).Address
  cad3 = "'" & sh.Name & "'!" & sh.Range("$A$2:$C$" & lr).Address
  For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
    Range("D" & i).FormulaArray = _
      Replace(Replace(Replace("=IFERROR(INDEX(#,SUMPRODUCT((IF(--ISNUMBER(FIND(@,B" & i & "))," & _
      "LEN(@))=(MAX(IF(--ISNUMBER(FIND(@,B" & i & ")),LEN(@)))))*(ROW(@)))-1)," & _
      "VLOOKUP(""*""&B" & i & "&""*"",|,3,0))", "#", cad1), "@", cad2), "|", cad3)
  Next
End Sub

 

[nniedzielski]

i got this error message: