Random selections non numeric

[clf]

Hello... The Data Analysis Sampling tool does not allow
you to randomly select data that does not contain
numbers. I need to just randomly select X number of
samples from an existing spreadsheet. Does anyone know
how thats done when the data is alphanumeric?

Thanks....

 

[Bob Umlas]

You can enter =rand() in a parallel range, then sort by this column & pick
the top X rows as your random sample.

Bob Umlas
Excel MVP

 

[RagDyer]

One way to accomplish this:

Data in column A1:A100

In an out of the way column, say column Z, enter this in Z1:
=RAND()
and copy down to Z100

Enter this formula in the column where you wish to have the random
selections returned:

=INDEX(A1:A100,RANK(Z1:Z100,Z1:Z100))

NOW, select the cell containing this formula.
*DO NOT* use the fill handle.
Click and drag down the *selection*, as far as you wish the number of random
choices to be returned.
The formula cell should be white, and the rest of the selection is grey, and
the grey cells are empty when you release the mouse.
While every thing is *still* selected, hit
<F2>
then do
<Ctrl> <Shift> <Enter>

If done correctly, this will *automatically* enclose the formulas in curly
brackets.

You should now have your random selections.
You can renew the selections by simply hitting <F9>.
--

HTH,

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



Hello... The Data Analysis Sampling tool does not allow
you to randomly select data that does not contain
numbers. I need to just randomly select X number of
samples from an existing spreadsheet. Does anyone know
how thats done when the data is alphanumeric?

Thanks....

 

[JE McGimpsey]

If you use the RandInt() function at

http://www.mcgimpsey.com/excel/randint.html

you can select X cells and array-enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(A:A,RANDINT(1,COUNTA(A:A)))