Forum Discussion

CoachSF's avatar
CoachSF
Copper Contributor
Nov 16, 2023

Sort by location and top 5 ?

I have a list of location, races and results. I am looking to filter the results to only show the top 3 fastest times per location and event. For example:

Column A = New York, Boston, Kansas City etc.

Column B = races per location e.g.1-8

Column C  = finish times by all entrants (could be 12 entrants)

 

My goal is display the results as follows for columns A, B, C:

Row 1: New York, Race 1, 1st place

Row 2: New York, Race 1, 2nd place

Row 3: New York, Race 1, 3nd place

Row 4: New York, Race 2, 1st place

...

Row 34: Boston, Race 7, 1st Place

Row 35, Boston, Race 7, 2nd Place etc.

 

I can run the custom filter, but I cant get it show only the top 3 regardless of the amount of entrants!

Excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 16, 2023

    CoachSF 

    =DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(Tabelle6[[Location]:[Heat]]))),LAMBDA(x,y,VSTACK(x,TAKE(SORT(FILTER(Tabelle6,(Tabelle6[Location]=INDEX(CHOOSECOLS(UNIQUE(Tabelle6[[Location]:[Heat]]),1),y))*(Tabelle6[Heat]=INDEX(CHOOSECOLS(UNIQUE(Tabelle6[[Location]:[Heat]]),2),y))),3,1),3)))),1)

     

    With Office 365 or Excel for the web you can use this formula along with a dynamic table to spill the results dynamically.