Removing one spreadsheet of data from another

**AndyH1** · 06-08-2010, 05:47 AM

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

**Marcol** · 06-08-2010, 05:54 AM

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

**AndyH1** · 06-08-2010, 06:10 AM

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

**Marcol** · 06-08-2010, 06:34 AM

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?

**vikaskamble87** · 06-20-2014, 05:25 AM

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

**dmlace** · 11-18-2015, 05:17 AM

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