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]

What do you have in B2?

And what values do you have in column A of the "Yard Check" sheet?

 

[nniedzielski]

B2 is list A, Column A is list B.

They got kind of jammed together when i submitted the post,

 

[DanteAmor]

I did not understand.
Do you want to find this data C53106 in the following list?

Book1
	A
1
2	53106
3	1
4	122
5	58672
6	830389
Yard Check

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

Or Do you want to find this data 53106 in the following list?

Book1
	A	B
1
2		C53106
3		E001
4		JM122
5		58672S
6		UMXU830389
Hoja2

 

[nniedzielski]

If you were to put in C2 on Hoja2, a Vlookup comparing B2 on Hoja 2 to A2 on Yard Check, that it returns something other than #N/A.

does that help at all?

 

[DanteAmor]

Try this

Sub compareLists()
    Range("D2").FormulaArray = _
        "=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)"
End Sub

 

[nniedzielski]

This did not work, simply got a #VALUE!

However, i am not 100% you understand what i am trying to do.

 

[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

I also assume that on sheet2 you have something like this.

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

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.

 

[nniedzielski]

OK, I was able to make it work recreating the sheets you have above, so thats awesome, thank you.

Now, i need to be able to take this formula and make it work for my workbook. My Yard Check list is Column A, and it can vary in size.

 

[DanteAmor]

Try this

Sub compareLists()
  Dim lr As Long, sh As Worksheet, cad1 As String, cad2 As String
  Set sh = Sheets("Yard Check")
  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