MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Picking Random Entries from list

Posted by Dave George on June 27, 2001 4:45 AM

How can I get Excel to pick random entries from a list of text cells. For example, I have a large list of names and I would like to pick names randomly and show the result in another cell

Posted by Dax on June 27, 2001 5:36 AM


You could use the Offset function combined with the Rand function. Say your list is in range A2:A11 you could use this formula to return a random name from the list:-


Posted by Joe Was on June 27, 2001 9:05 AM

VB Macro to Random pick from a list.

This macro will run from a "Hot-key" (Macro - Macros [select macro] - Options [assign key]) or a form button (View - Toolbars - forms [select, size and place button]) Note click to edit button lable.

Open macros and copy & paste the code below or (Macro - Macros [type myRnd as the macro name) and copy the code below, which is between Sub & End in between the macto Sub and End.

The code is commented to tell you how to set it up for your name-list sheet address. JSW

Sub myRnd()
'Find a random name in a existing names list.
'By Joe Was, 6/27/2001.

Dim myOrder As Range
Dim myName
Dim mySelect As Variant

'Note: The 20 below = the ending ROW of your names list.
' The 1 below = the starting ROW of your names list.
mySelect = Int((20 * Rnd) + 1)

'Note: The "A" below is the column where your names list is.
myName = Range("A" & mySelect)

'Put the answer in a screen message box.
MsgBox myName
End Sub

Posted by Joe Was on June 27, 2001 9:19 AM

Code to Random pick from a list and put to sheet cell.