Help with randomly displaying values from a list.
 

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.

Help with randomly displaying values from a list.
 

Add a new column a put in thed new column

=RAND() and copy down colun

then highlight new column and copy and pastespecial value only. Next sort
on new column.

"Scott" wrote:

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.

Help with randomly displaying values from a list.
 

"Scott" wrote:
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" wrote in message
...
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.

Help with randomly displaying values from a list.
 

PS: I forgot to mention....

I wrote:
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" wrote in message
...
"Scott" wrote:
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" wrote in message
...
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.