Excel shows name twice

Question

I got a problem with my Excel, where it shows a same name twice, if the 'racers' have the excact same time. For example in the picture the racers 7 & 9 and racers 5 & 10 have the same time, but in the Start grid it shows the same name twice. It should be 4. Racer7 5. Racer9 & 9. Racer5 10. Racer10

The Function of Cell I3 =IF(OR(ISBLANK(B3);ISBLANK(C3));"";INDEX($B$3:$B$32;MATCH(J3;$D$3:$D$32;0))) (I have to use format ';' instead of ',') Function of cell J3 =IFERROR(SMALL($D$3:$D$32;H3);"")

Link to the file (does not work in Google Sheets & the functions have to use local formatting)

xlsx file

Answer 1

A solution in old school array-formula-style would be:

Note It's an array-formula which needs to be confirmed through CTRLSHIFTENTER

=IF(OR(ISBLANK(B3),ISBLANK(C3)),"",INDEX($B$1:$B$32,SMALL(IF($D$1:$D$12=J3,ROW($J$1:$J$12)),COUNTIF($J$1:$J3,J3))))

The IF checks the timelist for the current time value and gives all matching lines back which are getting ranked by small. COUNTIF counts the occurences of the current time up to the current line.

Answer 2

MATCH and RANK Getting Sick When Handling Time

---

Formulas

COMMA

[D3]    =IF(NOT(ISNUMBER(C3)),"",ROUND($D$1-A3*"00:10,0"-C3,8))
[H3]    =IF(ISNUMBER(K3),RANK(J3,D$3:D$32,2),"")
[I3]    =IF(IFERROR(INDEX($B$3:$B$32,SMALL(IF($D$3:$D$32=J3,ROW($J$3:$J$32)-ROW(J$3)+1),COUNTIF($J$3:$J3,J3))),"")=0,"",IFERROR(INDEX($B$3:$B$32,SMALL(IF($D$3:$D$32=J3,ROW($J$3:$J$32)-ROW(J$3)+1),COUNTIF($J$3:$J3,J3))),""))
[J3]    =IFERROR(SMALL($D$3:$D$32,A3),"")
[K3]    =IFERROR(J3-J$3,IF(I3="","","disqualified"))

COLON

[D3]    =IF(NOT(ISNUMBER(C3));"";ROUND($D$1-A3*"00:10;0"-C3;8))
[H3]    =IF(ISNUMBER(K3);RANK(J3;D$3:D$32;2);"")
[I3]    =IF(IFERROR(INDEX($B$3:$B$32;SMALL(IF($D$3:$D$32=J3;ROW($J$3:$J$32)-ROW(J$3)+1);COUNTIF($J$3:$J3;J3)));"")=0;"";IFERROR(INDEX($B$3:$B$32;SMALL(IF($D$3:$D$32=J3;ROW($J$3:$J$32)-ROW(J$3)+1);COUNTIF($J$3:$J3;J3)));""))
[J3]    =IFERROR(SMALL($D$3:$D$32;A3);"")
[K3]    =IFERROR(J3-J$3;IF(I3="";"";"disqualified"))

Why is MATCH 'miscalculating' to '7' instead of '6' in cells 'I6' and 'I7' in OP's worksheet (formula in 'D3')?

Time has a ton of decimals so I guess it's 'seeing' the values in 'D8' and 'D9' as different values. To avoid this you can round the values. If you want to use only these values it is enough to round them to 8 decimals for the numbers to be recognized as different even by a millisecond. If you want to sum them there might be some inaccuracies. In OP's case 8 decimals is more than enough.

RANK (formula in 'H3') is also 'miscalculating' if no rounding.

Why the long formula?

Best try it with and without the IF statement and see for yourself.

Here's a Hint:

Answer 3

For this you need a tie breaker. The unfortunately best way of doing this is using a helper column. In my test sheet I used column E but the column could, of course, be anywhere. More importantly, it could be hidden. In this column you enter a formula like

=D3+ROW()/10^8

The point is that the addition must be so small that it makes no difference to the result on rounding. So, the number of results you treat in this may makes a difference. If you find that the addition changes the result in the last row, increase the exponent. The change I made add 0.0001 seconds to each result, multiplied by the row number: 0.0001 in the first row, 0.0002 in the second, 0.0003 in the third etc. Check the results in the 10th and 100th row.

Now the results in column E are all different and it's these results that are used in columns J and I.

[J3] =SMALL($E$3:$E$32,H3)

and

[I3] =INDEX($B$3:$B$32,MATCH(J3,$E$3:$E$32,0))

There will be no more duplicates but the "winner" of a draw is decided by his position in the list.