Forum Discussion
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!
=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.
CoachSF I would use pivot table, provided you can organize the data as shown in the very crude example.
