This post will guide you how to pick a random name from a list in Excel. How do I select random value from a list or a table with a formula in Excel 2013/2016.
Table of Contents
1. Pick Random Name from a List
Assuming that you have a list of names in range B1:B6, and you want to pick random names from this list. How to do it. You can use a formula based on the INDEX function, the RANDOMBETWEEN function and the COUNTA function to achieve the result. You can use one of the following formulas:
Type this formula into a blank cell and press Enter key on your keyboard. And then drag the Fill Handle down to list the random names that you need. And then press F9 key, it will generate the different name list randomly.
Note: you should know that the name list will be changed when you refresh the current worksheet.
2. Pick a Random Name from a List Using VBA Macros
In this tutorial, we’ll learn how to pick multiple random names from a list in Excel. We’ll use a VBA macro to do this, allowing you to select a source range of names, a destination range for the random names, and specify how many random names you want to pick.
Step1: To get started with the VBA method, press Alt + F11 to open the VBA editor in Excel.
Step2: In the VBA editor’s menu, click “Insert” and select “Module” to insert a new module.
Step3: Copy and paste the provided VBA code into the module.
Sub PickMultipleRandomNames() Dim SourceRange As Range Dim DestinationRange As Range Dim NumNamesToPick As Integer Dim i As Integer ' Select the source range containing names On Error Resume Next Set SourceRange = Application.InputBox("Select the source range containing names:", Type:=8) On Error GoTo 0 If SourceRange Is Nothing Then MsgBox "No source range selected. Macro aborted." Exit Sub End If ' Check if the selected range is valid If SourceRange.Rows.Count < 1 Or SourceRange.Columns.Count <> 1 Then MsgBox "Please select a single-column range with at least one name. Macro aborted." Exit Sub End If ' Select the destination range for the random names On Error Resume Next Set DestinationRange = Application.InputBox("Select the destination range for the random names:", Type:=8) On Error GoTo 0 If DestinationRange Is Nothing Then MsgBox "No destination range selected. Macro aborted." Exit Sub End If ' Ask the user how many random names to pick On Error Resume Next NumNamesToPick = InputBox("Enter the number of random names to pick:") On Error GoTo 0 If NumNamesToPick <= 0 Then MsgBox "Please enter a valid number of random names. Macro aborted." Exit Sub End If ' Pick and place the random names DestinationRange.ClearContents ' Clear any existing contents in the destination range For i = 1 To NumNamesToPick Dim RandomRow As Long RandomRow = Application.WorksheetFunction.RandBetween(1, SourceRange.Rows.Count) Dim RandomName As String RandomName = SourceRange.Cells(RandomRow, 1).Value DestinationRange.Cells(i, 1).Value = RandomName Next i ' Inform the user MsgBox NumNamesToPick & " random names placed in the destination range." End Sub
Step4: Save your workbook and close the VBA editor.
Step5: Press ALT + F8 to open the “Macro” dialog. Select the “PickMultipleRandomNames” macro and click “Run“.
Step6: A dialog box will appear, prompting you to select the source range containing names. Click and drag to select the range and press “OK.”
Step7: Another dialog box will appear, this time asking you to choose the destination range where the random names will be placed. Select a cell in your worksheet and press “OK.”
Step8: A prompt will appear, asking you to specify how many random names you want to pick. Enter the desired number and click “OK.”
Step9: The macro will pick the specified number of random names and place them in the destination range.
You’ve successfully learned how to pick multiple random names from a list in Excel using a VBA macro.
3. Video: Pick a Random Name from a List in Excel
This video tutorial where we’ll explore two methods for picking a random name from a list using both formula-based and VBA macro methods in Excel.
4. Related Functions
- Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
- Excel COUNTA function
The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
- Excel RANDBETWEEN Function
The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….