Random Number

[DTPower]

I am attempting to create a draft for a sports pool that i am involved in. There are 18 members in the pool with 18 weeks of competition. Each week there will be a "draft" to determine in which order members select their picks. I can use the =RANDBETWEEN(1,18) but this does not limit the results to a single occurrence for each week. Basically each member should have a number from 1-18 for each of the 18 weeks, no two members can have the same number each week.

Does anyone have any suggestions?

 

[shg]

In one columns, enter the numbers 1 to 18. In an adjacent column, enter the formula =RAND() and copy down. Sort by that column to put the numbers in random order.

 

[mikerickson]

"Basically each member should have a number from 1-18 for each of the 18 weeks, no two members can have the same number each week. "

That condition forbids the use of random. Consider that the numbers for the last week are completely determined by the preceeding weeks.

You could create one Master chart that meets your conditions and then use shg' method to randomly re-order the rows of that master chart. (re-ordering must be all entrys for that row in the Master chart.)

A similar method could be use to randomly reorder the columns of the Master chart after the rows have been reordered.

 

[DTPower]

I appreciate the reply however that will not eliminate duplicate results for each week. I need a draft order each week for 18 weeks with no member having the same number in two weeks. everyone should have rankings 1-18.

 

[Dryver14]

try just typing 1 to 18 down one column
then use that number as your starting poisition from 1 to 18

so 1234
then 2345 etc
when you reach 18 on that row start at 1 again
for all 18 rows