Solved

Manage email addresses

Posted on 2014-01-06
12
198 Views
Last Modified: 2014-01-14
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
0
Comment
Question by:mobileitsupport
[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
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39760278
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?
0
 
LVL 14

Expert Comment

by:Geisrud
ID: 39760752
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.
0
 
LVL 8

Accepted Solution

by:
Naresh Patel earned 250 total points
ID: 39761710
Hi mobileitsupport,

I don't understand you properly, but what ever I understand is attached.


Uniques Values

Thank you
Unique-Values.xlsx
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 8

Expert Comment

by:5teveo
ID: 39762447
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
0
 

Author Comment

by:mobileitsupport
ID: 39763374
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.
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39763917
Hi  mobileitsupport,

See attached


Thanks
Unique-Values.xlsx
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39765193
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
0
 

Author Closing Comment

by:mobileitsupport
ID: 39772296
Hey Itjockey, I'm curious, the third column creates a line between each item. Is that something that I can eliminate? Thanks, Chris
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39772483
Yes I guess sure.....ill look in to this tomorrow as here is sleeping time.....

Thanks
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39773008
Will you pls send me screen shot regarding what you are saying...thanks
0
 

Author Comment

by:mobileitsupport
ID: 39777629
Okay, here's it is.
email-list-output.JPG
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39778621
ok for this kind of requirement you need  one more column for calculation. see attached.
Unique-Values.xlsx
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

617 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