Selecting from a randomized list

[bigjim]

I would like to randomly assign people to different teams, from a list of
names. For example: I have a list of 50 people out of which I have selected
30 names. I want to randomly assign these 30 people to 6 teams of 5 each.

 

[Mike H]

Hi,

Let's assume your names are in Col A starting in A1. Put this formula in b1

=RAND()

copy down to the length of Col a
Select the 2 columns and sort on column B and the names will be randomley
grouped.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[bigjim]

I've got that done, but my problem is the new list that is sorted has names
and blanks interspersed throughout the list. I need to know how to select
the first five for team 1, the 2nd for team 2, etc. There will not always
be five per team or even six teams as this will vary, but if I can it to work
for this case I can make it work with the others. Sorry I wasn't clear on my
question.

Jim

 

[Joe User]

There will not always be five per team or even
six teams as this will vary, but if I can it to work
for this case I can make it work with the others.

Not necessarily. If you are not clear about your input and output
requirements, you are likely to get a solution that works for the special
case that you mentioned, but it is difficult to generalize.

Select the 2 columns and sort on column B and
the names will be randomley grouped.

[....]
I've got that done, but my problem is the new list
that is sorted has names and blanks interspersed
throughout the list.

I don't understand why. You said: "I have a list of 50 people out of which
I have selected 30 names". The reasonable inference is that the 30 names are
in 30 contiguous cells. I am sure that is what MikeH assumed. Is that not
the case?

If it is not the case, can you make it so easily?

If so, then MikeH's solution is probably the simplest to explain, if that
works for you. I would only add that in column C, I would enter the names of
each team repeated in contiguous cells as many times as the number of team
members for each team. You can add that after sorting. If you do it before
sorting, be sure to select only columns A and B (the names and the random
numbers) for the sort.

However, you are posting to the "Excel Programming" forum. That is usually
used for VBA questions (macros and UDFs), although the distinctions among
forums have blurred over the years. Are you looking for an Excel solution or
VBA solution, or don't you care?

Moreover, MikeH's approach can be tedious if you want a process that you can
repeat often or for many sets of 30 people. Are you looking for a less
labor-intensive approach?


----- original message -----

 

[bigjim]

I understand. I'll see if I can make it clearer. I have a list of names in
col B. B1 through B50. In Col A, before each name, there is a box. If the
user, puts an X in the box, that name will be used in the team selection.
When all the names have been selected, then I want to randomly place the name
into different teams, such as team A, Team B, etc. the teams may or may not
be of equal size depending on how many are selected. The ideal number for
each team will be 3, with the next ideal being 4, and the next being 5 per
team. I want to use VBA code so that once the names are selected, the user
will select the "form teams" button, and the code will randomize the selected
names and assign them to teams. The excel worksheet will list the selected
players in col L : If(a1="","",b1). Col M has rand() in each cell. I have
code that will sort col L and Col M together using Col M Ascending. the
result will be random but will contain some names and some blanks. The
problem I'm having is once this sort takes place, how do I assign them to
teams. I hope this explains things better and I sure do appreciate the help
I get from you guys.

Jim

There will not always be five per team or even
six teams as this will vary, but if I can it to work
for this case I can make it work with the others.

Not necessarily. If you are not clear about your input and output
requirements, you are likely to get a solution that works for the special
case that you mentioned, but it is difficult to generalize.

Select the 2 columns and sort on column B and
the names will be randomley grouped.

[....]
I've got that done, but my problem is the new list
that is sorted has names and blanks interspersed
throughout the list.

I don't understand why. You said: "I have a list of 50 people out of which
I have selected 30 names". The reasonable inference is that the 30 names are
in 30 contiguous cells. I am sure that is what MikeH assumed. Is that not
the case?

If it is not the case, can you make it so easily?

If so, then MikeH's solution is probably the simplest to explain, if that
works for you. I would only add that in column C, I would enter the names of
each team repeated in contiguous cells as many times as the number of team
members for each team. You can add that after sorting. If you do it before
sorting, be sure to select only columns A and B (the names and the random
numbers) for the sort.

However, you are posting to the "Excel Programming" forum. That is usually
used for VBA questions (macros and UDFs), although the distinctions among
forums have blurred over the years. Are you looking for an Excel solution or
VBA solution, or don't you care?

Moreover, MikeH's approach can be tedious if you want a process that you can
repeat often or for many sets of 30 people. Are you looking for a less
labor-intensive approach?


----- original message -----

I've got that done, but my problem is the new list that is sorted has names
and blanks interspersed throughout the list. I need to know how to select
the first five for team 1, the 2nd for team 2, etc. There will not always
be five per team or even six teams as this will vary, but if I can it to work
for this case I can make it work with the others. Sorry I wasn't clear on my
question.

Jim

 

[helene and gabor]

Hello bigjim,
This program does what you described.
My output said ( team numbers for all 30 people)
6,2,6,4,4,6,1,6,1,6,2,5,2,5,4,3,2,3,4,3,5,3,5,2,3,1,1,4,1 and 5.
5 people in each of the 6 teams.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Option Base 1
Sub team()
'30 people, 6 teams
Dim myarray As Variant
Dim mycount As Integer
Dim mytemp As Variant
'fill the array



myarray = Array(1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2,
3, 4, 5, 6, 1, 2, 3, 4, 5, 6)



For i = 1 To 30
Randomize
staticrnd = Rnd
kount = Int((31 - i) * Rnd()) + 1
'MsgBox "kount" & kount
Number = 0
For j = 1 To 30
If myarray(j) > 0 Then
Number = Number + 1
End If

If Number = kount Then
MsgBox "team" & myarray(j)
myarray(j) = 0
Exit For
End If

Next j



Next i

End Sub
------------------------------------------------------------------
The output is not very sophisticated, you have to push it thrue bit by bit.
It assigns 30 people (1-30) to 6 teams.
Each time you run it the output is different.

Good Luck!

Gabor Sebo

 

[helene and gabor]

I would like to randomly assign people to different teams, from a list of
names. For example: I have a list of 50 people out of which I have
selected
30 names. I want to randomly assign these 30 people to 6 teams of 5 each.

10 runs for assigning 30 people into 6 teams of 5 persons/team:

3 2 1 5 6 6 6 3 4 4 2 1 5 6 4 5 5 2 3 5 1 4 3 6 2 3 4 2 1 1
1 4 4 1 6 5 5 2 6 1 2 5 2 1 2 3 4 1 6 2 4 3 5 6 3 6 4 3 3 5
1 1 5 6 5 1 2 5 2 4 4 4 3 2 3 6 5 4 1 6 3 1 3 2 6 6 5 3 2 4
4 2 5 5 2 4 4 1 6 3 1 2 4 1 5 5 6 1 2 1 5 4 3 3 2 6 3 6 6 3
4 6 1 5 6 6 4 1 3 5 1 4 3 4 3 2 3 6 2 1 2 5 6 2 4 1 2 5 3 5
2 3 2 5 5 4 5 6 3 6 5 6 1 1 1 2 4 3 6 1 6 4 1 3 2 4 5 4 3 2
# 3 6 1 5 2 4 2 4 2 4 1 1 2 5 6 3 2 3 4 4 5 6 1 5 6 3 6 5 1
5 3 5 4 2 4 1 4 5 3 5 4 2 4 6 1 2 3 6 1 6 2 3 1 1 2 5 6 6 3
3 5 5 3 5 3 1 2 6 4 6 4 4 6 3 5 4 1 6 5 1 1 2 2 6 2 2 1 3 4
1 2 1 4 6 3 5 5 1 4 3 1 6 6 6 2 4 5 1 5 4 3 5 2 3 6 4 2 2 3

Best regards

Gabor Sebo

 

[Bernd P]

Hello,

I suggest to select a vertical range of 30 cells and to array-enter:
=RandInt(1,6,30,5)

Or select a horizontal range of 30 cells and array-enter:
=TRANSPOSE(RandInt(1,6,30,5))

My UDF RandInt you will find at
http://sulprobil.com/html/randint.html

With a sub calling my UDF you will have better control of
(re-)calculating the random numbers.

Regards,
Bernd