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,

 

[nniedzielski]

 

[DanteAmor]

Try this

Sub compareLists2()
  Dim lr As Long
  lr = Sheets("Yard Check").Range("A" & Rows.Count).End(xlUp).Row
  Range("D2").FormulaArray = _
    Replace("=INDEX('Yard Check'!$C$2:$C$#," & _
    "SUMPRODUCT((IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$#,B2))," & _
    "LEN('Yard Check'!$A$2:$A$#))=(MAX(IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$#,B2))," & _
    "LEN('Yard Check'!$A$2:$A$#)))))*(ROW('Yard Check'!$A$2:$A$#)))-1)", "#", lr)
End Sub

 

[nniedzielski]

Same error message.

I need to note, that when i adjusted the formula, it did return a result, however all the results were wrong, for example on Yard Check it would say Inbound, but the formula would return Outbound.

 

[DanteAmor]

I guess it's a VBA limitation, it doesn't allow me to put more than 99 in the macro.

This works:

Sub compareLists4()
  Dim lr As Long
  lr = Sheets("Yard Check").Range("A" & Rows.Count).End(xlUp).Row
  Range("D2").FormulaArray = _
    "=INDEX('Yard Check'!$C$2:$C$99," & _
    "SUMPRODUCT((IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$99,B2))," & _
    "LEN('Yard Check'!$A$2:$A$99))=(MAX(IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$99,B2))," & _
    "LEN('Yard Check'!$A$2:$A$99)))))*(ROW('Yard Check'!$A$2:$A$99)))-1)"
End Sub

But this sends the error "unable to set formulaarray property of range class"

Sub compareLists4()
  Dim lr As Long
  lr = Sheets("Yard Check").Range("A" & Rows.Count).End(xlUp).Row
  Range("D2").FormulaArray = _
    "=INDEX('Yard Check'!$C$2:$C$100," & _
    "SUMPRODUCT((IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$100,B2))," & _
    "LEN('Yard Check'!$A$2:$A$100))=(MAX(IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$100,B2))," & _
    "LEN('Yard Check'!$A$2:$A$100)))))*(ROW('Yard Check'!$A$2:$A$100)))-1)"
End Sub

Apparently it is the size of the formula.

You can change the name of your sheet "Yard check" to "Yard"

Try this:

Sub compareLists4()
  Dim lr As Long
  lr = Sheets("Yard").Range("A" & Rows.Count).End(xlUp).Row
  Range("D2").FormulaArray = _
    "=INDEX('Yard'!C2:C100," & _
    "SUMPRODUCT((IF(--ISNUMBER(FIND('Yard'!A2:A100,B2))," & _
    "LEN('Yard'!A2:A100))=(MAX(IF(--ISNUMBER(FIND('Yard'!A2:A100,B2))," & _
    "LEN('Yard'!A2:A100)))))*(ROW('Yard'!A2:A100)))-1)"
End Sub

An try this again:

Sub compareLists()
  Dim lr As Long, sh As Worksheet, cad1 As String, cad2 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
  Range("D2").FormulaArray = _
    Replace(Replace("=INDEX(#,SUMPRODUCT((IF(--ISNUMBER(FIND(@,B2))," & _
    "LEN(@))=(MAX(IF(--ISNUMBER(FIND(@,B2)),LEN(@)))))*(ROW(@)))-1)", "#", cad1), "@", cad2)
End Sub

 

[nniedzielski]

Maybe im not quite understanding what the formula is trying to do. But the lists are going to vary and they are always around 1000 rows a piece.

 

[nniedzielski]

Changing Yard Check to Yard an using this worked

Sub compareLists()
  Dim lr As Long, sh As Worksheet, cad1 As String, cad2 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
  Range("D2").FormulaArray = _
    Replace(Replace("=INDEX(#,SUMPRODUCT((IF(--ISNUMBER(FIND(@,B2))," & _
    "LEN(@))=(MAX(IF(--ISNUMBER(FIND(@,B2)),LEN(@)))))*(ROW(@)))-1)", "#", cad1), "@", cad2)
End Sub

do you mind explaining how the code works so i can fix it if issues happen?

thank you a million for your patience today.

 

[DanteAmor]

I guess you have something like this in the "Yard Check" sheet

Book1
	A	B	C
1
2	53106	data2	value2
3	1	data3	value3
4	122	data4	value4
5	58672	data5	value5
6	830389	data6	value6
Yard Check

If you run the macro on sheet2 the result will be like this:

Book1
	A	B	C	D
1
2		C53106		value2
3		E001
4		JM122
5		58672S
6		UMXU830389
Sheet2

Cell Formulas
Range	Formula
D2	D2	{=INDEX('Yard Check'!$C$2:$C$6,SUMPRODUCT((IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$6,B2)),LEN('Yard Check'!$A$2:$A$6))=(MAX(IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$6,B2)),LEN('Yard Check'!$A$2:$A$6)))))*(ROW('Yard Check'!$A$2:$A$6)))-1)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

What the formula array does is search for each of the values in the list within cell B2.
That is, it takes the value "53106" and searches for it within "C53106",
takes the value "1" and searches for it within the value "C53106",
takes the value "122" and searches for it within "C53106",
takes the value "58672" and searches for it within "C53106",
takes the value "830389" and searches for it within "C53106"

In the previous example the values "53106" and "1" exist within the value "53106", but the formula checks which of the 2 values has a maximum of matching characters, in this case, the value "53106" matches 5 characters.

Then take the row number and with the Index formula get the data from column C.

-------------------------------------------

Sub compareLists()
  Dim lr As Long, sh As Worksheet, cad1 As String, cad2 As String
  Set sh = Sheets("Yard")
  'Get the last row of column A
  lr = sh.Range("A" & Rows.Count).End(xlUp).Row
  'Put in the variable cad1 the name of the sheet and the range of cells in column C
  cad1 = "'" & sh.Name & "'!" & sh.Range("$C$2:$C$" & lr).Address
  'Put in the variable cad2 the name of the sheet and the range of cells in column A
  cad2 = "'" & sh.Name & "'!" & sh.Range("$A$2:$A$" & lr).Address

  'Put the formula array, but replace # with the cad1 and @ with the cad2
  Range("D2").FormulaArray = _
    Replace(Replace("=INDEX(#,SUMPRODUCT((IF(--ISNUMBER(FIND(@,B2))," & _
    "LEN(@))=(MAX(IF(--ISNUMBER(FIND(@,B2)),LEN(@)))))*(ROW(@)))-1)", "#", cad1), "@", cad2)
End Sub

'Then
'=INDEX(#,SUMPRODUCT
Result:
'=INDEX("'Yard'!$C$2:$C$6,SUMPRODUCT

Then
SUMPRODUCT((IF(--ISNUMBER(FIND(@,B2))
Result
SUMPRODUCT((IF(--ISNUMBER(FIND('Yard Check'!$A$2:$A$6,B2))

 

[nniedzielski]

If I wanted to copy this down from D2 to the bottom to match up with C2 row length, how could i do that and not bust up the array?

 

[DanteAmor]

I do not understand what you need.
If you drag the formula in cell D2 down, is what you need?

 

[nniedzielski]

yes