Randomly allocate players into Teams

I am trying to create a worksheet in Excel 2010 where people are to be put into teams - could be 3 to a team one week and then 2 the following week(maximum number of players to a team will never exceed 4ppl). Team sizes will be wholly dependant on the number of people (some weeks there may be an uneven number of players ie 21 players but it is a 2 player per team round), but it is rare or at least a very long night if we have more than 18 teams. The number of teams and players to a team will also vary on a weekly basis and to add to my joy there are rules in which I need to ensure that no two team members have played together in the previous 6 months and that only one female to a team.
I have created a worksheet (Membership), that will have the members details listed so that I can identify males/females (Column E). On another worksheet (MainPage) which is to be the front page in which the people’s names will be entered in manually(cells C8:C31, H8:H31) and on the same worksheet the number of players to a team will be entered (cell O3).
What I was hoping to do was somehow lookup the list of names and based on the number entered in Cell O3 (No of players to team) of the Mainpage worksheet, allocate players to teams which are then listed on Mainpage worksheet in cells M7 through to P24
M7:P7= Team 1 Player 1:Player 4,
M8:P8= Team 2 Player 1:Player 4 etc,
And then the data also copied across to another worksheet (TeamsHistory) which will record the date and team members for reference to ensure I can meet the rule of members not playing together in previous 6 months.
I know this is a big ask and I thank you all for the help in advance but silly me offered to assist with my husband’s darts club with entering the electronic age before I realized the logistics of what I was in for. (I know some of you may have been wondering what kind of teams I was organizing) most of the stuff that I need is pretty standard but as for writing the code for this to happen is way beyond my intelligence.


Again thank you for any help you are able to give me

Eages

Thank you Millz for getting back to me and yes I fully understand why you need the workbook - silly me didn't think at the time.

Please see attached

thank you for taking the time to help me, It is greatly appreciated

Eages

suppose 16 players and teams of 4 and a period of 6 months in which 2 players can not play together and you play every week,
i think that's impossible. After max 2 months you're blocked.

bsalv

We have in total about 60 members however not everyone attends on a weekly basis and most weeks it is two players to a team.

I guess however if it came to the point that we had all played in teams together then I would need something to be able to override that rule.

Wow this is becoming a bigger problem than I had thought >> Mental note. never put my hand up for anything again.

Thanks for bringing that it light for me bsalv

Eages

i promiss you nothing yet, it's for next weekend.

a little bit late, but here is my schedule.
- In "Membership" i need 2 columns "Nickname" and "***". If later you have an unknown person in "Main Page" I assume it's a male.
- In Main page you enter all your nicknames in C8:C55, in C3 and O3 your date and number of players to a team. When you change 1 of both, cells S3:S4 are cleared (see later).
- Now push the button "Allocate teams". The programme makes 500 trials to make a schedule and chooses the best one. The teams appear in M7:P24 and the 10 worst couples appear in S3. There you see 10 numbers with "|" as separator. If the first number is for example 5.000, that means that the worst couple of people played together 5 days ago. The next number is for the second worst couple etc. Do you understand this ?
- you can push that button a second time, perhaps the result will be better than the first time and then it overwrites your teams and S3, otherwise you see the result in S4. You can push that button several times.

Then I don't know what you want to do with this names, but if you run the macro "played", the program copies the couples to a table in "team history". That table is the source for the pivottable in that same worksheet. There you see how many times a couple played together and the last date.

For testing i made a macro "test", where in a loop you play with those names from the 1st january untill the last of january.
Try that one and if something isn't clear ask it me.