Select single row based on multiple field conditions

[ANARK]

Hi Everyone - Im working on a report which has 500k lines of excel data. Looking for a way to extract only one line per ID based on some conditions.

In the sample below, I have to select one line per ID. Complete data is grouped by ID field. Under an ID - first check is on field "Level".

if its "High" it has to be selected, if we have multiple lines with the same level - line item with higher sum needs to be selected but based on Level.

Scenario:1 - if we have multiple\different levels for the same ID, Level "High" with higher sum needs to be selected.

Scenario:2 - if there is only one level, any of "High\Mid\Low" the line item with higher sum needs to be selected.

Scenario:3 - If we have only "Mid" & "Low", line item "Mid" with higher sum needs to be selected.

Scenario	ID	Form	BU	Level	Prod	Ctry	Sum
1	10	O	APJ	High	A	SG	5
1	10	R	APJ	Low	D	NZ	10
1	10	R	APJ	High	D	NZ	8
2	12	R	EMEA	Mid	B	UK	55
2	12	P	EMEA	Mid	C	UK	120
3	15	O	AMER	Low	D	US	35
3	15	O	AMER	Mid	B	CA	15
4	25	P	AMER	Low	D	US	35
4	25	O	AMER	Low	B	CA	15

The final output should look like below:

Scenario	ID	Form	BU	Level	Prod	Ctry	Sum
1	10	R	APJ	High	D	NZ	8
2	12	P	EMEA	Mid	C	UK	120
3	15	O	AMER	Mid	B	CA	15
4	25	P	AMER	Low	D	US	35

Tried different things but couldnt get a solution, Any help will be much appreciated.

[binuacs]

@ANARK one way of doing this

[Qiu · Accepted solution]

@ANARK 
I would see this as a basic sorting problem.

The level with "words" can not be sorted as we want, so we can convert them to integer then sort.

[ANARK]

Thanks, Let me try to implement and share update.

[ANARK]

Thanks for this, worked like I expected.