Round Robin in excel

I would like to make and run "round robin" tournaments, and know that it could be made using excel worksheet, but can't think of a way to do it. So, the problem is, how to make on one sheet names of the players participating in the event and automate with commands on the next sheet randomly who will with who play (so far I have made this using random numbers, but hos to connect name in the field A1 on Sheet1 with random nuber in the field A2 on the same sheet, so that on the Sheet 2 I get name with (or without) the random number) ?


Something like this is where I got stuck

ID of the contestant             Player name                    random number
  1.                                     Player 1                                 93
  2.                                     Player 2                                 46
  3.                                     Player 3                                 32
  4.                                     Player 4                                 66
  5.                                     Player 5                                 2



and on sheet2 if I list by values or random numbers I have to switch back to see who are those numbers representing


Thank You

 

Question Info


Last updated May 1, 2019 Views 16,375 Applies to:

what does random numbers mean?

ex: 93 means player 9 vs player 3? then shouldn't you better use 0903 to have more than 10 players?

Anyway, the way to get the player name (sheet1 B1 to B10) from player number (sheet1 A1 to A10) is:

in Sheet2, Column A are the player numbers

Column B, B1: =INDEX(Sheet1!$A$1:$C$10,MATCH(A1,Sheet1!$A$1:$A$10,0),2)

or  =Offset(Sheet1!$B$1,MATCH(A1,Sheet1!$A$1:$A$10,0)-1,0)

Drag down as you want

Fix 1 and 10 in multicell ranges (ex $A$1:$C$10) to match your data



Why Excel gives us so much but so little?

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

=RANDBETWEEN(1,100)

to get a random number

and in next sheet, I have to get randomly seated pairs of players

This above helps, and I'll start with that and find some use.
As I haven't been using excel for a while, I forgot some of the functions.

So, I have to get player from the same .xlsx, and previous sheet, that rolled highest with RANDBETWEEN function in one field, and next to him player who rolled second (I used =MAX('PRIZE POOL'!E9:E58) and =LARGE('PRIZE POOL'!E9:E58,2) to sort from the highest to lowest numbers, so now only I need to get instead of numbers in that or next to it field names instead of thenumbers)

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Here's a good alternative to your "do it yourself" approach.  I've used it in the past and it has been helpful.

 

http://www.devenezia.com/round-robin/forum/YaBB.pl?num=1142176312

 

HTH,

Eric

If this post is helpful or answers the question, please mark it so.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Take a look at my posts in this thread: http://answers.microsoft.com/en-us/office/forum/office_2007-excel/randomize-names-in-a-sheet-and-a-copy-and/3d089496-bca5-432b-a064-b33941b3937c

There is a link to an example file too.

Hope that helps.

Cheers
Rich

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi

all you have to do is replace the "searching" cell with your result:

Say you choose Index, Search cell in bold:

=INDEX(Sheet1!$A$1:$C$10,MATCH(A1,Sheet1!$A$1:$A$10,0),2)

this as explained would look to match what was in cell A1.

So in Sheet2, in column A, you have A1 Largest, A2 2nd largest ....

(this can be done with as you said) 

replace what you look for in the function A1 = INDEX('PRIZE POOL'!$A$1:$C$10,MATCH(LARGE('PRIZE POOL'!E9:E58,2) , 'PRIZE POOL'!$A$1:$A$10,0),2)

OR in B1 write  =INDEX('PRIZE POOL'!$A$1:$C$10,MATCH(A1,'PRIZE POOL'!$A$1:$A$10,0),2) 

(I replaced sheet1 with  PRIZE POOL)...

 

Why Excel gives us so much but so little?

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.