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
Dim LastRow As Long, Rng As Range, Txt As String
'Delete NewSht if it already exists
On Error Resume Next
'Copy StartSht as NewSht
ActiveSheet.Name = NewSht$
'Delete all colummns after B.
'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.
Range("D" & HdgRow + 2 & ":D" & LastRow&).Copy
Range("D" & HdgRow + 2 & ":D" & LastRow&).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'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" &
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
If this information was helpful, please indicate this by clicking ''Yes''.
"John W. Vinson" wrote:
> On Sat, 13 Mar 2010 15:44:04 -0800, bigjim <firstname.lastname@example.org>
> >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.
> John W. Vinson [MVP]