Excel vba - Compare two ranges and find non matches

Question

I've got two Excel sheets where one sheets consists of a list of users. And the other list contains the same data, only the same user is listed several times. Now, I need some way of comparing the second list with the first one and delete the rows that contains a user that's not found in the first list.

The first list looks like this:

• Paul Mccartney
• John Lennon
• George Harrison
• Ringo Starr

The second list might look like this:

• Paul Mccartney
• Paul Mccartney
• Paul Mccartney
• John Lennon
• John Lennon
• John Lennon
• George Harrison
• George Harrison
• George Harrison
• Ringo Starr
• Ringo Starr
• Ringo Starr
• Ringo Star
• Ringo Star
• Ringo Star

So, comparing these two lists, you see that the name Ringo Star is NOT in the first list, and I want to delete those rows. I've tried with several loops, but I can't quite get this to work. I guess I could add these items to an array of some sort, and run it though a function. But is there an easy way of doing this without that much code?

Answer 1

This time, you could use a collection.

Here is a try based on your previous file:

Option Explicit

Sub test()
Dim i As Long
Dim arrSum As Variant, arrUsers As Variant
Dim cUnique As New Collection

'Put the name range from "Summary" in an array
With ThisWorkbook.Sheets("Summary")
    arrSum = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

'"Convert" the array to a collection (unique items)
For i = 1 To UBound(arrSum, 1)
    On Error Resume Next
    cUnique.Add arrSum(i, 1), CStr(arrSum(i, 1))
Next i

'Get the users array
With ThisWorkbook.Sheets("Users")
    arrUsers = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

'Check if the value exists in the Users sheet
For i = 1 To cUnique.Count
    'if can't find the value in the users range, delete the rows
    If Application.WorksheetFunction.VLookup(cUnique(i), arrUsers, 1, False) = "#N/A" Then
        With ThisWorkbook.Sheets("Summary").Cells
            .AutoFilter Field:=1, Criteria1:=cUnique(i)
            .Range("A2", .Range("A" & Rows.Count).End(xlUp)).EntireRow.Delete
        End With
    End If
Next i
'removes AutoFilter if one remains
ThisWorkbook.Sheets("Summary").AutoFilterMode = False
End Sub