How do you choose a random number....lottery style?

[Boler]

We have a listing of 1500 names and we are trying to figure out a way to randomly choose one person on the list. How do we set it up to randomly choose a name or number?

 

[PaddyD]

Hi - welcome to the board!

One way:

1) set up a series of random numbers next to the list of names:

=rand()

2) select an abitrary member of the list (e.g. the largest).

but - caveat:

none of the excel rand() functions are set up to return only unique numbers. This may not matter for you ('cos you picking an arbirtary member, I guess you don't mind dealing with the duplicates by just picking the first instance on the list:

=INDEX(A1:A10,MATCH(LARGE(B1:B10,1),B1:B10,0),1)

change references to siut.

paddy

 

[NateO]

Hello, depending on your sheet layout, like if the names are in a column, a procedure like the following could be used:<pre>
Sub Ran()
Dim upr As Integer, lwr As Integer, Mynum As Integer
Randomize
upr = 1500 'change to last row number
lwr = 1 'change to first row number
Mynum = Int((upr - lwr + 1) * rnd + lwr)
Range("a" & Mynum).Select 'Change a to appropriate column
'msgbox Range("a" & Mynum) ' If you just want a pop-up
End Sub</pre>

This will select a random cell in the range of names, this works with a1:a1500.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-04 18:07

 

[cnank]

have the names in column A. In column B use the RAND function. Then havea a macro to sort the A&B columns by column b. In the cell you want to display the "winner" just have it =A1 from the section of the worksheet (or other worksheet) that these two columns are in. Every time you run the macro to sort, the rand function will assign a new number to the column b and a new name will appear incell A1, thereofre a new name will appear in your winning cell.

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Jerry		Harry
2	Mary
3	Larry
4	Barry
5	Harry
6
7
Sheet4