Excel randomly select name from list with multiple entries

Question

I have an excel 2007 worksheet with employee names in column A and total number of entries in column B. I need to be able to randomly select x number of employee names from the total number of entries, allowing for the fact that some will have multiple entries.

For example:

Amy............30   
Brian..........12
Charlene.......15
Michael.........1
Nathan..........7

What is the best way to do this?

My initial thoughts are:

1) find the max() of column B occurances of a random number in another column, like C. Then find the top values for all of that new column.

2) create a VBA array of all of the potiential entries and randomly pick one from there.

3) loop through all of the names in column A and create a temp worksheet with column B instances of each, then assign a random num generator and choose the top n.

Having said that, there may be something a lot easier. I am not sure where to begin. Normally I can find code that is similar to what I need, but I am not having any luck. Any help that you can offer would be appreciated.

Thank you in advance.

Can you elaborate on what you're tyring to do? E.g. can you return the same name more than once? And if so, can you select it more often than the number of entries the name has? — Bram
– Bram, Commented Sep 17, 2013 at 20:53
The same name can be selected more than once, but not more than the number of entries. After it is pulled, it should not be counted. This essentially pulling names from a hat where each entry is it's own ticket. Each person can have 1 to n tickets total. Does this help explan the question? — mriley
– mriley, Commented Sep 17, 2013 at 21:15
@mriley - Your pulling names from a hat analogy is a lot better. If you started a new question focused on that I am sure you would get more responses. — Stepan1010
– Stepan1010, Commented Sep 17, 2013 at 21:23

Stepan1010 · Accepted Answer · 2013-09-17 21:02:37Z

2

I would probably do something like this if I understand your question correctly(I just read your question title):

SO1

SO2

answered Sep 17, 2013 at 21:02

Stepan1010

3,1341 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mriley Over a year ago

Thank you. That is part of what I need and would work if there was one entry for each line. In my case, Bob would have 60 entries, Joe 45, Schmidt 15, and the rest just one. The number of entries is indicated in another column, like F. How could I add that in to account for multiple entries?

Stepan1010 Over a year ago

@mriley Do you want to weight the chance that each will occur by how many entries it has? If this is the case you can probably use Bram's suggestion below(which is a little more involved). If this is NOT the case then you can just copy and paste unique values so you don't have duplicate entries.

Bram Over a year ago

Create an extra column (or an array holding this data if you do it in vba), that computes the cumulative number of entries up to given name. Generate a random number between 1 and the total number of entries and use the technique described above. This will give you sampling from the distribution with replacement.

mriley Over a year ago

Thank you Stepan1010 and Bram. Between your answers, I solved my issue. In column F, I put the total number of entries for each person. For the NameID column, I used D2 =1, D3 =D2+F2, D4 =D3+F3,... And Lastly, A2 =RANDBETWEEN(1,SUM($F:$F)). This works exactly how I needed it to and didn't have to resort to VBA. Thank you again.

Bram Over a year ago

You're welcome. Please mark Stepan1010's answer as solving your problem, part of the etiquette here.

Collectives™ on Stack Overflow

Excel randomly select name from list with multiple entries

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related