Simple lookup is not so simple

[nimakharrazi]

I have two columns of data. Column A has 7,000 names and Column B has 1,500. I need to compare the two and delete all the names in Column A that don't have a match in Column B. How do I do this?

Thank you in advance!

 

[John Davis]

Maybe:

Sub nimakharrazi()
Dim lr As Long
Dim rcell As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row

Columns("B:B").Insert xlToRight

With Range("B2:B" & lr)

    .Formula = "=VLOOKUP(A2,$C$2:$C$1000,1,FALSE)"
    .Value = .Value
    
End With

For Each rcell In Range("B2:B" & lr)

    If rcell.Text = "#N/A" Then
    
        rcell.Offset(, -1).Clear
        
    End If
    
Next rcell

Columns("B:B").Delete xlToLeft

For Each rcell In Range("A2:A" & lr)

    If rcell.Value = "" Then
    
        rcell.Delete xlUp
        
    End If
    
Next rcell


End Sub

 

[nimakharrazi]

That worked beautifully! Thank you John!

Maybe:

Sub nimakharrazi()
Dim lr As Long
Dim rcell As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row

Columns("B:B").Insert xlToRight

With Range("B2:B" & lr)

    .Formula = "=VLOOKUP(A2,$C$2:$C$1000,1,FALSE)"
    .Value = .Value
    
End With

For Each rcell In Range("B2:B" & lr)

    If rcell.Text = "#N/A" Then
    
        rcell.Offset(, -1).Clear
        
    End If
    
Next rcell

Columns("B:B").Delete xlToLeft

For Each rcell In Range("A2:A" & lr)

    If rcell.Value = "" Then
    
        rcell.Delete xlUp
        
    End If
    
Next rcell


End Sub

 

[John Davis]

You're welcome. Glad to hear that, and thanks for the feedback.