CODEWITHSUNDEEP
excelsortingmax
Alex Rowe
Alex Rowe

Reputation: 11

Excel - Returning the name of the highest ranked scorer within one of three groups in a table

enter image description here

This is probably one of those questions with a very simple solution.

Due to security concerns, I can't go into much detail as to the specifics of my data, but to surmise:

I have three groups of people, divided up into three groups - "red", "blue", and "green". Each person gets a score, and its all contained in a table. I need a formula that returns the name of the highest scoring member of each group (as opposed to the highest score overall), and I can't add any helper columns.

I've tried to use XLOOKUP to look up the MAX Values of each group, but the result returns the first person who matches that score - who might be in a different group. I've also tried to use Index/Match to match the highest MAXIFS score which worked until I used in a table and then it fell apart.

Upvotes: 1

Views: 1054

Answers (4)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

You can use Pivot Tables to get the output you want:

enter image description here

Just take fields Group and Name into rows section and Score into values section. Then apply a filter values on field Name group and choose last option TOP 10 and set it up to show only top 1:

Filter data in a PivotTable

enter image description here

Why using Pivot Tables if there are formulas? Because in case there is any tie, the Pivot Table will show up those values and formulas won't!

Just as example, let's say Collin got also 46 Score like Arturo. Both of them are the highest value so both of them are the 1st ones. Pivot Table will show this:

enter image description here

Easy to update and make less errors.

Upvotes: 2

JvdV
JvdV

Reputation: 75890

Pivot Table + Power Pivot:

Since you are working with an actual table, here is a fun alternative using a Pivot Table with a Power Pivot measure written in Data Analysis Expressions (DAX):

  • Select any cell in your table;

  • On the 'Insert' tab, click 'Pivot Table' and tick the 'Add this data to the Data Model' box before clicking 'OK';

  • Now go to the 'Power Pivot' tab and choose to add a new measure in the 'Calculations' group;

  • Give it any reasonable 'Measure name' and in the formula section use:

    =MAXX(TOPN(1,VALUES(Table1[Name]),CALCULATE(MAX(Table1[Score])),DESC),[Name])

  • Now hit 'OK';

  • Select the Pivot Table you created and make sure that you have the 'Group' field selected as 'Rows';

enter image description here

Now we have it, a calculated field using our own measure showing the top scorer per Group:

![![enter image description here

When new data is added to the model (or changed), you'd need to refresh the Pivot Table >> 'Table Design' >> 'Refresh All'.

Formula:

If a formula is a must, then maybe try:

![![enter image description here

Formula in E7:

=LET(x,UNIQUE(Table1[Group]),HSTACK(x,MAP(x,LAMBDA(x,@SORT(FILTER(Table1,Table2[Group]=x),3,-1)))))

PowerQuery:

A 3rd option could be to run a quick analyses in PowerQuery:

  • Select any cell in your table and on the 'Data' tab, click 'From Table/Range' in the 'Get & Transform Data' group;
  • Select the 2nd column, and choose the 'Group By' option on the 'Transform' tab;
  • Make sure that you make the following aggregations in the 'Advanced' tab:

enter image description here

  • Now, on the 'Add Column' tab, choose the 'Custom Column' in the 'General' group;

  • Pick a name for your new column, I choose 'Best' and add the following formula:

    = Table.Max([Helper2], "Score")

If done correctly, your data will now look like:

enter image description here

  • Now, on the 'Add Column

  • Click the 'Expand' button (top right in 'Best.Name') and just select only 'Name';

  • Delete the two helpers, and you'll be good to go and close & load the query back to Excel:

enter image description here

Here is the M-code for completion's sake:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Group", type text}, {"Score", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Helper1", each List.Max([Score]), type nullable number}, {"Helper2", each _, type table [Name=nullable text, Group=nullable text, Score=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Best", each Table.Max([Helper2], "Score")),
    #"Expanded Best" = Table.ExpandRecordColumn(#"Added Custom", "Best", {"Name"}, {"Best.Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Best",{"Helper1", "Helper2"})
in
    #"Removed Columns"

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36880

Try FILTER() with few other functions.

=INDEX(SORT(FILTER($A$3:$C$13,$B$3:$B$13=E5),3,-1),1,{1,3})

To get all by single formula and spill result dynamically, try-

=HSTACK(UNIQUE(B3:B13),
BYROW(UNIQUE(B3:B13),LAMBDA(x,INDEX(SORT(FILTER(A3:C13,B3:B13=x),3,-1),1,1))),
BYROW(UNIQUE(B3:B13),LAMBDA(x,INDEX(SORT(FILTER(A3:C13,B3:B13=x),3,-1),1,3))))

enter image description here

Upvotes: 1

Dominique
Dominique

Reputation: 17527

MaxIFS() to the rescue:

This is the formula:

=MAXIFS(B1:B8,A1:A8,"Red")

This is how to use it:

enter image description here

Upvotes: 0

Related Questions