[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2016-10-27
5
Medium Priority
?
100 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 34

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 34

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 34

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41871509
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.

Question has a verified solution.

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

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.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

591 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