How do I put items from a list or column into other boxes in Excel
 

I have a column of about 1000 names and I want to make 3x3 grids, each
containing 9 random names from this list in Excel. How can I get Excel to do
this for me?

How do I put items from a list or column into other boxes in Excel
 

I'm assuming that your looking for the grids to be populated with random
names "without replacement".

Say your list of names was in Column Z, from Z1 to Z1000.

Enter this formula in Y1:
=Rand()
And copy down to Y1000.

Now, enter this formula in say A1:

=INDEX($Z$1:$Z$100,RANK(INDEX($Y$1:$Y$100,(3*ROWS( $1:1))-3+COLUMNS($A:A)),$Y$1:$Y$100))

Drag across to copy to C1.

Then drag down to copy as needed.

You can add borders between every 3 rows to separate your grids,
OR,
You can even clear a row of formulas to delineate your grids.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Ferby" wrote in message
...
I have a column of about 1000 names and I want to make 3x3 grids, each
containing 9 random names from this list in Excel. How can I get Excel to
do
this for me?

How do I put items from a list or column into other boxes in Excel
 

Of course, you should add a 0 to each of the range sizes in the formula to
bring them up to 1000.
I did my test on 100.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
I'm assuming that your looking for the grids to be populated with random
names "without replacement".

Say your list of names was in Column Z, from Z1 to Z1000.

Enter this formula in Y1:
=Rand()
And copy down to Y1000.

Now, enter this formula in say A1:

=INDEX($Z$1:$Z$100,RANK(INDEX($Y$1:$Y$100,(3*ROWS( $1:1))-3+COLUMNS($A:A)),$Y$1:$Y$100))

Drag across to copy to C1.

Then drag down to copy as needed.

You can add borders between every 3 rows to separate your grids,
OR,
You can even clear a row of formulas to delineate your grids.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Ferby" wrote in message
...
I have a column of about 1000 names and I want to make 3x3 grids, each
containing 9 random names from this list in Excel. How can I get Excel to
do
this for me?

How do I put items from a list or column into other boxes in E
 

Thanks for this brilliant response, I hope that you don't mind me asking if
you could help with a few related issues?!

Firstly, is there any way to add bias to some of the names so they are
selected more frequently?

Secondly is there any way to publish the 3x3 grid or open it via the
internet, so that every time someone opens it, it is populated anew in their
browser?

Thanks again.

Ferbs

"RagDyeR" wrote:

I'm assuming that your looking for the grids to be populated with random
names "without replacement".

Say your list of names was in Column Z, from Z1 to Z1000.

Enter this formula in Y1:
=Rand()
And copy down to Y1000.

Now, enter this formula in say A1:

=INDEX($Z$1:$Z$100,RANK(INDEX($Y$1:$Y$100,(3*ROWS( $1:1))-3+COLUMNS($A:A)),$Y$1:$Y$100))

Drag across to copy to C1.

Then drag down to copy as needed.

You can add borders between every 3 rows to separate your grids,
OR,
You can even clear a row of formulas to delineate your grids.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Ferby" wrote in message
...
I have a column of about 1000 names and I want to make 3x3 grids, each
containing 9 random names from this list in Excel. How can I get Excel to
do
this for me?

How do I put items from a list or column into other boxes in E
 

Can't help at all with the Internet stuff, and as far as your question on
adjusting probability ... that's also a gray area with me.

However, I would possibly think that a common-sense approach might be the
unequal listing of names, which should tilt the scales of random
probability.

If you've got a 1,000 names ... each listed once ... and you choose to
increase the chances of 10 of those names, add additional entries of those
names to the list.
Just like buying 10 lottery tickets.
You've got a 10 times better chance then a person who buys a single lottery
ticket.
So you increase the size of the list to 1,100, and 10 names are listed 10
times instead of once.

Just a thought.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ferby" wrote in message
...

Thanks for this brilliant response, I hope that you don't mind me asking
if
you could help with a few related issues?!

Firstly, is there any way to add bias to some of the names so they are
selected more frequently?

Secondly is there any way to publish the 3x3 grid or open it via the
internet, so that every time someone opens it, it is populated anew in
their
browser?

Thanks again.

Ferbs

"RagDyeR" wrote:

I'm assuming that your looking for the grids to be populated with random
names "without replacement".

Say your list of names was in Column Z, from Z1 to Z1000.

Enter this formula in Y1:
=Rand()
And copy down to Y1000.

Now, enter this formula in say A1:


=INDEX($Z$1:$Z$100,RANK(INDEX($Y$1:$Y$100,(3*ROWS( $1:1))-3+COLUMNS($A:A)),$Y
$1:$Y$100))

Drag across to copy to C1.

Then drag down to copy as needed.

You can add borders between every 3 rows to separate your grids,
OR,
You can even clear a row of formulas to delineate your grids.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Ferby" wrote in message
...
I have a column of about 1000 names and I want to make 3x3 grids, each
containing 9 random names from this list in Excel. How can I get Excel
to
do
this for me?