results table for speed trials, is this even possible?

Have been wrestling with this one for a while but getting nowhere. Hope someone can help?

So I have a table of competitors, each with a number

x1.jpg

(there will be many more than 10 competitors in the real competition, hence my trying to automate the process)

Competitors will then be doing speed trials (timed by hand, for various practical reasons)

and the results (competitor number, time ) put straight into a results table thus

x2.jpg

and ideally what I want is as results are added to the results table, row by row, for the table of competitors to automatically populate with the results posted to the correct competitor, thus:

x3.jpg

Once we have the results in place we can select the fastest time etc, all that stuff is fine. I just need to get the results into the competitor table.

If it's too complex to have it so that competitors can do multiple runs, and the formula can recognise that it's a second run or third run and put it in the appropriate column, then we will change the format and have three different results tables, and no competitors will be allowed to do a second run until everyone has completed their first, etc.

And just to add an extra twist to it, it has to work on Excel 2007 ...

Dummy table attached.

Very very many thanks for any help or advice from you excel gurus!

Not sure, For Number, put this on I2 and press ENTER as array formula, press all together CTRL+SHIFT+ENTER, then copied down
=IFERROR(INDEX($B$2:$B$11,INT(SMALL(IF($C$2:$E$11<>"",(ROW($C$2:$E$11)-ROW($C$2)+1)*10^5+COLUMN($C$2:$E$11)-COLUMN($C$2)+1),ROWS($A$1:A1))/10^5)),"")

For Time, put this on J2 and ENTERED as array formula as well, then copied down as necessary:
=IFERROR(INDEX($C$2:$E$11,SMALL(IF(($B$2:$B$11=$I2)*($C$2:$E$11<>""),MATCH(ROW($C$2:$E$11),ROW($C$2:$E$11)),""),COUNTIF($I$2:I2,I2)),1*(SMALL(IF(($B$2:$B$11=$I2)*($C$2:$E$11<>""),MATCH(ROW($C$2:$E$11),ROW($C$2:$E$11))+1*MATCH(COLUMN($C$2:$E$11),COLUMN($C$2:$E$11)),""),COUNTIF($I$2:I2,I2))-SMALL(IF(($B$2:$B$11=$I2)*($C$2:$E$11<>""),MATCH(ROW($C$2:$E$11),ROW($C$2:$E$11)),""),COUNTIF($I$2:I2,I2)))),"")

That's insane. Works perfectly!!! Thank you soooo much 63falcondude. You're an absolute legend.

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.