Forum Discussion
Identifying the highest values from multiple sources
I'm working on putting together seasonal statistics from a sporting league (Cricket).
One of the stats I need is each player's highest individual score from the season, but with more than 300 players in the League, manually finding each player's top score during the season is not practical.
So far I have used "sortby" to produce an alphabetical list of each player and their individual scores from the season in descending order. I have a small example below of where I am.
| Player | Scores | Not Out |
| Player A | 66 | 0 |
| Player A | 47 | 0 |
| Player A | 45 | 0 |
| Player A | 42 | 0 |
| Player A | 33 | 0 |
| Player A | 18 | 0 |
| Player A | 14 | 0 |
| Player A | 11 | 0 |
| Player A | 5 | 0 |
| Player A | 4 | 0 |
| Player A | 4 | 0 |
| Player A | 2 | 0 |
| Player A | 1 | 0 |
| Player A | 0 | 0 |
| Player B | 7 | 0 |
| Player B | 4 | 0 |
| Player B | 3 | 0 |
| Player B | 2 | 0 |
| Player B | 2 | 1 |
| Player B | 1 | 0 |
| Player B | 0 | 0 |
| Player B | 0 | 0 |
| Player B | 0 | 0 |
| Player B | 0 | 1 |
| Player C | 105 | 1 |
| Player C | 50 | 0 |
| Player C | 31 | 0 |
| Player C | 23 | 0 |
| Player D | 97 | 0 |
| Player D | 94 | 0 |
| Player D | 69 | 0 |
| Player D | 69 | 0 |
| Player D | 20 | 0 |
| Player D | 15 | 0 |
| Player D | 13 | 0 |
| Player D | 11 | 0 |
| Player D | 10 | 0 |
| Player D | 7 | 0 |
| Player D | 2 | 0 |
| Player D | 0 | 0 |
| Player D | 0 | 0 |
Now I just want extract each player's highest score from the season, so that I end up with just the top score for each player.
What I am trying to end up with from the above array would look like the following
Player Scores Not Out
| Player | Scores | Not Out |
| Player A | 66 | 0 |
| Player A | 5 | 0 |
| Player C | 105 | 1 |
| Player D | 97 | 0 |
Transform your scores data into a structured table and apply this formula. Let's say you call it "Table1". Make sure it's sorted (Player, ascending order and Score and Not Out both in descending order.
=LET( players, UNIQUE(Table1[Player]), score, XLOOKUP(players,Table1[Player],Table1[Scores]), notout, XLOOKUP(players,Table1[Player],Table1[Not Out]), HSTACK(players, score, notout) )
2 Replies
Hi Iceberg​,
Try this formula:
=LET( gb, GROUPBY(A2:A42, B2:B42, MAX,, 0), VSTACK(A1:C1, HSTACK(gb, BYROW(gb, LAMBDA(a, XLOOKUP(TRUE, BYROW(a = A2:B42, AND), C2:C42))))) )HTH.
IlirU
Transform your scores data into a structured table and apply this formula. Let's say you call it "Table1". Make sure it's sorted (Player, ascending order and Score and Not Out both in descending order.
=LET( players, UNIQUE(Table1[Player]), score, XLOOKUP(players,Table1[Player],Table1[Scores]), notout, XLOOKUP(players,Table1[Player],Table1[Not Out]), HSTACK(players, score, notout) )
