Assign from a list of names randomly

[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.

 

[Jack Leach]

I am running excel 2003.

Have you tried the newsgroup devoted to Excel? This on is for VBA
programming in Microsoft Acccess...

http://www.microsoft.com/communitie...b45-8feb-4ce1-9a70-1d87d8dfb131&lang=en&cr=us


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[John W. Vinson]

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'd suggest you ask your question in an Excel newsgroup - this group is for a
different program, Microsoft Access.

 

[ryguy7272]

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").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 & "" & LastRow&)
'Recalc, then copy & paste the random numbers in place as values.
Calculate
Range("D" & HdgRow + 2 & "" & LastRow&).Copy
Range("D" & HdgRow + 2 & "" & 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 & "" & 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