[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

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?
0
IT_Field_Technician
Asked:
IT_Field_Technician
  • 3
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
 
IT_Field_TechnicianAuthor Commented:
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!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached and click the button on Sheet2 to run the code and see if this is what you were trying to achieve.
Domains.xlsm
0
 
IT_Field_TechnicianAuthor Commented:
This worked thanks!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now