Excel 2010 formula to delete repeated content based on value

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.

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.