Create Random Generated Sweepstakes

[LonelyITGuy]

Hi All

New to this forum and first time poster. So please play nice.

I'm wanting to setup a excel spreadsheet that can act as a randomn generated sweepstake. You know the one. All names get put in a hat and each person takes one out that is there. There are no duplicates.

I had a look at the spinning wheel concept on the forum and thought a similar thing might do the job. However once the ball lands on a number it is then withdrawn from the next persons try.

Thus giving people a chance to draw the top sweepstake. Is anyone bored enough to help me out.

 

[Sandeep Warrier]

Hi & welcome to the Board!

How about something like this?

Column A is the list of names. Column B & C are helper columns.

Column F contains randomly selected names from the list (without duplicates). The list will change every time you press F9 (calculate).

Excel Workbook
	A	B	C	D	E	F
1	Name List	Random Numbers	Rank			Randomly Generated List Without Duplicates
2	Name 1	0.94938568	3			Name 15
3	Name 2	0.015132732	20			Name 11
4	Name 3	0.664366008	9			Name 1
5	Name 4	0.260112454	15			Name 16
6	Name 5	0.828730073	5			Name 5
7	Name 6	0.122743551	19			Name 8
8	Name 7	0.262346278	14			Name 18
9	Name 8	0.80523578	6			Name 14
10	Name 9	0.418476192	11			Name 3
11	Name 10	0.367294743	12			Name 17
12	Name 11	0.983472915	2			Name 9
13	Name 12	0.14531836	18			Name 10
14	Name 13	0.235254452	16			Name 20
15	Name 14	0.680053829	8			Name 7
16	Name 15	0.989946627	1			Name 4
17	Name 16	0.894314115	4			Name 13
18	Name 17	0.588686013	10			Name 19
19	Name 18	0.685454234	7			Name 12
20	Name 19	0.153295984	17			Name 6
21	Name 20	0.315350911	13			Name 2
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=RAND()
C2	=RANK(B2,$B$2:$B$21)-COUNTIF($B$2:B2,B2)+1
F2	=INDEX($A$2:$A$21,MATCH(SMALL($C$2:$C$21,ROWS($F$1:F1)),$C$2:$C$21,0))

 

[LonelyITGuy]

That is awesome and I had been reading on how to complete this exact task.

However can this be slightly change to make it that the cells only change when each person presses f9.

Sort of
Person 1 presses f9 and gets there random name
Person 2 presses f9 and gets there random name but person 1 name stays as it was before.

 

[Sandeep Warrier]

I don't think this can be done by formula. You'll need to use VBA for it. Let me know if you are ok to do that.