Forum Discussion
Keep only the rows with highest values in column C when column A and B have equal values
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!
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.
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.
- 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!
G_M_C I believe the easiest way would be to use Power Query for that. See attached. You familiar with PQ?
- 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!
