Assign from a list of names randomly

  • Thread starter Thread starter bigjim
  • Start date Start date
B

bigjim

I would like to randomly assign people to different teams, from a list of
names. For example: I have a list of 30 people and I want to assign them to
six teams of five, randomly. I am running excel 2003.
 
I would like to randomly assign people to different teams, from a list of
names. For example: I have a list of 30 people and I want to assign them to
six teams of five, randomly. I am running excel 2003.
Click to expand...

I'd suggest you ask your question in an Excel newsgroup - this group is for a
different program, Microsoft Access.
 
I found this on another post; modified to do what I think you want to do:
Assuming things such as ID in ColA, Name in ColB, Location in ColC ... other
stuff to the right of this. Run this code:
Global Const StartSht = "Sheet1" 'Name of sheet with Name/Location data
Global Const NewSht = "SheetX" 'Name for the new sheet

Sub RandomPicker()
Dim LastRow As Long, Rng As Range, Txt As String
'Delete NewSht if it already exists
On Error Resume Next
Sheets(NewSht).Delete

'Copy StartSht as NewSht
Sheets(StartSht$).Copy Before:=Sheets(1)
ActiveSheet.Name = NewSht$
'Delete all colummns after B.
Columns("D:D").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft
'Enter a heading and formula to generate a random number in column C.
Range("D" & HdgRow).Activate
ActiveCell.FormulaR1C1 = "rand"
Range("D" & HdgRow + 2).Activate
ActiveCell.FormulaR1C1 = "=ROUND(RAND()*10000,0)"
'Find the last row of data.
LastRow& = Range("A" & Rows.Count).End(xlUp).Row
'Copy the random number formula down through the last row.
Range("D" & HdgRow + 2).Select
Selection.AutoFill Destination:=Range("D" & HdgRow + 2 & ":D" & LastRow&)
'Recalc, then copy & paste the random numbers in place as values.
Calculate
Range("D" & HdgRow + 2 & ":D" & LastRow&).Copy
Range("D" & HdgRow + 2 & ":D" & LastRow&).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Assign all the data to a range variable (for convenience).
Set Rng = Range("A" & HdgRow & ":D" & LastRow&)
'Sort the data by location and random number.
Rng.Sort Key1:=Range("B" & HdgRow), Order1:=xlAscending, Key2:=Range("D" &
HdgRow), _
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal

End Sub
 
Back
Top