How to find and select multiple items / values at the same time in Excel

[gtm1]

Here is an over simplified example of my problem



I have 2 excel spreadsheets.

The first spreadsheet has 1 column with a list of names

John, James, Steve, Richard, Bob

The second spreadsheet also has 1 column with a list of the same names, plus some MORE names.

John, Karl, James, George, Tony, Steve, Mike, Richard, Bob, Chris

I want to find the names in the first spreadsheet, and delete them from the second spreadsheet.. so that the second spreadsheet will be left with

Karl, George, Tony , Mike, Chris

Find and Select lets me do this, but only one at a time. Is there a quicker way to search for multiple names in one go.. as I have around 1000 to check - thanks.

[jeffreybrown]

You could do this with a macro! Please test on a copy of your data first.

Sub Rearrange()
    Dim wsSrc As Worksheet: Set wsSrc = Sheets("Sheet1")
    Dim wsDest As Worksheet: Set wsDest = Sheets("Sheet2")
    Dim lrSrc As Long: lrSrc = wsSrc.Range("A" & Rows.Count).End(xlUp).Row
    Dim lrDest As Long: lrDest = wsDest.Range("A" & Rows.Count).End(xlUp).Row
    
    Const sFormula As String = "=COUNTIF($A$2:$A$#,A2)"
    
    Application.ScreenUpdating = False
    
    wsSrc.Range("A2:A" & lrSrc).Copy wsDest.Range("A" & lrDest + 1)
    
    With wsDest
        .Range("B1").Value = "Hdr"
        lrDest = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("B2").Resize(lrDest - 1)
            .Formula = Replace(sFormula, "#", lrDest)
            .Value = .Value
        End With
        With .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
            .AutoFilter Field:=1, Criteria1:=">1"
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
        .Columns(2).EntireColumn.Delete
    End With

    Application.ScreenUpdating = True
End Sub