Compare Two Lists

nniedzielski

Active Member
Joined
Jan 8, 2016
Messages
386
I am using V Lookup to compare two lists:

VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
What do you have in B2?

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

nniedzielski

Active Member
Joined
Jan 8, 2016
Messages
386
B2 is list A, Column A is list B.

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
I did not understand.
Do you want to find this data C53106 in the following list?

xl2bb.xlam
A
1
253106
31
4122
558672
6830389
Yard Check


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

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

xl2bb.xlam
AB
1
2C53106
3E001
4JM122
558672S
6UMXU830389
Hoja2
 

nniedzielski

Active Member
Joined
Jan 8, 2016
Messages
386
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
Try this

VBA Code:
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

Active Member
Joined
Jan 8, 2016
Messages
386
This did not work, simply got a #VALUE!

1574713491382.png


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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
I guess you have something like this in the "Yard Check" sheet

xl2bb.xlam
ABC
1
253106data2value2
31data3value3
4122data4value4
558672data5value5
6830389data6value6
Yard Check



I also assume that on sheet2 you have something like this.
xl2bb.xlam
ABCD
1
2C53106
3E001
4JM122
558672S
6UMXU830389
Sheet2


If you run the macro on sheet2 the result will be like this:
xl2bb.xlam
ABCD
1
2C53106
3E001
4JM122
558672S
6UMXU830389
Sheet2
Cell Formulas
Range(s)Formula
D2D2{=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

Active Member
Joined
Jan 8, 2016
Messages
386
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,856
Office Version
2007
Platform
Windows
Try this

VBA Code:
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
 

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top