Label duplicate with "1" if it has the max value in another column

Question

I am trying to find a way to tag a record with a "1" if it has the highest Column G value of all of its duplicates. Duplicates are determined via a Text Join in Column V. All other duplicates with a lesser Column G value will receive a "0".

I have this formula started, but it only marks the first occurrence:

=IF(COUNTIF($V$2:V2,V2)>1,0,COUNTIF($V$2:V2,V2))

Here is a pic of the setup:

The result should be a "1" in Q4 because it has the highest value in Column G of the three duplicates from Column V.

Thanks!

Answer 1

This is easy if you have you Office 2019 or 365 using the new MAXIFS function:

=(MAXIFS(G:G,V:V,V2)=G2)*1

Otherwise you can use this, which might run slower:

=(G2=MAX(IF(V:V=V2,G:G)))*1