how do I create and extract a random order from list in Excel?

[Michael]

Hi All,

I have a list of names and I want to randomly sort them and extract one of
the sorted names in order, then randomise again, select, randomise, select
etc... i.e. Say I have 4 names:
Fred
Betty
Wilma
Barney

I want to randomly order the names, say it results in
Fred 1st
Wilma 2nd
Barney 3rd
Betty 4th

I then want to have say the first name (Fred) copied to another cell,
Say the list of names was in Cells A1:A4
so Fred would be copied to C1.

Then the remainder of the list is randomised again resulting in say
Betty 1st
Wilma 2nd
Barney 3rd
Then Betty's name would be put in/copied to cell C2

Then the list is randomised again, and so on and so forth.
Similar in a way to the way numbers for a lottery may be drawn, but I want
to use Names, not numbers.

Can Anyone Help?

Thank you

 

[Gary''s Student]

put your names in column A. In column B enter:

=RAND() and copy down

In another column, say column C, enter:

=INDIRECT("A" & ROW()) and copy down

sort A & B by B this will randomize both A & B

 

[Michael]

Thanks, that was sort of helpful, but unfortunately this does not allow a
re-randomisation using only the remaining three names in the example list,
and then the remaining two names etc after the first name is drawn. Also it
involves manually sorting the list.

 

[Ragdyer]

Why do it one at a time?

Why not just have your entire list re-display in random order?

Say you place your original list in an out-of-the-way location, say Z1 to
Z4.

In Y1, enter the Rand function:
=RAND()
And copy down to Y4.

Now, enter this formula in C1, and copy down to C4.

=INDEX(Z$1:Z$4,RANK(Y1,Y$1:Y$4))

This gives you a random display of your names.
Each time you hit <F9>, you'll get a new random order.

 

[Michael]

Yes, I would agree if that is what I want, but I do not want the entire list
re-displayed in random order every time.
The first time I want (n) names randomised
The second time I want (n-1) names where the list does not include the first
name from the first randomised list. If I randomise all again, I risk
getting duplicate names.
The third time I want (n-2) names randomised and so on.
Ultimately I will take the selcted names drawn and then match/cross
reference them to two/three other lists, I have a good idea how to do the
cross referencing, but am stuck on the randomising of a decreasing list.

 

[Ragdyer]

I don't understand your need to take the top name from a random ordered
list.

You could just as well copy the formula from C1, and enter it wherever you
want that first name to display.
Then, copy the formula from C2, and enter that wherever you want the 2nd
name to display, and do the same with the other formulas in C3 and C4.

You'll get *no* duplicates when you hit <F9>, and you'll have the 4 random
names in whatever location you need them.