asked on

How to cross reference 2 lists in 2 separate columns in excel.

I have a list that is in this format in column A1 in excel 2010:

Allow, *@mxtoolbox.com, *
Allow, *@amazon.com, *
Allow, *@flip.com, *
Allow, *@demo.com, *
Allow, *@gmail.com, *
Allow, *@yahoo.com, *

I want to be able to remove the entire line in Column A1 (or just delete the cell) if it matches a domain name in column B1 which is a list of domain names as such:

flip.com
gmail.com
yahoo.com

Can someone help me understand how i could achieve this?

Subodh Tiwari (Neeraj)

You may try something like this....

Sub DeleteCells()
Dim lr1 As Long, lr2 As Long, i As Long, ii As Long
Dim x
lr1 = Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Cells(Rows.Count, 2).End(xlUp).Row
If lr2 = 1 And Range("B1") = "" Then
    MsgBox "No domains are listed in column B to compare with domains in column A.", vbExclamation, "Domains Not Found!"
    Exit Sub
End If
x = Range("B1:B" & lr2).Value
For i = lr1 To 1 Step -1
    For ii = 1 To UBound(x, 1)
        If InStr(Cells(i, 1).Value, x(ii, 1)) Then
            Cells(i, 1).Delete shift:=xlUp
            Exit For
        End If
    Next ii
Next i
End Sub

Open in new window

Dana D

ASKER

Subodh Tiwari (Neeraj) Thanks you so much but im unsure if the code is working - Can you please adjust the code so it puts the results in sheet 2 or something?

thank you so much!

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dana D

ASKER

This worked thanks!

Subodh Tiwari (Neeraj)

You're welcome. Glad to help.