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

Boler

New Member
Joined
Nov 3, 2002
Messages
31
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?
 

Some videos you may like

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

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Legend
Joined
Feb 17, 2002
Messages
9,700
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
Joined
Jul 16, 2002
Messages
23
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top