0

In a sheet that has two columns, the first represents a string and the second represents some field value associated to that string as shown below. I roughly have 30,000 rows like that.

My Question is, how can I remove the duplicates with least field value?

For example, I'd like to get rid of (F,4) while maintaining F, 5)

   A   B
1  F   5
2  F   4
3  G   2
4  E   1
5  G   3
6  E   2
Improve this question
4
  • 2
    Do you want to really want to delete the duplicates with the least field value or do you want to find the maximum field value? The latter is easily achieved using a pivot table.
    – Mike Fitzpatrick
    Apr 15, 2014 at 9:28
  • @MikeFitzpatrick delete the rows
    – dassouki
    Apr 15, 2014 at 9:37
  • Can you re-sort the data first? Or must it remain in a mixed order?
    – Madball73
    Apr 15, 2014 at 12:46
  • I agree with the above question, can the data be sorted? Also will there be more than one duplicate only keeping the greatest value? e.g. delete (F,2) and (F,4) but keep (F,5).
    – Raystafarian
    Apr 15, 2014 at 13:04

2 Answers 2

Reset to default
0

How many letters are in A column? If not much, my quick dirty solution would be:

  • in C1 write: =IF(A1="F";B1;"")
  • in D1 write: =B1=MIN(C:C)

then quick filter by D column, showing only "TRUE", and delete that row. Repeat for all other letters (in first formulae, change quoted letter in A1="F").

If there are many values in A column, Pivot on another sheet with VLOOKUP's from there on 1st sheet will do. Can't do this abstractly though, done that in Google Docs spreadsheet. Don't mind copying it for your own use, also you can export to Excel and ODF.

Improve this answer
0

You can use either of the following formulas (created in C1 and copied down):

=SUMPRODUCT(--(A:A=A1),--(B:B>B1))

or

=COUNTIFS(A:A,"="&A1,B:B,">"&B1)

This results in a count of how many records have bigger column B values than the current row with matching column A. You can delete all rows with non-zero values.

Improve this answer

You must log in to answer this question.

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