asked on

Manage email addresses

Hello,
We have multiple email lists for our newsletter. I have the ability to combine lists and remove duplicates but I need to be able to output a list of email addresses that appear in one list but not the other. So list A has 150 addresses and list B has 100. Both have similar and different email address. We want to create list C which contains only the email addresses that do not occur in one or the other list.

If there's an out of the box solution, that would be great but I think we could program an Excel file to do the same.

Thanks,
Chris

Raheman M. Abdul

what do you mean by one or the other lists?
Do you mean the email address should be unique among the 2 lists? ie no duplicates?

Joe Rud

Sounds like you just want to remove duplicates and populate a third list with only the remaining unique data.

Excel has a built-in tool to remove duplicates. On the ribbon, click on the "Data" tab. In there you'll see a "Remove Duplicates" tool that is very easy to use.

ASKER CERTIFIED SOLUTION

Naresh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

5teveo

I created test data...

column A is List A
Column G is list B
Column L is new List based upon your rules...
see attached file

Dim res As Variant

LRowA = Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
LRowG = Worksheets("Sheet1").Range("G" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row

Sheets("Sheet1").Select
Range("L2", Range("L2").End(xlDown)).Clear

rx1 = 2
RowIndexDestination = 2

For xRA = 2 To LRowA
res = Application.Match(Worksheets("Sheet1").Cells(xRA, "A"), Worksheets("Sheet1").Range("G1:G" & LRowG), 0)
If IsError(res) Then
Worksheets("Sheet1").Cells(RowIndexDestination, "L") = Worksheets("Sheet1").Cells(xRA, "A")
RowIndexDestination = RowIndexDestination + 1
End If
Next xRA

For xRG = 2 To LRowG
res = Application.Match(Worksheets("Sheet1").Cells(xRG, "G"), Worksheets("Sheet1").Range("A1:A" & LRowA), 0)
If IsError(res) Then
Worksheets("Sheet1").Cells(RowIndexDestination, "L") = Worksheets("Sheet1").Cells(xRG, "G")
RowIndexDestination = RowIndexDestination + 1
End If
Next xRG
MakeCListfromAandB.xlsm

mobileitsupport

ASKER

Hello, Thanks for the replies. I think that I may not have clearly described what I'm looking for. So here it goes. I start an email campaign with list A. From that campaign, I create list B of all those who have opened the email. From that campaign, I want to resend the email to all of those who are not in list B (they have not opened the email). Thanks for your help.

Naresh Patel

Hi mobileitsupport,

See attached

Thanks
Unique-Values.xlsx

5teveo

Modified Macro below... see attached

Use 'Version2' macro - delete 'Version1' if you want

'
' Macro1 Macro
'
Dim res As Variant

LRowA = Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
LRowG = Worksheets("Sheet1").Range("G" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row

Sheets("Sheet1").Select
Range("L2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents

For xRA = 2 To LRowA
res = Application.Match(Worksheets("Sheet1").Cells(xRA, "A"), Worksheets("Sheet1").Range("G1:G" & LRowG), 0)
If IsError(res) Then
Worksheets("Sheet1").Cells(xRA, "L") = Worksheets("Sheet1").Cells(xRA, "A")
End If
Next xRA
MakeCListfromAandBv2.xlsm

mobileitsupport

ASKER

Hey Itjockey, I'm curious, the third column creates a line between each item. Is that something that I can eliminate? Thanks, Chris

Naresh Patel

Yes I guess sure.....ill look in to this tomorrow as here is sleeping time.....

Thanks

Naresh Patel

Will you pls send me screen shot regarding what you are saying...thanks

mobileitsupport

ASKER

Okay, here's it is.
email-list-output.JPG

Naresh Patel

ok for this kind of requirement you need one more column for calculation. see attached.
Unique-Values.xlsx