Solved

Pick More Than One Name At Random From A List

Posted on 2014-04-14
2
541 Views
Last Modified: 2014-04-14
Hello Experts

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.

Name-Picker.xls
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,$B$2:$B$21))
C3:  =INDEX($A$2:$A$21,RANK(B3,$B$2:$B$21))
C4:  =INDEX($A$2:$A$21,RANK(B4,$B$2:$B$21))
C5:  =INDEX($A$2:$A$21,RANK(B5,$B$2:$B$21))

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.

Thanks

Bill
0
Comment
Question by:BillDL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 39998656
The most important thing is that Column B contains random numbers. Since that's true in your file, you can comfortably use RANK to select 4 people (though hardcore  mathematicians might have problems with that, but in example it's totally sufficient).
To double check, I opened the file 10 times, and JLO was selected twice, as was Forrest Gump. What more could you wish for?
0
 
LVL 38

Author Comment

by:BillDL
ID: 39998682
Thank you for that Kimputer.  I tried to count how many times one of the names came up if I pressed F9 repeatedly 208 times, the idea being that it would be the equivalent of one random selection per week for 4 years that I could average out and get an idea how random it really was.  I lost count after about 7 months ;-)

As long as you're happy with the formulae, then that's good enough for me, but I might have to get my IT department (and HR) to verify it for HR to agree though.  I just wanted to get a working example in place to present to them if needed.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question