I am trying to pick more than one name from a list of names in one column. The selection needs to be random (or as near to random as Excel functions can be), and there can be no duplication of the selected names. This isn't a lottery or anything with prizes, whereby somebody might later argue about how random the selection was, it is to pick out a couple of employee names for random weekly alcohol testing. The proper list will comprise about 40 names.
I have created a spreadsheet with two sheets containing different formulae which I think will work, but I need somebody to check and make sure that I have got it right or maybe suggest an alternative approach.
The sheet named DATA contains column headers in row 1. A list of names occupies cells A2:A21.
Cells B2:B21 contain the =RAND() function to create a random number.
Cells C2:C5 are where 4 randomly selected names should be placed.
The formula in C2 is: =INDEX($A$2:$A$21,RANK(B2,
As I understood it, the RANK function more or less sorts the random numbers in column B into ascending order, picking the top 4 numbers ? and selects the names from column A that lie alongside each of those 4 numbers.
Is that correct?
Have I used the formulae correctly?
The sheet named TEST uses a formula that I found online, but I don't fully understand how it works. From what I can understand it looks like the same name could be picked twice.
The spreadsheet will be run on two computers, one with Office 2003 and the other with Office 2010, hence the 97-2003 XLS format.
I would have considered using an Access Database, but it's years since I even tried to create anything in Access and I think there are compatibility issues between 2003 and later versions of Access.
Could somebody check out what I have and make suggestions.