3

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?

Improve this question

3 Answers 3

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Again thanx! This is working really well :) I see there are some concepts here that are new to me, especially the comparing parts. I'll study and break down your code to make sure I'll understand every aspect of it :)
+1 from me as well as a variant loop/collection is very efficient
2

You can use a simple MATCH formula to detect any non matches, then delete them with AutoFilter

If your first list was in Sheet 1 Column A, your second in Sheet 2 Column A then in B1 of Sheet 2 put =ISNA(MATCH(A1,Sheet1!A:A,0)) and copy down

this returns TRUE where the second list cant be matched against the first. You can then delete these TRUE rows with autofilter

Note that you could also use =COUNTIF(Sheet1!A:A,A1)=0 for the same effect to identify not matches (as TRUE)

xl2010 pic shown here

enter image description here [VBA added]

Sub QuickKill()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    ws2.Columns(2).Insert
    Set rng1 = ws2.Range(ws2.[a1], ws2.Cells(Rows.Count, "A").End(xlUp))
    Rows(1).Insert
    With rng1.Offset(0, 1)
        .FormulaR1C1 = "=COUNTIF('" & ws1.Name & "'!C1,RC[-1])=0"
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .EntireRow.Delete
        .EntireColumn.Delete
    End With
End Sub
Improve this answer

3 Comments

Ah, I see that works :) But I do need it via VBA I think. Because I want this list to be as pure as possible, how would I do this using VBA then? Could I add this formula to an empty column, from top to bottom. And then loop through each cell, finding the value TRUE, and then delete those rows using vba code? Or could I run an IF with that formula and check the value directly?
I thought no vba was very little VB :). The VBA above does the same thing automatically, ie a very quick delete using a working formula column rather than a loop.
This is a very clear answer. And you are probably right, using formula will be faster than a loop. +1 btw
1

See this question. Using that technique, you could easily query SELECT * FROM [Sheet2$] WHERE columnX NOT IN (SELECT columnY FROM [Sheet1$]

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.