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]

It is the same problem reviewed in post #14, your excel version does not support the length of the array formula to pass it from vba to the cell.
It is a limitation of excel.
Suggestions:
- You could put the 2 lists on the same sheet, that saves the space of the name sheet.
- Use the formula from post #27 and copy down.

 

[nniedzielski]

I could use the formula from 27, but I need it to look at the whole column A and C, right now they are just looking at 2:6

 

[nniedzielski]

When I alter the formula from #27, it appears to find the wrong trailers, maybe the formula is finding wrong numbers too easily now?

 

[DanteAmor]

It is not recommended to use the entire column in an array formula.

Try

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

It may be convenient to create a macro that performs searches.

 

[nniedzielski]

Help me understand how the array works then please, I have a column of 1032 cells that need to be searched. If the array doesn’t catch everything, how do they get found?

 

[DanteAmor]

Change 600 to a larger number, for example 2000

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

 

[nniedzielski]

Change 600 to a larger number, for example 2000

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

I just went through the 1032 lines and this formula does exactly what i need. I was wondering, i get some #N/A results, which just means the value is not on the Yard list, can we alter the formula to return the value "Kill" instead of #N/A?

thanks again,

 

[DanteAmor]

Try (No tested)

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

 

[nniedzielski]

that works, awesome

 

[DanteAmor]

Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.