Delete row if data in column B is in column A Closed

Question

I'm working on a mailing campaign where I have two mailing lists A and B.

Mailing list A contains ALL email addresses
Mailing list B contains a handful of selected email addresses

I have recently sent an email to list B but require further responses for the campign. I now wish to mail everyone who I have not already emailed.

Therefore I need to email Mailing list A with Mailing list B removed from the data.

I have the data in excel in two separate columns A and B. I can use Vlookup to see which data is in both columns but my research thus far has not provided me with a solution for removing the data from A if the data is also found in B

For example:

Column A              Column B 
a@a.com               b@b.com 
b@b.com               d@d.com 
c@c.com               f@f.com
d@d.com               g@g.com 
e@e.com               i@i.com
f@f.com               l@l.com
g@g.com
h@h.com
i@i.com
j@j.com
k@k.com
l@l.com

The mailing list is approximately 9,000 and 4,900 for A and B respectively.

I would very much appreciate your resonses (including any macro that can automatically remove the duplicates)

Configuration: Windows 7 / Firefox 3.6.10

TrowaD · Answer

Hi Nashy,

Put column B under column A and sort your data alphabetically.
Then use this code:

Sub test()
Set MR = Range("A1:A9000")
    For Each cell In MR
If cell.Value = cell.Offset(1, 0).Value Then Range(cell, cell.Offset(1, 0)).ClearContents
    Next
End Sub
Now sort your data again to leave out the blank cells.

Best regards,
Trowa

Delete row if data in column B is in column A

1 response

Similar discussions

Subscribe To Our Newsletter!