# Excel 2010 formula to delete repeated content based on value

I have a spreadsheet with two columns, a name and a value. The name is repeated several times with different values, for instance:

Name - 1
Name - 2
Name - 3

etc.

I am looking for a formula that will go through the spreadsheet and delete all duplicate instances of the name column, saving the one with the second highest value. So if I input a spreadsheet like the one above, it would save the row "Name - 2" and delete the others. Is this possible?

EDIT: The spreadsheet has 6000+ values, so I'd prefer a solution that's as automated as possible. I was thinking something like:

1. Sort values by name, then value.
2. Formula that deletes duplicate rows with lowest values.
3. Formula that deletes all rows but lowest values.
-
Instead of using a formula, filter/sort the results and then pick the 2nd largest. I assume a matrix MAX function could work. In either case you would need to delete by hand. – Doktoro Reichard Oct 8 '13 at 22:28
I've been sorting by value and then selecting and deleting manually, but this is a spreadsheet that has 6000+ values that need to get shrunk down to something like 2000, so it takes a long time. – Elisa Oct 8 '13 at 22:38
LARGE can give you the n-th biggest element of an array. Other than this, there is no function that deletes cells. So, your answer will require VBA scripting. – Doktoro Reichard Oct 8 '13 at 23:12

You can get what you want with a helper column and a filter.

Step 1: Helper column

Add a column to your table with the following formula, where the names are in `A2:A18` and the values are in `B2:B18`.

``````=OR(COUNTIF(\$A\$2:\$A\$18,A2)=1,SUMPRODUCT(1*(B2<\$B\$2:\$B\$18),1*(A2=\$A\$2:\$A\$18))=1)
``````

This formula will return `TRUE` for the rows you want to keep, i.e., with the second highest value for duplicate names, and any non-duplicate names (like `d` in my example below). If by chance you DON'T want to keep non-duplicate rows, you can use the following formula instead.

``````=SUMPRODUCT(1*(B2<\$B\$2:\$B\$18),1*(A2=\$A\$2:\$A\$18))=1
``````

Step 2: Filter

Simply filter the entire table for rows that are `TRUE` in the helper column.

If filtering is not enough, and you really need to delete the other data, you can just copy and paste the filtered result to another table, and then after deleting the original table, you can paste in your clean copy.

-
Very clever, but this fails when there is a tie for the highest value for a name. Yes, I know that the OP said, “The name is repeated several times with different values,” (emphasis added), but I’m inclined not to take that literally without confirmation. IMHO, the best answer probably involves the `LARGE` function, but I can’t immediately see how to craft that into a working solution. I did, however, “fix” your response. – Scott Oct 9 '13 at 2:39
Excellent! This worked perfectly! – Elisa Oct 9 '13 at 17:38

I agree that you should sort by name and value; and I agree that Excellll has a good approach.  But his answer fails if your data contains a tie for the highest value for a name; I have adapted (built upon) that answer here:

Define two helper columns:

• `C2` - `=(A2<>A3)`
• `D2` - `=IF(A1=A2, B1, B2)`

Column `C` identifies the rows that are the last occurrence of a name, and Column `D` gets the second highest value (or the only value, if there is only one occurrence).  Then filter out the rows where Column `C` contains `FALSE`, and pair the name in Column `A` with the value in Column `D`.  Note that the following example uses the same data as Excellll’s answer, except the top two `b`’s are both 17.  I have used conditional formatting to highlight the `A` and `D` data where `C` is `TRUE`.

-
And if 3 consecutive rows are tied for highest value...? I suggest using something guaranteed unique as the tiebreaker instead, like `ROW()`. – Excellll Oct 9 '13 at 4:13
@Excellll: If three consecutive rows are tied for highest value... my answer still works. What’s your point? – Scott Oct 9 '13 at 17:30
Sorry, I didn't read all the way through to the last step. I thought you were filtering on a different condition. +1 for the trouble – Excellll Oct 10 '13 at 1:07