Solved

Manage email addresses

Posted on 2014-01-06
12
194 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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