Random Team Generator allowing Duplicates on separate teams

[b_fruge]

Hello: I am working a rodeo benefit that allows people to enter up to 2 times for an event. I need to have all of the members randomly assigned to teams of 4 without pairing them with themselves since they can be on 2 teams. I will have all names in Column A, and the number of teams will be dependent on the number of entries (not set amount).

 

[shg]

Name	Team
Alan	1
Alan	2
Cain	1
Dana	1
Eric	1
Fran	2
Gary	2
Gary	3
Ivan	2
Jane	3
Kent	3
Leah	3
Mark	4
Nina	4
Otto	4
Otto	5
Quin	4
Rene	5
Seth	5
Tina	5
Ulis	6
Vera	6

<tbody>
</tbody>

Sort the names in alphabetical order.

In B2, enter 1

In B3 and copy down,

=IF(A3=A2, B2+1, MATCH(TRUE, FREQUENCY(B$2:B2, ROW(A$1:A1)) < 4, 0))

The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

 

[b_fruge]

This works great, and I can definitely use it - I wanted to ask before finalizing if there was a way to randomize team assignments. This seems to place them on teams alphabetically by sorting - Once again, not a big deal, just wanted to see if that was an option here.

 

[shg]

Name	Team
Alan	1
Dana	1
Otto	1
Ivan	1
Jane	2
Quin	2
Cain	2
Gary	2
Nina	3
Fran	3
Ulis	3
Tina	3
Seth	4
Eric	4
Rene	4
Otto	4
Gary	5
Alan	5
Alan	6
Mark	5
Leah	5
Vera	6

<colgroup><col style="width: 37pt;" span="2" width="49">
<tbody>


</tbody>

=MAX(MATCH(TRUE, FREQUENCY(B$2:B2, ROW(A$1:A1)) < 4, 0), IFERROR(LOOKUP(2, 1/(A$1:A2=A3), B$1:B2) + 1, 0))

 

[b_fruge]

Thank you for your help - I really appreciate it - That works better without the sort, although it still assigns group numbers based on which order the names are in. However, I can and will use it to meet our needs. Once again, thanks!

 

[shg]

You're welcome.

although it still assigns group numbers based on which order the names are in.

Then randomize the order of the names ...