Array of random numbers with no repeats

J

jim holland

Is there a way to have Excel generate a 168 element array
where each array element is between 1 and 240 and the
array has no duplicate numbers?
 
M

Max

One approach: Try this simple set-up?

Put in A1: =RAND(), copy down to A240
Name the range A1:A240 as : TBL

Select B1:B240

Put in the formula bar: =RANK(TBL,TBL)

Hold down Ctrl + Shift keys, press Enter
(It's an array formula)

Done correctly, Excel will wrap curly braces around
the formula, viz: {=RANK(TBL,TBL)}

In B1:B240 will be a random shuffle
of the numbers 1 - 240 without repeats

Put in D1: =INDIRECT("B"&4*ROW()-7+COLUMN())
Copy D1 across to G1, then copy down to G60

The above will re-arrange what's in B1:B240
into a 4 col x 60 row grid in D1:G60, transposing
4 consecutive col cells from B1:B240 at a time "row-wise"

So in D1:G60 will be a random matrix
of all the numbers 1 - 240 without repeats

Pressing F9 will regenerate another random shuffle
in both grids above

Now you have a range of options with which
to select your random array of 168 elements
from the base of 240.

For example, if you want the 168 elements
to be in a 4 col x 42 row grid,
just take any 42 consecutive rows from within D1:G60

Or take any 168 consecutive col cells from within B1:B240
 
M

Michael R Middleton

jim holland -
Is there a way to have Excel generate a 168 element array where each array
Click to expand...
element is between 1 and 240 and the array has no duplicate numbers? <

If you need to do this only one time, enter 1 through 240 in column A, enter
=RAND() in adjacent cells of column B (and optionally Edit | Copy followed
by Edit | Paste Special | Values), select a populated cell in A or B, choose
Data | Sort using column B, and pick the first 168 values in column A.

- Mike Middleton, www.usfca.edu/~middleton
 
Top