3

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.
3
  • 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. Oct 8, 2013 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, 2013 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. Oct 8, 2013 at 23:12

3 Answers 3

4

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.

2
  • 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. Oct 9, 2013 at 2:39
  • Excellent! This worked perfectly!
    – Elisa
    Oct 9, 2013 at 17:38
2

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.

                               

3
  • 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, 2013 at 4:13
  • @Excellll: If three consecutive rows are tied for highest value... my answer still works. What’s your point? Oct 9, 2013 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, 2013 at 1:07
0

Another alternative it could be, create a list with unique name values with this option and then use LARGE formula as array formula, something like this:

=LARGE(IF($A$2:$A$20=D2;$B$2:$B$20);2)

..IF($A$2:$A$20=D2;$B$2:$B$20)...

If match condition, then give me the values. In my examples it could be 
something like {1,2,...,6,FALSE,...FALSE} and the formula omits FALSE 
values.

=LARGE(..., ??) [??] Rank Value you want in this case 2

to convert as array formula, after write it, click F2 to edit and Ctrl+Shift+Enter and the formula you will get something like this:{=LARGE(...)}

The sheet could be like this.

You must log in to answer this question.

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