Random Name Picker from List (Lottery Woes)

[drex]

Hello everyone,

I've scoured the forums with no luck to possible ways to figure out a problem, I've been having...

I've run into a big of a snag when dealing with a type of Lottery system. Long story short, a group of people receive a coupon/ticket that, at the end of the month, should be "picked out of a (large) hat" and win a prize.

I currently have the number of tickets they have in a chart like this;

Name # of Tickets
Charles 4
Cherly 9
Robert 5
Jane 2
David 3


I was thinking of having them listed multiple times (ColumnB) along with a unique identifier (ColumnA);


ColumnA ColumnB
Row1 1 Charles
Row2 2 Charles
Row3 3 Charles
Row4 4 Charles
Row5 5 Cherly
Row6 6 Cherly
Row7 7 Cherly
Row8 8 Cherly
Row9 9 Cherly
Row10 10 Cherly
Row11 11 Cherly
Row12 12 Cherly
Row13 13 Cherly
Row14 14 Robert
Row15 15 Robert
Row16 16 Robert
Row17 17 Robert
Row18 18 Robert
Row19 19 Jane
Row20 20 Jane
Row21 21 David
Row22 22 David
Row23 23 David


This way, all I would have to do is use

=VLOOKUP(RANDBETWEEN(1,23),$A:$B,2,0)

... and hold down F9 for a while while the names flash by and when releasing, I would have the winner's name!


My problem is that I do not know how to list the name N times the tickets they won...

Does anyone have any ideas?


Thanks in advance!
~d

 

[shg]

Here's one way:

      ---A--- --B--- --C--- -------------------------D--------------------------
  1    Name   # tkts lookup                                                     
  2   Charles      4      0 C2 and down: =SUM(B1, C1)                           
  3   Cherly       9      4                                                     
  4   Robert       5     13                                                     
  5   Jane         2     18                                                     
  6   David        3     20                                                     
  7                      23                                                     
  8                                                                             
  9   Winner         Robert C9: =INDEX(A2:A6, MATCH(RANDBETWEEN(0, C7-1), C2:C7))

 

[p45cal]

I did it almost exactly as shg4421 did it so I won't repeat, however I wrote a udf to do it too.

Excel Workbook
	A	B	C	D	E
1	Name	# of tickets		Winner:	Cheryl
2	Charles	0
3	Cheryl	23
4	Robert	15
5	Jane	10
6	David	3
Sheet

backed up by an inefficient function in a standard module:

Function Winner(xxx)
TicketCount = Application.Sum(xxx.Columns(2))
ReDim TheArray(1 To TicketCount)
i = 1
For Each cll In xxx.Columns(1).Cells
  For j = 1 To cll.Offset(, 1).Value
    TheArray(i) = cll.Value
    i = i + 1
  Next j
Next cll
Winner = TheArray(Application.WorksheetFunction.RandBetween(1, TicketCount))
End Function

Just hold down F9 for as long as you want!
It copes with blanks and zeroes in the second column properly.
Tested by recording the results and the frequency was pro rata the number of tickets bought per person.
But do you believe it's right?!

 

[drex]

@shg4421: This is perfect.. exactly what I needed.

@p45cal: I will test yours though if it's the UDF of shg4421's solution, I applaud you as well. Though I agree; it is very hard to quantify it's accuracy. Theory will have to do

Thank you both for all of your help!

 

[energman58]

If it really is a lottery you are running you probably want a better random number generator than RAND() or the VBA rnd function. They are fine for a bit of fun for the office sweepstake or scheduling staff to tasks but if there is real money at stake you probably want a more "industrial" version. They are pretty cheap to buy and are signifcantly better than the Excel versions. Just Google for Random Number Generator Excel or something similar and you will find plenty. Mersenne Twister based pseudo random numbers are the current first choice for things like Monte Carlo Simulation where you need to generate numbers quickly - you can get something pretty good for about $25.

 

[energman58]

This is s mersenne twister implementation for VB I found on teh web

http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/VERSIONS/BASIC/vb_mersennetwister_rev1.txt

 

[p45cal]

p45cal: I will test yours though if it's the UDF of shg4421's solution, I applaud you as well. Though I agree; it is very hard to quantify it's accuracy. Theory will have to do

It's an implementation of your solution!
I tested it by recording the results it produced (with sample sizes in the 10s to 1000s) and checked that the results were in proportion to the number of tickets people had bought - they were correct.

<embed src="http://www.box.net/embed/8bvg2acjbbmgghc.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">