Solved

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

Posted on 2016-10-27
5
43 Views
Last Modified: 2016-11-02
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
Comment
Question by:IT_Field_Technician
  • 3
  • 2
5 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
 

Author Comment

by:IT_Field_Technician
Comment Utility
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
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:IT_Field_Technician
Comment Utility
This worked thanks!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome. Glad to help.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now