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]

that works, awesome

?

 

[nniedzielski]

Hi Dante-

I got some help and was able to break the formula up to get away from the 255 character issue:

Dim lr As Long, i As Long
    Dim sh As Worksheet
    Dim cad1 As String, cad2 As String, cad3 As String, formula1 As String, formula2 As String, formula3 As String
    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
    formula1 = "=IFERROR(IFERROR(INDEX(" & cad1 & ",SUMPRODUCT((IF(--ISNUMBER(FIND(" & cad2 & ",B" & i & ")),LEN(" & cad2 & "))=X_X_X)*(ROW(" & cad2 & ")))-1),Y_Y_Y)"
    formula2 = "(MAX(IF(--ISNUMBER(FIND(" & cad2 & ",B" & i & ")),LEN(" & cad2 & "))))"
    formula3 = "VLOOKUP(" & """*""" & "&B" & i & "&" & """*""" & "," & cad3 & ",3,0)"
    Range("D" & i).FormulaArray = formula1
    Range("D" & i).Replace "X_X_X", formula2
    Range("D" & i).Replace "Y_Y_Y", formula3
    Next

How can I add the portion to this formula where I can get the #N/A to say Kill instead.

thanks,

 

[DanteAmor]

Try this

Sub compareLists_6()
  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("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
    formula1 = "=IFERROR(IFERROR(INDEX(" & cad1 & ",Z_Z_Z),Y_Y_Y),""kill"")"
    formulaZ = "SUMPRODUCT((IF(--ISNUMBER(FIND(" & cad2 & ",B" & i & ")),LEN(" & cad2 & "))=X_X_X)*(ROW(" & cad2 & ")))-1"
    formula2 = "(MAX(IF(--ISNUMBER(FIND(" & cad2 & ",B" & i & ")),LEN(" & cad2 & "))))"
    formula3 = "VLOOKUP(" & """*""" & "&B" & i & "&" & """*""" & "," & cad3 & ",3,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]

Thanks Dante,

I think that you have officially solved that worksheet for me, so now how do i send the same formula back to the other sheet? I want to run this same formula on the Yard worksheet in Column F.

Sheets("Yard").Select
    Set sh = Sheets("Master Export")
    lr = sh.Range("A" & 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("$A$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),""Not on M/E"")"
      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 & ",3,0)"
      Range("F" & i).FormulaArray = formula1
      Range("F" & i).Replace "Z_Z_Z", formulaZ
      Range("F" & i).Replace "X_X_X", formula2
      Range("F" & i).Replace "Y_Y_Y", formula3
    Next

I tried to just replicate and change, but it returned everything as Not on M/E.

Could it be because the columns are in slightly different positions on the other sheet? On the Master Export the trailer number is in B instead of A, but Status is still in C. Not sure if or why that would matter.

 

[DanteAmor]

Any change of sheet or column must modify the macro.
I don't understand where you are putting it or where you should look.

He currently does this:
- Macro runs on active sheet.
- Read the data in column B of the active sheet.
- Look in column A on the "yard" sheet
- Obtained from C of the "yard" sheet.
- Put the result in column "D" of the active sheet.

That way you should explain how your data will be.

 

[nniedzielski]

He currently does this:
- Macro runs on active sheet.
- Read the data in column A of the active sheet.
- Look in column B on the "Master Export" sheet
- Obtained from C of the "Master Export" sheet.
- Put the result in column "D" of the active sheet.


Does this help?

 

[nniedzielski]

Hi Dante-

I wanted to summarize what i need with some images:

I need D2 on the Yard sheet to have the above formula, but in reverse by using the data on the Master Export to retrieve the Status in Column C, using all the same matching and index logic.

This is the "Yard" sheet, and i need the formula you created above to go in D2:

This is the Master Export file that it needs to extract from:

This make sense?

 

[DanteAmor]

He currently does this:
- Macro runs on active sheet.
- Read the data in column A of the active sheet.
- Look in column B on the "Master Export" sheet
- Obtained from C of the "Master Export" sheet.
- Put the result in column "D" of the active sheet.

Try this to run on active sheet (yard)

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]

Everything returned back as "Kill"

 

[nniedzielski]

No matter what i do and how i alter the code, every cell returns back with "Kill". Do you know what the issue could be?

=IFERROR(IFERROR(INDEX(Master Export!$C$2:$C$809,SUMPRODUCT((IF(--ISNUMBER(FIND(Master Export!$B$2:$B$809,A2)),LEN(Master Export!$B$2:$B$809))=(MAX(IF(--ISNUMBER(FIND(Master Export!$B$2:$B$809,A2)),LEN(Master Export!$B$2:$B$809)))))*(ROW(Master Export!$B$2:$B$809)))-1),VLOOKUP("*"&A2&"*",Master Export!$B$2:$C$809,2,0)),"kill")