Randomly select numbers from a range

[Fred Smith]

I need to put the numbers from 1 to 54 in a list where each number is randomly
selected. A lot like choosing bingo balls, but once the ball is chosen, it can't
be chosen again.

How do I put 1 to 54 in random order with no duplicates and none missing?

 

[Ron Rosenfeld]

I need to put the numbers from 1 to 54 in a list where each number is randomly
selected. A lot like choosing bingo balls, but once the ball is chosen, it can't
be chosen again.

How do I put 1 to 54 in random order with no duplicates and none missing?

See this MSKB article:

http://support.microsoft.com/kb/213290/en-us
--ron

 

[Ron Rosenfeld]

The macro gets stuck in an endless loop if I try to get more than 85
percent of the range.

See if this usual response to this type of question is what you want.
Put the numbers 1 to 54 in column A. Enter =rand() in B1. Autofill to
B54. With the cursor in B1 sort. Column A will be in a different order
each time you sort.

Yes, I just noticed that loop issue. Your method seems simple and effective.
--ron

 

[Max]

Just one way to quickly set it up using formulas ..

Assuming the numbers 1-54 are listed in A1:A54
Put in B1: =RAND()
Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$54))
Select B1:C1, copy down to C54. (Hide away col B)

C1:C54 will return a random scramble of the numbers within A1:A54 (or
whatever's within A1:A54) . Re-generate the random scramble by pressing F9.

 

[Biff]

Theoretically, dups are still possible using RAND but highly unlikely with a
small distribution.

Biff

 

[Max]

This caveat also applies to the "standard" sort routine <g>

 

[Biff]

It does but with the sort routine normally you're pulling the numbers
directly from the source column so there's no chance of dupes if you write
the proper formula.

Biff

 

[Max]

It's the same "source" here, ie the helper col with: =RAND() filled down
correspondingly to match the source col.

I don't know what you meant by:

the proper formula ..

---

 

[Ken Johnson]

This caveat also applies to the "standard" sort routine <g>

I suppose the best solution would be to have the worksheet functions do
the bulk of the work then use a macro to copy the values into the table
after checking for the extremely unlikely duplicates. If duplicates are
found then the macro could just re-run itself. This also would avoid
the problem of the random 1 to 54 values in the table changing
everytime the worksheet calculates anything.

A macro can be re-run from within itself when a certain condition is
met can't it?

Ken Johnson

 

[Max]

Ken,
Being non-vba proficient, don't think I'm in a position to comment. My
suggestion to Fred was just a formula variant to the "standard" way
described in skatonni's response, which provides a way to get a new random
scramble of the source items within A1:A54 consigned to merely pressing F9.
That's all there is to it.

---

 

[Biff]

It's the same "source" here, ie the helper col with: =RAND() filled down

correspondingly to match the source col.

Oh, I'm calling column A the source.

I don't know what you meant by:

Whatever formula is used to pull the randomized numbers out of the source
column (column A).

Biff

 

[Max]

I don't know what you meant by:

Whatever formula is used to pull the randomized numbers out of the source
column (column A).

So by inference ..
this earlier one is not one of the "proper" formulas then ?

Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$54))

It seems to work well enough for the purposes here

---

 

[Biff]

I'm talking about using rand and then sorting and then using a formula to
pull the randomized numbers DIRECTLY from column A. Rand is used just as a
sort key and recalcs don't mess things up if you need the picks to remain
constant. Maybe put a button on the sheet with a sort macro to generate new
draws.

Biff

It seems to work well enough for the purposes here

Yes, it does. I was just pointing out that dupes are still a *possibility*.
You do know that I'm anal, don't you? <bg>

Biff

 

[Fred Smith]

Thanks, Max. That's exactly what I wanted.

I did a simple sum of column A and C to check for duplicates. I know in theory
this could still mask an error, but it was good enough for my purposes.

I also enjoyed your discussion with Biff, but my needs aren't that specific. I
can easily manually fix any error that creeps in.

 

[Max]

You're welcome, Fred !
Thanks for the feedback ..