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

#### Boler

##### New Member
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?

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

##### MrExcel MVP
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.

#### NateO

##### Legend
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

##### New Member
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.

Book1
ABCDEFGHIJ
1JerryHarry
2Mary
3Larry
4Barry
5Harry
6
7
Sheet4

Replies
5
Views
454
Replies
9
Views
134
Replies
2
Views
65
Replies
1
Views
218
Replies
7
Views
85

### Forum statistics

1,127,407
Messages
5,624,587
Members
416,036
Latest member
eloisa manzanarez

### 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.

### Which adblocker are you using?

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

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