Pick 10 random names from a table

[Serafin54]

we are trying to alleviate some projects and trainings and hoping to streamline how we select people for different tasks. In a week, 10 people will be selected at random but depending on the week, the list (which in excel which is a table due to the changing number of names) to choose from could be between 20 and 45. I found some macros that select one name but they seem to repeat. Is there a way to randomize picking 10 names from the list without duplicating?

Mahalo

 

[sheetspread]

Put a rand() next to each name, sort, and pick the top 10 or whichever else.

 

[Logit]

.
VBA method :

Sub PickNamesAtRandom()


Dim HowMany As Integer
Dim NoOfNames As Long
Dim RandomNumber As Integer
Dim Names() As String 'Array to store randomly selected names
Dim i As Byte
Dim CellsOut As Long 'Variable to be used when entering names onto worksheet
Dim ArI As Byte 'Variable to increment through array indexes


Application.ScreenUpdating = False


HowMany = Range("D3").Value
CellsOut = 6


ReDim Names(1 To HowMany) 'Set the array size to how many names required
NoOfNames = Application.CountA(Range("A:A")) - 1 ' Find how many names in the list
i = 1


Do While i <= HowMany
RandomNo:
    RandomNumber = Application.RandBetween(2, NoOfNames + 1)
    'Check to see if the name has already been picked
    For ArI = LBound(Names) To UBound(Names)
        If Names(ArI) = Cells(RandomNumber, 1).Value Then
            GoTo RandomNo
        End If
    Next ArI
    Names(i) = Cells(RandomNumber, 1).Value ' Assign random name to the array
    i = i + 1
Loop


'Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)


    Cells(CellsOut, 4) = Names(ArI)
    CellsOut = CellsOut + 1


Next ArI


Application.ScreenUpdating = True


End Sub

Download sample workbook : https://www.amazon.com/clouddrive/share/J5wqvhMs2GOuzsiMboHzIOzlpd4aKbQy3OTpEXpA8cx

 

[Serafin54]

Thank you Logit. This is perfect!

 

[Logit]

You are welcome.