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]

[TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl33, width: 49, bgcolor: #F3F3F3"]Name
[/TD]
[TD="class: xl33, width: 49, bgcolor: #F3F3F3"]Team
[/TD]
[/TR]
[TR]
[TD="class: xl35, bgcolor: yellow"]Alan
[/TD]
[TD="class: xl34, bgcolor: white, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl35, bgcolor: yellow"]Alan
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Cain
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Dana
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Eric
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Fran
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl35, bgcolor: yellow"]Gary
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl35, bgcolor: yellow"]Gary
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Ivan
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Jane
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Kent
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Leah
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Mark
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Nina
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl35, bgcolor: yellow"]Otto
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl35, bgcolor: yellow"]Otto
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]5
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Quin
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Rene
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]5
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Seth
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]5
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Tina
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]5
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Ulis
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]6
[/TD]
[/TR]
[TR]
[TD="class: xl37, bgcolor: white"]Vera
[/TD]
[TD="class: xl36, bgcolor: #E5E5E5, align: right"]6
[/TD]
[/TR]
</tbody>[/TABLE]
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]

[TABLE="width: 74"]


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

[TD="class: xl33, width: 49, bgcolor: #F3F3F3"]Name[/TD]

[TD="class: xl33, width: 49, bgcolor: #F3F3F3"]Team[/TD]

[/TR]

[TR]

[TD="class: xl35, bgcolor: yellow"]Alan[/TD]

[TD="class: xl34, bgcolor: white, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Dana[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl35, bgcolor: yellow"]Otto[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Ivan[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]1[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Jane[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]2[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Quin[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]2[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Cain[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]2[/TD]

[/TR]

[TR]

[TD="class: xl35, bgcolor: yellow"]Gary[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]2[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Nina[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]3[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Fran[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]3[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Ulis[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]3[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Tina[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]3[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Seth[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]4[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Eric[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]4[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Rene[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]4[/TD]

[/TR]

[TR]

[TD="class: xl35, bgcolor: yellow"]Otto[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]4[/TD]

[/TR]

[TR]

[TD="class: xl35, bgcolor: yellow"]Gary[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]5[/TD]

[/TR]

[TR]

[TD="class: xl35, bgcolor: yellow"]Alan[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]5[/TD]

[/TR]

[TR]

[TD="class: xl35, bgcolor: yellow"]Alan[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]6[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Mark[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]5[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Leah[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]5[/TD]

[/TR]

[TR]

[TD="class: xl37, bgcolor: white"]Vera[/TD]

[TD="class: xl36, bgcolor: #E5E5E5, align: right"]6[/TD]

[/TR]


</tbody>[/TABLE]

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