?
Solved

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

Posted on 2016-10-27
5
Medium Priority
?
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41863354
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
ID: 41864820
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 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41864877
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
ID: 41871136
This worked thanks!
0
 
LVL 32

Expert Comment

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 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