Removing Data from one spread based on another

[Hamilton Hoffman]

So I don't know how to quite put this as I'm evidently a moron, but I have two spreadsheets. One is the master, the other is a list of data that I need to remove from the master. What I need to know is how do I either filter on or compare the master to the scrub list?

So, for example, I have a bunch of e-mails that bounced from a mailing. I don't want to upload them into salesforce, so I want to take the bounce list and compare it against the master list. I then want to remove everything that's on the bounced list from the master. I have no idea how to do that except for one at a time (and that's just stupid).

Any recommendations? Please help!!

 

[NARAYANK991]

Hi Hoffman ,

Using a formula , you can match the email IDs in the bounced list with those present in the master , so that if there is a match , the result is 1 , otherwise it is 0. This formula needs to be introduced on the master sheet tab , in a separate column.

Once this is done , you can use the AutoFilter feature in Excel to show only those rows of data which have 1 in the above newly introduced column. Selecting these rows and pressing the DEL key will delete these rows of data.

Narayan

 

[Hamilton Hoffman]

Thank you for the response Narayan. Due to being sooo very ignorant in Excel (it's been a long time since I've had to use it), what formula would you recommend? I've got 2013, so there appears to be a plethora of options and I'm a little stymied by them. Thoughts?

 

[NARAYANK991]

Hi Hoffman ,

Since you say you are not familiar with Excel , please clarify the following :

When you say you have two spreadsheets , do you mean two workbooks or two worksheets ?

A workbook is the same as a physical file on your computer storage media , whether hard disk or flash disk or anything else.

A worksheet is a virtual entity within a workbook ; one workbook can have even a hundred worksheets.

Once you clarify this , I will upload a sample file(s) with the formulae.

Narayan

 

[Hamilton Hoffman]

Hello Narayan!

Thank you so very much for your assistance. I'm a real novice when it comes to Excel. These two spreadsheets are two separate workbooks. The only way I've been able to compare the data prior has been to do it one at a time. When you have over 500 records, that's a bit time consuming and I KNOW there's a better way.

In essence I need to have the records from the "bounce" list expunged from the master list. I'm going to have to do this repeatedly in my new position, and doing it one at a time is going to make me crazy! I truly appreciate your expertise on the subject, so thank you for your help.

 

[NARAYANK991]

Hi Hoffman ,

I have uploaded two workbooks ; you can open the one named Master_Emails , and do the following :

1. Click on the Name Box , and select Table1 ; this selects the entire table of master email addresses.

2. Click on the AutoFilter arrow in column B.

3. You will see 3 checkboxes labelled (Select All) , FALSE , TRUE.

4. Click on the FALSE checkbox to uncheck it.

5. Excel will now display only those email addresses which are present in the Bounced_Emails workbook.

6. Right-click anywhere within the table and select Delete and then Entire Sheet Row.

7. All the displayed rows will be deleted.

8. You can now again click on the AutoFilter arrow , and click on either (Select All) or TRUE to display the remaining email addresses.

Narayan

 

[Hamilton Hoffman]

Hello Narayan,

Thank you for the explanation. My question now would be how do I actually do the above to the workbooks I have? It seems to me that I'd have to identify which e-mails were in the bounced list and compare them against the master in order to determine the true/false statement, then create a separate column for the "match". That's what I don't know how to do. How did you get the master list to look at the bounce list? How did you determine which are "true" and which are "false"?

I apologize for my ignorance, but that's why I joined this forum. Please expound when you have a moment.

Thank you!

 

[NARAYANK991]

Hi Hoffman ,

Sorry for the delay ; you don't have to do anything except insert the formulae which are present in the uploaded file Master_Emails.xlsx , in your master file.

If we consider the first formula , in cell B2 , it is as follows :

=ISNUMBER(MATCH($A2,'C:\Documents and Settings\My Name\My Documents\Downloads\[Bounced_Emails.xlsx]Bounced_Sheet'!$A$2:$A$8,0))

The components which can vary here are as follows :

$A2 - this is the cell which contains the first email address in your master emails workbook ; if your data of master emails is in column J , starting from cell J7 , then put this to $J7.

'C:\Documents and Settings\My Name\My Documents\Downloads\[Bounced_Emails.xlsx]Bounced_Sheet'!$A$2:$A$8

In the above , the differently coloured components refer to the following , in the same order :

a. The path name of the workbook which contains the list of bounced emails

b. The file name of the workbook which contains the list of bounced emails

c. The name of the worksheet which contains the list of bounced emails

d. The range reference which contains the list of bounced emails

Just copy this formula , as it is , into your file , and then change the individual components to suit.

If all this is difficult , just post the above components in your case viz.

What is the path name , file name , worksheet name and range reference for both the files , and I will upload two files which will correspond exactly to your two files ; you can then download these two files , and just copy paste your data into them.

Narayan

 

[Hamilton Hoffman]

Hello Narayan,

Thank you so very much for the detailed breakdown above. I do apologize for the delayed response. I've actually been working on a different project until today.

I did just as you said and copied everything. I put the correct information in and all I'm getting are falses. Out of over 10000 entries, not a one of them is showing up as true (despite knowing for a fact that they're on the bounced list). I think I may be going mad!

Here is the formula I'm attempting to use:

=ISNUMBER(MATCH($M2,'C:\Users\hhoffman.VGOCOM\My Documents\[CA Bounces.xlsx]CA Bounces'!$C$2:$C$1312,0))

I really have NO idea why it is not showing any trues. Please help!!

 

[cdbauer1]

@Hamilton Hoffman Why don't you just put the list of data in a separate worksheet within the same workbook? That removes the complication of having two workbooks and potentially two instances of Excel screwing things up.

Then you can write a formula like this:
=IFERROR(IF(MATCH([@[EMAIL ADDRESSES]],Table13[INVALID EMAIL ADDRESSES],0)>0,"Remove","Keep"),"Keep")

That says If there is a match between an email in the Master table and the Remove table, mark the match as "Remove". If there isn't a match, mark it as "Keep".

Then you'd filter on the "Remove", CTRL + Click each row that you want to remove, and finally Left-Click > Delete > Entire Sheet Row.

See attached