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?

--
Regards,
Fred

On Sun, 1 Oct 2006 16:44:36 -0600, "Fred Smith" wrote:

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

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.


--
skatonni
------------------------------------------------------------------------
skatonni's Profile: http://www.officehelp.in/member.php?userid=4186
View this thread: http://www.officehelp.in/showthread.php?t=1216057

Posted from - http://www.officehelp.in

On Mon, 2 Oct 2006 06:13:15 +0530, skatonni
wrote:


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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fred Smith" wrote in message
...
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?

--
Regards,
Fred

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

Biff

"Max" wrote in message
...
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fred Smith" wrote in message
...
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?

--
Regards,
Fred

This caveat also applies to the "standard" sort routine <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
Theoretically, dups are still possible using RAND but highly unlikely with
a small distribution.

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" wrote in message
...
This caveat also applies to the "standard" sort routine <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
Theoretically, dups are still possible using RAND but highly unlikely
with a small distribution.

Biff

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
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 wrote:
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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ken Johnson" wrote in message
ps.com...

Max wrote:
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

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:
the proper formula ..

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

Biff

"Max" wrote in message
...
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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
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

I don't know what you meant by:
the proper formula ..

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
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:
the proper formula ..

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

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

"Max" wrote in message
...
I don't know what you meant by:
the proper formula ..

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
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:
the proper formula ..

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

Biff

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.

--
Regards,
Fred


"Max" wrote in message
...
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fred Smith" wrote in message
...
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?

--
Regards,
Fred

You're welcome, Fred !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fred Smith" wrote in message
...
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.

--
Regards,
Fred