Using Mac Excel, how can I delete partial duplicate rows?

Question

I'm running Mac Office 2011 under Lion.

I have a sheet of many thousands of rows of data, with a good 40 or 50 columns. One column is email address. If any two rows have a duplicate email address, I'd like to keep only one of the rows.

Ideally, when duplicates were found it would default to keeping the row that has a particular value ("Y") in a specific cell, if applicable. But even if it just kept the first or last one, it would be an improvement.

Also, there can easily be more than two instances of a particular email address, so a solution would need to be able to handle any arbitrary number of duplicates.

I have tried the solution suggested here - Remove duplicate rows in excel when not all the columns are the same - but it did nothing for me.

Thanks for any suggestions!

Answer 1

I don't know Mac Excel specifically, but I believe the following approach works for almost all spreadsheet applications:

1. Go to an empty column.
2. Use the =COUNTIF(A:A,A2) function. A:A is the column containing e-mails and A2 is the nearest cell containing the e-mail.
3. Copy mentioned formula to the entire column.
4. "1" is returned for unique values, anything more than "1" means duplicated value.
5. Sort by e-mail, if you want to have duplicates in rows in neighboring cells.
6. Use filter to hide non-duplicated values (i.e. hide 1s) or sort by new column descending to get all duplicates on top of the table.
7. Additionally, you may use filter on other column (containing "Y" or sth) to separate e-mails you want to leave and ones to be removed.