MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Numbering Schedule


Posted by Paul Pratt on February 10, 2002 2:18 PM

I would like to know how to make a spreadsheet make a schedule of random numbers from 1 to 36 in rows and columns.

1 2 3 4 5 6 etc
4 6 5 1 2 3 etc
5 3 1 2 5 4 etc
etc
I am having prolems doing this.


Posted by faster on February 10, 2002 2:37 PM

You can use =RANDBETWEEN(1,36), to use the function
you may need to go to tools/add-ins and add
Analysis ToolPak (if you don't have it already loaded)

Posted by Paul Pratt on February 10, 2002 2:54 PM

Ok that seemed to work but what do I do about the duplicate numbers in the columns and rows?

Posted by Paul B on February 10, 2002 3:01 PM

Have a look at
http://www.xl-logic.com/pages/formulas.html
Rand_unique

Posted by Yogi Anand on February 10, 2002 8:06 PM

Hi Paul:
This is how I have done in the past (I wouldn't be surprised that there is a better way), if I wanted 36 unique numbers selected randomly, I would ask for say 100 random numbers, then I would filter this list to retain only unique numbers. Plese post back whether it works for you!

Yogi Anand
ANAND Enterprises www.handtech.com/anand

Posted by Mark W. on February 11, 2002 7:29 AM

Suppose you wanted a 6x6 array or random numbers between 1 and 36 inclusive...

1. Enter the series 1 through 36 in A1:A36.
2. Enter the formula, =RAND(), in B1:B36.
3. Enter the values, {0,1,2,3,4,5}, in E1:J1.
4. Enter the values, {1;7;13;19;25;31}, in D2:D7.
5. Enter the formula, =INDEX($A:$A,$D2+E$1), into
E2, copy right to cell J2, and copy down to cell
J7.

Cells E2:J7 now contains your 6x6 array of random
values. Resort A:B based on column B when you
need a new set of random values.