Generate Random Records based on a condition

vonnie_e

New Member
Joined
Oct 19, 2006
Messages
3
Hi there

I've had a look through the board to find the answer to this but if I've missed it I apologise.

We've taken ticket requests for a show where each person can request up to 6 tickets. Each person's details appears in one Excel row.

We have 100 tickets to give away, how do I get a random selection where the total number of requests=100.

I can do it using sampling if every row equates to the same number of requests but in this case it doesn't, can anyone help???????????
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, vonnie_e
Welcome to the Board !!!!!

this seems like doable :)
it looks to me as a "weighted random-pick"
  A  B  
1 A  2  
2 B  4  
3 C  6  
4 D  3  
5 E  4  
6 F  1  

test

[Table-It] version 06 by Erik Van Geit

in this case client C would have the most chance to be picked
if this is what you want then please provide an example to work with

kind regards,
Erik
 

vonnie_e

New Member
Joined
Oct 19, 2006
Messages
3
Hi and thanks

I would have, for example the following list

Name Number tickets
Joe 4
Kate 3
Sophie 2
Charlene 5
Tom 2
Eve 1
Paul 3

There are 20 tickets requested here by 7 people. I only have 6 tickets to give away - I need a random list of names where the requests add up to 6. i.e. Joe+Tom or Kate+Sophie+Eve

I hope this makes sense!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I need a random list of names where the requests add up to 6
this seems very illogic to me: how can you be sure your clients would buy tickets so that you could find combinations adding up to 6

also: what's the answer to my previous question ? (weighted random pick)
wouldn't Charlene, who bought most tickets, deserve more chance to get a free ticket ?
 

vonnie_e

New Member
Joined
Oct 19, 2006
Messages
3

ADVERTISEMENT

Sorry if I've not worded this very clearly, it's turning my head to mush!!!!

I have 20 free tickets to a concert to give away. One person can request up to 6 free tickets. No-one is more entitled to receive any than anyone else, it should be totally random (or as random as possible) as long as the total number of requests adds up to 20.

So I want Excel to return the names of random people selected as long as between them their total number of requests is 20.

Thanks!
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
There's random and then there's random. You need to define the rules by which you distribute the tickets. Simply saying "random" doesn't help much.

First, a non-random approach. Simply allocate tickets as a % of those requested. So, if people ask for a total of 100 tickets and you have only 20, each gets 20/100 or 1/5 of their request. This is often used to allocate stock shares when an offering is oversubscribed.

Next, if you decide to allocate tickets so that people either get their complete request or nothing, simply organize the list in a random sequence. Now, go down the list, allocating as many tickets as each person has requested until you run out of them.

Optionally, you may decide to allocate tickets so that as many people as possible get at least a few tickets. In this case, sort the list in random sequence. Now, allocate 1 ticket to each going down the list. If you have tickets left over, start over at the top of the list and allocate 1 more ticket to those whose request is still not satisfied. Repeat the process until all tickets are exhausted.

A variant of the above would be to allocate tickets in groups of two. This might make sense for most social events that people attend as a couple.

To organize the list in random order see the first option on the page
Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

Sorry if I've not worded this very clearly, it's turning my head to mush!!!!

I have 20 free tickets to a concert to give away. One person can request up to 6 free tickets. No-one is more entitled to receive any than anyone else, it should be totally random (or as random as possible) as long as the total number of requests adds up to 20.

So I want Excel to return the names of random people selected as long as between them their total number of requests is 20.

Thanks!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
sorry if I brought any confusion here pisting two replies which belonged to another thread

I'll wait for your next comments
 

Forum statistics

Threads
1,136,990
Messages
5,678,985
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top