Keep only the rows with highest values in column C when column A and B have equal values

[G_M_C]

Hello!

I have an Excel table like the one below and I want to keep only the rows with highest values in column C when column A and B have equal values and also to keep all the rows that have different values across all the columns.

For example, for the rows 2, 3, 4 *that wahe equal values in columns A and B), I want to keep only one entry, which has the maximum value in column C (that will be row number 4) and also keep row number 5 (which has different values across all the cells) and so on.

Is there a way to do this in Excel?

Thanks!

[#2]

@G_M_C I believe the easiest way would be to use Power Query for that. See attached. You familiar with PQ?

[#3]

@G_M_C 

I'd create a helper formula in column D with formula

=C2=MAXIFS($C$2:$C$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2)

in D2, filled down.

You can then filter for FALSE.

Delete the filtered rows, and finally remove the filter.

[#4]

Hello!
That worked, thanks a lot!
Also, it worked with the following formula, sugested by @OliverScheurich:
=IF(OR(LARGE(IF(($A$2:$A$23=$A2)*($B$2:$B$23=$B2),$C$2:$C$23),1)=$C2,COUNTIFS($A$2:$A$23,$A2,$B$2:$B$23,$B2)=1),A2,"")
I hope it will help other users too.
All the best!

[#5]

Hello and thank you for your time!
I am not familiar with PQ, but I will try to learn in the future.
I managed to solve the problem with the formulas sugested by our colleagues @HansVogelaar and @OliverScheurich.
All the best!

[#6]

@G_M_C 

There are new functions that may be brought into play as part of the solution algorithm.  One such is GROUPBY.  If the first two columns are named 'IDs' and the 3rd is 'weightedRatio', the formula

= GROUPBY(IDs, weightedRatio, MAX, , 0)

will filter out all the duplicate IDs, returning the maximum weighted ratio for each distinct combination.