Google Sheet - Return unique id with maximum mark associated-CodePudding

Good day

I'm trying to return the maximum mark of each student. If the fails the training, they can try a new attempt, and my summary sheet should include only unique value with highest mark obtained.

Example of data:

| | A |B |
| -| - | - |
|1 |email | score|
|2|[email protected] | 1|
|3 |[email protected] | 3|
|4 |[email protected] | 3|
|5 |[email protected] | 4|
|6 |[email protected] | 5|
|7 |[email protected] | 4|
|8 |[email protected] | 7|
|9 |[email protected] | 1|
|10 |[email protected] | 7|

And i would like to return this table:

 | | D |E |
|- | - | - |
|1 |email | score|
|2| [email protected] | 4|
|3 |[email protected] | 3|
|4 |[email protected] | 7|
|5 |[email protected] | 7|

Code used in COL D2: <br>
    =UNIQUE(A2:A,FALSE,FALSE)<br>
Code used in COL E2: <br>
    =if(G2<>"", ARRAYFORMULA(VLOOKUP(G2,D2:E,2,false)),"")<br>
Code used in COL E3: <br>
   =if(G3<>"", ARRAYFORMULA(VLOOKUP(G3,D3:E,2,false)),"")<br>

Is there any way to optimize this?

CodePudding user response：

In D2 paste

=UNIQUE(A2:A)

In E2 paste this formula

=MAX(TRANSPOSE(FILTER($B$2:$B, $A$2:$A=D2)))

CodePudding user response：

use:

=SORTN(SORT(A2:B, 2, 0), 9^9, 2, 1, 1)

A2:B     range
2        sort by 2nd column
0        in descending order
9^9      return all rows
2        group by
1        first column
1        in ascending order