Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question
did the answer below work for you? – JoshP Sep 28 '12 at 13:51
For me, this helped me solve the question: In Excel, How can I hide all but 1 row of a set of rows when a column value repeats multiple times? See my comments in the answer by @kurp. – condiosluzverde Dec 9 '13 at 22:14
up vote 2 down vote accepted

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.
share|improve this answer
Aha... clever. That makes sense... I'll give it a shot, thanks!! – Jonathan van Clute Sep 19 '12 at 23:55
Building on top of @kurp's solution using COUNTIF(), I used an IF(COUNTIF(A:A,A2)>1,"Hide","Show"). It enables one to filter on that column to select only rows with "Show" as the value. When there are 5 rows with same value, the COUNTIF() evaluates to 5 for the first appearance, on the next row the COUNTIF() shows 4, etc. on down to the last row that shows 1. Note this requires the rows are sorted by the repeated value column. One can put the constant "Show" or "Hide" literally in any row to force that row to show or hide itself. – condiosluzverde Dec 9 '13 at 22:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .