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
  • 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 '14 at 9:28
  • @MikeFitzpatrick delete the rows – dassouki Apr 15 '14 at 9:37
  • Can you re-sort the data first? Or must it remain in a mixed order? – Madball73 Apr 15 '14 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 '14 at 13:04
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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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