Excel random allocations

QuestionEdit

I knowcan be used to generate random names from a list. However i wish to know how to create a random generator that picks a name from a list once and puts it in one cell, it then cannot pick that name again. I want this to work for several cells on one spreadsheet . The principle is to allocate people in positions at random. I have tried doing this myself, however my limited skills are failing me on this one. Any help will be very much appreciated.

AnswerEdit

Sorry - wrong formulas. Use this:

=INDEX(A:A,MATCH(SMALL($E$1:$E$45,ROW(A1)),$E$1:$E$50,0))

A good way to allocate positions is to enter =RAND() in a parallel range, copy/paste values, then use the LARGE function (or SMALL) to get the names out randomly. For example, if you have names in A1:A50, enter =RAND() in E1:E50 (assuming column E is available), then after these are converted to values you can use =INDEX(A1:A50,LARGE(E1:E50,1)) for the first name, =INDEX(A1:A50,LARGE(E1:E50,2)) for the next, etc. Or, if you want to fill these down, you can use =INDEX(A$1:A$50,LARGE(E$1:E$50,ROW(A1)))

HTH