random order in a set of words in 1 column

[Guest]

I'm formulating a questionnaire with a number of stimulus words to be
presented. I want to be able to put them in random order for a number of
different documents

 

[David McRitchie]

Hi ...,
The easiest way would be to create a helper column with the function =RAND()
and sort on the random number generated then remove the helper column.
More information on the function in your Excel Help.

 

[Max]

One way ..

Assume the words are in A1:A10

Put in B1:
=INDEX(A:A,MATCH(SMALL(C:C,ROWS($A$1:A1)),C:C,0))

Put in C1: =RAND()

Select B1:C1, fill down to C10

Col B will return a random shuffle of the words in A1:A10

Press F9 key to regenerate a fresh shuffle

Freeze the results elsewhere with a copy > paste special > values elsewhere

 

[Max]

Here's an extension to the earlier set-up if we want generate more than 1
randomized shuffle at one go, using Data > Table ..

Assuming the earlier set-up is in Sheet1

In a new Sheet2
--------------------
Put in A3: =Sheet1!B1
Copy down to A12

Put in B2: =COLUMNS($A$1:A1)
Copy across to say, K2
(assume we want to generate 10 sets at one go)

Select A2:K12
Click Data > Table
Enter in box for "Row input cell": A1
Leave "Column input cell" box empty
Click OK

What we'll get in B3:K12 are 10 randomized sets of words, in B3:B12, C3:C12,
.... K3:K12. (11 sets actually if you include the one in A3:A12)

Pressing F9 will regenerate another 10/11 randomized sets at one go
(Freeze results elsewhere as before)

Extend to suit ..