+ Reply to Thread
Results 1 to 6 of 6

Removing one spreadsheet of data from another

  1. #1
    Registered User
    Join Date
    06-08-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Removing one spreadsheet of data from another

    Hi,

    I have two excel spreadsheets. One is a complete list of our customer records, the other is a list of records (taken from the complete list) that are unsubscribes to our mailings. I want to be able to remove the unsubcribe list from the complete list - can this be done? Even if I can just match and remove the email addresses, this will serve the main purpose of ensuring we don't contact any of our subscribers that have unsubscribed.

    many thanks for your help

    Andy

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Removing one spreadsheet of data from another

    Sounds like this could maybe be done fairly easily in 2007 using

    Data > Remove Duplicates

    If you append one list to the other first.

    Can you post a sample workbook?
    It should not contain any sensitive data.

    Cheers

  3. #3
    Registered User
    Join Date
    06-08-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Removing one spreadsheet of data from another

    Hi Marcol,

    I did think about remove duplicates, but wasn't sure if this would still leave one of the records in the spreadsheet and just remove the duplicate one. Looking at the filter>advanced tab in excel, there is a way to remove duplicates, but this says 'leave uniques', so I'm guessing this means it wouldn't remove both duplicate email addresses, just one of them.

    Unfortunatley I can't show the data, it's got customer records in.

    Any help or thoughts really appreciated...

    thanks again

    Andy

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Removing one spreadsheet of data from another

    Well hush my mouth.....

    You are absolutely correct, silly me.

    How about a macro to step thro' Sheet2 "e-mail address",assuming they are all in the same column. Then Find on Sheet1 and delete the entire row.
    Would that suit you?

  5. #5
    Registered User
    Join Date
    06-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    22

    Re: Removing one spreadsheet of data from another

    For example if you want to delete two rows which contain Ritesh and rakesh. (Can be any number of rows). In column B write Delete and drag down to the entire list in Sheet 2 which you wanted to delete from sheet1.

    Given sheet 2:

    ColumnA ColumnB
    ------- -------
    Ritesh Delete
    Rakesh Delete

    You can flag the rows in sheet 1 where a value exists in sheet 2:

    ColumnA ColumnB
    ------- --------------
    Vikas =IF(IFERROR(VLOOKUP(D4,Sheet2!$B$4:$C$7,2,FALSE),0)=0,"","Delete"
    Kamble =IF(IFERROR(VLOOKUP(D4,Sheet2!$B$4:$C$7,2,FALSE),0)=0,"","Delete")
    Ritesh =IF(IFERROR(VLOOKUP(D4,Sheet2!$B$4:$C$7,2,FALSE),0)=0,"","Delete")
    Kumar =IF(IFERROR(VLOOKUP(D4,Sheet2!$B$4:$C$7,2,FALSE),0)=0,"","Delete")
    Rakesh =IF(IFERROR(VLOOKUP(D4,Sheet2!$B$4:$C$7,2,FALSE),0)=0,"","Delete")

    ColumnA ColumnB
    ------- --------------
    Vikas 0
    Kamble 0
    Ritesh Delete
    Kumar 0
    Rakesh Delete


    Then apply filter to column B and delete the rows containing Delete then later filter and remove blanks. Hence there shall be no blankl rows even.

    Regards,
    K. vikas
    Last edited by vikaskamble87; 06-20-2014 at 05:37 AM.

  6. #6
    Registered User
    Join Date
    11-18-2015
    Location
    England
    MS-Off Ver
    2016
    Posts
    1

    Re: Removing one spreadsheet of data from another

    The easiest way I've found of doing this, is to just copy the unique reference from your second 'unsubscribe' sheet into the final column of the first, as below:

    Reference Name Email Unsubscribe Reference
    14-96305 A Smith [email protected] 14-96305
    14-96308 A Smith [email protected] 14-96308
    05-64973 A Smith [email protected] 14-96339
    14-96330 A Smith [email protected]
    14-96339 A Smith [email protected]

    Then highlight the column with the reference from your initial spreadsheet (column A above), and the column you've just added (column D above) and click conditional formatting - highlight cell rules, duplicate values.

    Now you just need to sort column A - Data, sort...and under 'sort on' select cell colour. The duplicate values will be moved to the top and you can deleted them out of the spreadsheet along with column F.

    David

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1