How to distinguish duplicates using a SORT function

[ExcelColonist]

I am sorting data from a large table (called Games) for the top ten fastest running times, I will be continually adding new data to the table and the top ten times may change moving forward. I have attached a picture to be used as a visual, which also includes the function I am using.

The first column, is the table row the information is taken from

=MATCH(J2,Games[Time],0)

I eventually plan on adding an INDEX function to this equation so I can match the name to the time.

As you can see in the photo, there is a tie for first place at 9 minutes and 41 seconds, but both are sourced from the same row (row 119 in the table Games). This is the equation I am using for this.

=INDEX(SORT(Games[Time],1,1),SEQUENCE(10),1)

Both 9.41 times should be coming from their own unique rows in the table, does anyone have any ideas how I can accomplish this?

 

[jasonb75]

Match will always return the first result found (it does tell you this in the help file). For subsequent matches you need to use an array of some kind, the easiest would be something like

=IFERROR(AGGREGATE(15,6,ROW(Games[Time])/(Games[Time]=C2),COUNTIF(C$2:C2,C2))-ROW(Games[[#Headers],[Time]]),"")

Note that I've used C2 based on the screen capture as the relevance of J2 in the posted formula is not entirely clear.

 

[Fluff]

If you have the names in the table you can use something like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Name	Track	Time
2	Tom Pearce	Track 1	12.65				Dan'l Whiddon	9.26
3	Bill Brewer	Track 1	10.37				Peter Davy	9.76
4	Jan Stewer	Track 1	10.69				Tom Cobley	9.76
5	Peter Gurney	Track 2	14.12				Bill Brewer	10.37
6	Peter Davy	Track 2	9.76				Jan Stewer	10.69
7	Dan'l Whiddon	Track 2	9.26				Tom Pearce	12.65
8	Harry Hawke	Track 3	14.61				Peter Gurney	14.12
9	Tom Cobley	Track 3	9.76				Harry Hawke	14.61
Master

Cell Formulas
Range	Formula
G2:H9	G2	=INDEX(SORT(Table2,3,1),SEQUENCE(ROWS(Table2)),{1,3})
Dynamic array formulas.

 

[ExcelColonist]

Match will always return the first result found (it does tell you this in the help file). For subsequent matches you need to use an array of some kind, the easiest would be something like

=IFERROR(AGGREGATE(15,6,ROW(Games[Time])/(Games[Time]=C2),COUNTIF(C$2:C2,C2))-ROW(Games[[#Headers],[Time]]),"")

Note that I've used C2 based on the screen capture as the relevance of J2 in the posted formula is not entirely clear.

J2 is the cell to the right of the cell where the fastest times are first listed, as it descends it'll be J3, J4, J5 etc until the first top ten times are listed.

 

[ExcelColonist]

If you have the names in the table you can use something like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Name	Track	Time
2	Tom Pearce	Track 1	12.65				Dan'l Whiddon	9.26
3	Bill Brewer	Track 1	10.37				Peter Davy	9.76
4	Jan Stewer	Track 1	10.69				Tom Cobley	9.76
5	Peter Gurney	Track 2	14.12				Bill Brewer	10.37
6	Peter Davy	Track 2	9.76				Jan Stewer	10.69
7	Dan'l Whiddon	Track 2	9.26				Tom Pearce	12.65
8	Harry Hawke	Track 3	14.61				Peter Gurney	14.12
9	Tom Cobley	Track 3	9.76				Harry Hawke	14.61
Master

Cell Formulas
Range	Formula
G2:H9	G2	=INDEX(SORT(Table2,3,1),SEQUENCE(ROWS(Table2)),{1,3})
Dynamic array formulas.

Thank you very much, this solves my problem.

As a follow up question, if I wanted to create a MATCH function to the right of column H (column I) that returned the track number

=INDEX(Table2,MATCH(H1,Table2[Time],0),2)

It lists the tied times (9.76) both as Track 2, when one should be Track 3. What would be the method to avoid this repetition?

 

[Fluff]

How about

=INDEX(SORT(Table2,3,1),SEQUENCE(ROWS(Table2)),{1,3,2})

 

[Fluff]

Or if you just want the 10 fastest

=INDEX(SORT(Table2,3,1),SEQUENCE(10),{1,3,2})

 

[ExcelColonist]

J2 is the cell to the right of the cell where the fastest times are first listed, as it descends it'll be J3, J4, J5 etc until the first top ten times are listed.

Actually, I'm not sure how to edit a reply, but I figured out the answer to the follow up question 10 seconds after I posted it haha.

INDEX(Table2,MATCH(H1,Table2[Name],0),2)

Easy peasy.

But what would be the solution if Peter Davy ran Track 2 AND 3 for the exact same time of 9.76. How would this be taken into account? Same runner, same time.

EDIT: found the edit button.

 

[ExcelColonist]

How about

=INDEX(SORT(Table2,3,1),SEQUENCE(ROWS(Table2)),{1,3,2})

You're a super star, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.