Random Selection of items in Excel?

[mnpremo]

I'm creating an Excel spreadsheet that has several hundred numbers listed (in
no particular order) in the first column. The numbers are all in this
format: 000.00.00.00

I was wondering if Excel has the ability to go through and select several of
these numbers randomly from the column so that I can then have a random
sample from my list of hundreds of numbers.

Thank you-
Maureen

 

[Conrad Carlberg]

Hi Maureen,

I've always found that the simplest way is to select as many adjacent cells
in column B as you have numbers in column A. With those column B cells
selected, enter this formula:

=RAND()

and hold down CTRL as you press Enter. This will enter pseudo-random numbers
in column B, one for each value in column A. At this point, it usually makes
sense to convert your =RAND() formulas to values. Select them, choose Edit |
Copy,. then choose Edit | Paste Special, click the Values option, and click
OK. (There's also a keyboard shortcut to do this, but it's not a big
timesaver.)

Now, suppose that your existing numbers are in cells A1:A495. If you've put
random values in B1:B495, select A1:B495 and sort (either ascending or
descending) on column B. This will give you a random ordering of the numbers
in column A. If you want a randomly selected set of column A values, just
select A1:A5.

 

[Conrad Carlberg]

Sorry, I should have said this:

"If you now want a randomly selected set of five column A values, just
select
any five contiguous values in column A."

 

[mnpremo]

Thank you Conrad - I will give this a try. I'm also open to other
suggestions if anyone has any.

Thanks again-
Maureen

 

[B. R.Ramachandran]

Hi,

If the numbers are listed in column A (say A2:A2001), enter the following
formula in B2.

=INDIRECT("A"&RANDBETWEEN(2,2001))

where the two numbers in the parentheses are the first and last row numbers
of your data range. Fill the formula down in column B to as many random
samples as you want.
The sampling would dynamically change every time Excel recalculates it
(when you open the workbook or whenever you hit the F9 button). If you want
a set of random samples to stay static, you have to do Copy-Paste
Special-Values.
If, in addition to the random samples, you also want to know their cell
addresses,
you could use the following formulas in B2 and C2.

In B2, ="A"&RANDBETWEEN(2,2001)
In C2, = INDIRECT(B2)

and fill down the formulas to as many random samples as you want.

Note: The RANDBETWEEN function is in the Analysis Toolpack. If it is not
available in you Excel, run the Setup program to install the toolPak and then
enable it by using the Add-Ins command on the Tools menu.

Regards,
B. R. Ramachandran

 

[Jim Cone]

Maureen,

My Excel add-in Special Sort has a random sort option.
It also provides 11 other sort options including: color,
numbers only, decimal ( similar to your example), dates,
and reverse.
Comes with a Word.doc install/use file.
Free upon request - remove XXX from my email address.

Regards,
Jim Cone
San Francisco, USA
(e-mail address removed)


"mnpremo"
<[email protected]>
wrote in message
Thank you Conrad - I will give this a try. I'm also open to other
suggestions if anyone has any.
Thanks again-
Maureen

 

[mnpremo]

Thanks everyone - these methods work great, but is there any way to generate
all the numbers in random order all at the same time - instead of one by one?

Thanks again!

 

[mnpremo]

I just got it to work - Just what I was looking for. THANKS!!!

 

[Conrad Carlberg]

Hi Maureen,

Glad to hear it.