Help with randomly displaying values from a list.

S

Scott

I have a list of 144 names. In a separate column, I want to break that list
up into groups of 4. Obviously, I know how to do that. But how do you
randomly select those names without duplicates?
For instance:
Names in Cells N7:N150.
Starting in cell C7 through C150, I want to randomly list those names.

Any help would be appreciated.
 
J

JoeU2004

PS: I forgot to mention....
put the =RAND() formulas into Y1:Y144, then copy-and-paste-special-value
into X1:X144

Of course, you could put =RAND() into X1:X144 and
copy-and-paste-special-valule back to X1:X144, overwriting the formula with
constants.

The advantage of putting the formulas into a different range is that it
makes it easy to generate a new random list. The disadvantage is that the
volatile RAND expressions continue to be recalculated with every edit
anywhere in the workbook :-(. That can become costly as number of such
formulas increases. I don't think that would be a problem with just 144.


----- original message -----

JoeU2004 said:
Scott said:
I have a list of 144 names. [....] But how do you
randomly select those names without duplicates?
For instance: Names in Cells N7:N150.
Starting in cell C7 through C150, I want to randomly list those names.

Many ways to do this. One way....

Put =RAND() into a column of 144 cells, say X1:X144. Then, starting in
C7, put the following fomula and copy down:

=INDEX($N$7:$N$150,RANK(X1,$X$1:$X$144))

Note that the first X1 is a relative reference; it changes as you copy the
formula down. Everything else is absolute references.

Tweak....

Unfortunately, RAND is a volatile function. There are many ways to avoid
the problems that creates. One way: actually put the =RAND() formulas
into Y1:Y144, then copy-and-paste-special-value into X1:X144. You can
hide Y1:Y144 as well as X1:X144.


----- original message -----

Scott said:
I have a list of 144 names. In a separate column, I want to break that
list
up into groups of 4. Obviously, I know how to do that. But how do you
randomly select those names without duplicates?
For instance:
Names in Cells N7:N150.
Starting in cell C7 through C150, I want to randomly list those names.

Any help would be appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top