# Sort by location and top 5 ?

Copper Contributor

# 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!

2 Replies

# Re: Sort by location and top 5 ?

@CoachSF I would use pivot table, provided you can organize the data as shown in the very crude example.

# Re: Sort by location and top 5 ?

=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.