Creating Balanced Teams (Golf)

markko

New Member
Joined
Sep 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Sorry is this has been covered. I tried searching and found things were were, as they say, close, but no cigar.

In this case it's golf but I think the function can be more generally used, so I'll be more generic. And I'll use simple numbers as an example.

I have twelve players with various levels of skill. I need to divide them into 4 teams.

The easy part: They have been ranked from 1 to 12 based on their skill level. They have been assigned into 4 "Groupings" s using the CEILING function, so that the four highest ranked players are grouped together, the next four highest ranked together, etc.

What I would like to do is automatically create teams in which there is one player from each Grouping. IOW, each team would have a Grouping 1 player, a Grouping 2 player, a Grouping 3 player and a Grouping 4 player. It's kind of a "Choose one from Column A, one from Column B" kind of problem.

Any suggestions?

Thank you.
1630855945966.png
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Upvote 0
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCDEFGH
1
2Player 111Team 1Player 3Player 8Player 12
3Player 221Team 2Player 2Player 6Player 11
4Player 331Team 3Player 1Player 5Player 9
5Player 441Team 4Player 4Player 7Player 10
6Player 552
7Player 662
8Player 772
9Player 882
10Player 993
11Player 10103
12Player 11113
13Player 12123
14
154
16
Master
Cell Formulas
RangeFormula
F2:F5F2=SORTBY(FILTER($A$2:$A$13,$C$2:$C$13=1),RANDARRAY($B$15))
G2:G5G2=SORTBY(FILTER($A$2:$A$13,$C$2:$C$13=2),RANDARRAY($B$15))
H2:H5H2=SORTBY(FILTER($A$2:$A$13,$C$2:$C$13=3),RANDARRAY($B$15))
C2:C13C2=CEILING.MATH(B2/$B$15,1)
Dynamic array formulas.
 
Upvote 0
Solution
A more dynamic option
+Fluff 1.xlsm
ABCDEFGHI
1PlayerRankGroup
2Player 111Team 1Player 2Player 5Player 9
3Player 221Team 2Player 4Player 8Player 12
4Player 331Team 3Player 3Player 7Player 11
5Player 441Team 4Player 1Player 6Player 10
6Player 552
7Player 662
8Player 772
9Player 882
10Player 993
11Player 10103
12Player 11113
13Player 12123
14
15Teams4
16Players12
17
Master
Cell Formulas
RangeFormula
E2:E5E2="Team "&SEQUENCE(B15)
F2:H5F2=INDEX(SORTBY(A2:A13,C2:C13+RANDARRAY(B16)),MOD(SEQUENCE(B15,,0),B15)+SEQUENCE(,B16/B15,,B15))
C2:C13C2=CEILING.MATH(B2/$B$15,1)
Dynamic array formulas.
 
Upvote 0
Hi & welcome to MrExcel.
How about
They both work, Fluff. I even understand the formulas, although the first example much more than the second). I already had the ceiling formula to generate the initial Groupings. Since the number of players is variable and the number of players per team can vary, I will have to play around with both to see which works out over the course of a few weeks.

The only downside I see is that "RAND" recalculates every time the spreadsheet changes. I know the general workaround to copy and paste values once I like the array but is there a way to set up the sheet so that it only recalculates when I want it to?
 
Upvote 0
is there a way to set up the sheet so that it only recalculates when I want it to?
The only way to do that would be to turn calculation to manual. But that will affect all open workbooks & if you press F9 to recalculate then the teams will change.
 
Upvote 0
The only way to do that would be to turn calculation to manual. But that will affect all open workbooks & if you press F9 to recalculate then the teams will change.
Even that would work. Thank yo so much.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top