Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Manage email addresses

Posted on 2014-01-06
12
Medium Priority
?
206 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
  • 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

876 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