How to remove rows that match?

I have a csv file with list of people (first name, last name, email) and then later on I will get a list of people in another csv file that unsubscribed ( only one column with email).

Q: How can I delete all rows from the first file in case the row contain email of someone that unsubscribed?

I was thinking of using a spreadsheet (MS Excel, Open Office, google) but I can use any other solution.

I tried MS Excel but simple remove duplicate is not working. I guess I need to use other approach. But do not know what to do next.

Note that I need to export the 'clean' csv into new file. Of course I don't want the file to have the 'unsubscribed' email.

Radek

Posted 2015-02-10T23:18:02.407

Reputation: 2 914

We aren't here to tell you what program to use and how to do it. We are here to help you when you tell us what you have tried. Decide what you are going to use and share where you get stuck. – CharlieRB – 2015-02-10T23:33:09.657

I tried MS Excel but simple remove duplicate is not working. I guess I need to use other approach. But do not know what to do next. – Radek – 2015-02-10T23:37:35.827

@sunk818: it's a great idea. It deserves an answer... – Radek – 2015-02-11T22:21:09.257

Answers

If you use Microsoft Access, you can just link to the unsub CSV (assuming it is the same file every time with just different e-mails). Then create a query an update query that will flag your subscribers with an unsubscribe flag. Perhaps that same query can update another column with the current date/time. You can then run another query that will export updated table to a clean CSV. You can do all of this as a Macro in Access. As long as your linked CSV file is the same, everything else can be automatically processed and a clean CSV be created in a matter of seconds.

Sun

Posted 2015-02-10T23:18:02.407

Reputation: 5 198

That's why asked for suggestion of other kinds of solution. I think that using db might be bit easier and quicker to do. – Radek – 2015-02-11T23:00:34.353

I have a semi-automated way to do this in Excel:

Import the CSV files into two sheets. I called them MasterList and UnsubList
Sort the UnsubList (Sort A to Z) using the Sort & Filter button
In the MasterList, I created another column called Unsub?
In each row under the Unsub? column, enter the following formula:

=IF(C2=VLOOKUP(C2,UnsubList!$A$2:$A$1000,1),"Unsub","Keep")

In this example, it would be for the 2nd row. If the unsub list has more than 999 entries, you need to make the range bigger.
Then turn on Filter (under the Sort & Filter button). You should have something similar to the screenshot below.
Unselect only the Keep rows using the dropdown for the Unsub? column. This means that only the rows that require removal will remain visible
Select all these rows except the heading by dragging the row numbers from top to bottom, right click, and click Delete in the pop-up menu
Now they are gone from the MasterList
Delete the Unsub column and re-export to csv

MasterList

UnsubList

Alex

Posted 2015-02-10T23:18:02.407

Reputation: 824