How can I control which duplicates are deleted?-CodePudding

I have a list of duplicates that have different durations attached to it (in a Google Sheet):

Duplicate A - 2:21
Duplicate A - 2:21
Duplicate A - 3:30
Duplicate A - 4:00
Duplicate B - 2:04
Duplicate B - 6:00
Duplicate C - 5:32
Duplicate C - 3:43
Duplicate C - 2:32
Duplicate C - 9:00

What I would like to do is find all duplicates and only keep the one entry in each set (A,B,C) that has the longest duration.

So my list should look like this at the end:

Duplicate A - 4:00
Duplicate B - 6:00
Duplicate C - 9:00

What's the best way to achieve this? Regex? Formula?

CodePudding user response：

One way you could do this is with a filter and MAX():

Here is an example sheet I created to demonstrate this.

In cell D1 I have the formula

=filter(unique(A1:A), unique(A1:A)<>"")

And in cell E1 I have

=iferror(max(filter($B$1:$B, $B$1:$B<>"", $A$1:$A=D1)))

The formula in E1 would need to be dragged down the column (though someone else may have a better solution that doesn't require this).

Please let me know if you have any questions, or if this does not work for you.