PFA my sample data. The requirement is fairly simple.
For each [Transaction Reference] I want 1 row. | want the field value returned based on firstly the Max [CapitalFlag] then then Max [Nett Value]. i.e. No matter what the max Nett Value is the higher CapitalFlag (i.e. 8 not 1) will take presidence.
So for this example it would be the second row. Even thought the Nett Value isn't highest the Capital Flag is higher (8 not 1).
I started with this script but it takes into account just the Nett Value, not the CapitalFlag