Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.
share|improve this question
    
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

2 Answers 2

up vote 4 down vote accepted

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

helper column example

Step 2: Filter

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

filter

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.

share|improve this answer
    
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.

                               

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.