VBA Help PLS! Compare two lists and output unmatched items

[Xceller]

Thank you in advance!

Hi - I have two lists that I have compare to compare.
expected result:
Mark items matched in next to them, output unmatched items in column "J"

I started the code below. It properly marks the correct items, but I have trouble outputting them in Column "J"


Sub BNKREC()

Counter = 0
For i = 3 To 9
For J = 3 To 6
If Cells(i, "B").Value = Cells(J, "F").Value And Cells(i, "C").Value = Cells(J, "G").Value Then
Cells(i, "D").Value = "X"
Cells(J, "H").Value = "X"
Else
Range("J1").Offset(Counter, 0).Value = Cells(i, "B").Value
End If
Counter = Counter + 1
Next
Next

End Sub




List 1:
Date Check# Amount
03/02/2013 100 $1,000.00
03/03/2013 101 $1,045.25
03/05/2013 102 $280.56
03/06/2013 103 $456.31
03/08/2013 104 $250.55
03/15/2013 105 $2,456.12
03/25/2013 106 $844.76

List 2:
Date Check# Amount
03/02/2013 100 $1,000.00
03/05/2013 102 $280.56
03/08/2013 104 $250.55
03/25/2013 106 $844.76


Expected result:

Un-Matched List:
03/03/2013 101 $1,045.25
03/06/2013 103 $456.31
03/15/2013 105 $2,456.12

 

[JoeMo]

Try this:

Sub UnmatchedList()
Dim lR1 As Long, lR2 As Long, nRU As Long, Matched As Boolean
Dim vA1 As Variant, vA2 As Variant
'Assume first list starts in A2 - adjust to suit
lR1 = Range("A" & Rows.Count).End(xlUp).Row
'Assume 2nd list starts in F2 - adjust to suit
lR2 = Range("F" & Rows.Count).End(xlUp).Row
'Assume unmatched output starts in K2 - adjust to suit
Range("K1:M1").Value = Range("A1:C1").Value
vA1 = Range("A2", "C" & lR1).Value
vA2 = Range("F2", "H" & lR2).Value
Application.ScreenUpdating = False
For i = LBound(vA1, 1) To UBound(vA1, 1)
    Matched = False
    For j = LBound(vA2, 1) To UBound(vA2, 1)
        If Join(Array(vA1(i, 1), vA1(i, 2), vA1(i, 3)), "") = _
            Join(Array(vA2(j, 1), vA2(j, 2), vA2(j, 3)), "") Then
            Matched = True
            Exit For
        End If
    Next j
    If Not Matched Then
        nRU = Range("K" & Rows.Count).End(xlUp).Row + 1
        Range("K" & nRU).Resize(1, 3).Value = Array(vA1(i, 1), vA1(i, 2), vA1(i, 3))
    End If
Next i
End Sub

 

[Xceller]

Hi JoeMo,

Thanks for the quick response. I tried your code. It just copied the whole List1 to Range K1:M8

 

[JoeMo]

Hi JoeMo,

Thanks for the quick response. I tried your code. It just copied the whole List1 to Range K1:M8

What range is list2 in?

 

[Xceller]

Range for list1 = A2:C9
Range for List2 = E2:G6

 

[JoeMo]

Range for list1 = A2:C9
Range for List2 = E2:G6

If you look at the comments in the code I posted, it indicates list2 is assumed to be in F:H ('Assume 2nd list starts in F2 - adjust to suit). You need to adjust the code to your range as indicated in the comment.