Generate Random Records based on a condition

[vonnie_e]

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???????????

 

[erik.van.geit]

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]

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]

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]

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]

EDIT: this was a reply to another thread, sorry

 

[erik.van.geit]

IDEM

 

[tusharm]

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]

sorry if I brought any confusion here pisting two replies which belonged to another thread

I'll wait for your next comments