Finding the largest number among a group (similar to For Loop)

[ADhal]

I have a data set like below:

Country	SumRevenue	SumDiscount	M1	M2	Country	Revenue	Discount(5%ofRevenue)
Australia	1000	50	Jon	Mac	Australia	100	5
Australia	1000	50	Jon	Mac	Australia	150	7.5
Australia	1000	50	Jon	Mac	Australia	200	10
Australia	1000	50	Mac	Mac	Australia	250	12.5
Australia	1000	50	Tom	Mac	Australia	300	15
India	360	18	Ravi	Raj	India	120	6
India	360	18	Ravi	Raj	India	130	6.5
India	360	18	Raj	Raj	India	110	5.5
US	1270	63.5	Bob	Liz	US	300	15
US	1270	63.5	Jim	Liz	US	400	20
US	1270	63.5	Mary	Liz	US	340	17
US	1270	63.5	Liz	Liz	US	230	11.5

I want to iterate for each country, and compare between M1 and M2. If M1 = M2 and Revenue = MAX(Revenue), then select that line, else select the line with MAX(Revenue)

So expected Output is:

Country	SumRevenue	SumDiscount	M1	M2	Country	Revenue	Discount(5%ofRevenue)
Australia	1000	50	Mac	Mac	Australia	250	12.5
India	360	18	Ravi	Raj	India	130	6.5
US	1270	63.5	Jim	Liz	US	400	20

For Australia, M1 = M2 but for India and US, the line with MAX(Revenue) is selected. 

Any help will be greatly appreciated. 

[princejindal · Accepted solution]

Hi @ADhal,

Find attached the solution.

Cheers!