a list of 21 golfers not all playing each week , in teams of 4 or 3 , with no duplicates

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi mapia,

As you've found this isn't a trivial problem. Find the attached where I solved it for a club that had a similar rule. This club played a weekly event match play where no two players were supposed to play against each other again throughout the season. This meant we needed to keep track of every prior match that was played. That was the first problem. Spellings their names the same way all season was also a problem, but not part of the workbook as I recall.

The structure of this workbook is important for solving this problem. You are trying to do a sheet per week and I used a sheet called "AllResults". My AllResults sheet has the group number that two opponents played in (this two can be a 3 or 4 by clicking on the Resize Groups for threesomes or foursomes). So in Column A of the AllResults sheet is the number of the group people played together in. This structure allows me to help pick people who haven't played together (on the same team) before. This AllResults sheet structure which you can do is part of the secret for solving your problem.

The reason this problem is very hard, is there might not be an answer. How do you find a group of 4 where none of the players have never played together if there is no answer? My answer to this was to build random teams and see if players fit the criteria (not having played together before). I'd do this 1000 times and if no answer was found, I could try another 1000 or simply let the random pairing stand. We called this solution by exhaustion.

This is the first time I've had anyone who cared (I hope you are still reading) the trick in this answer....
1. You pick 2 players at random (you could use 3 or 4 as well).
2. You do an advanced filter of the all results using those player names. I do this on the WeekRound sheet in Column AA.
3. Along comes the group numbers they have played in. If any of those group numbers match, I randomly pick other players and try again.

The above 3 steps takes some head scratching to see why this works. I also use a Frequency function to see the same group number shows up.

After the event I click on the "Post Round To Results" button that adds this week's matches to the Results tab giving that group a unique number to show they have all played together.

I do all the above using VBA, and a 1000 tries takes a few seconds to perform.

Find attached the workbook that was last used to perform this magic. The players sheet has an Event macro behind it to select the players who are going to play. I use a double click in Column A to add an "x" is they are playing this week.

Hi mapia and semcode,

Explaining the Advanced Filter with Team numbers is a little hard. See the example where I've done it by hand on the new week sheet. See if it makes more sense when all the steps are shown on a single sheet.

Hi mapia,

I worked for about 3 days on this exact problem. That is, what if there is no way to combine people so no player had played with any of the others in the foursome. I believe it found a 4 person team who would fit the goal and then look for the next 4 some. If it tried 1000 tries it would stop and claim this was the best it could do. It would do its best to give the first few foursome no players who had previously played together but simply failed for the last few foursomes. I'd need to go back and review the code to see what is exactly is happening. You should try some small samples and see what the code does.

Hi djapigo,

This problem is a little different than normal permutation or combinations. Say you have 6 players and each needs to play once against each of the other players. This would be twosomes in golf with 6 total players in a Round Robin type of match. You would say there are 6*5 different ways to do this as suggested in your answer above. So let's try. Players are ABCDEF. We have:
AB, AC, AD, AE, AF, BC, BD, BE, BF, CD, CE, CF, DE, DF, EF. This looks like all of them as BA would repeat AB, etc. Now say we take these same 6 players and put them in a threesome. We want to match them so no two players will play in a group with a player that they have played in before. ABC, DEF, AD..... There is only 1 round of golf with 6 players that matches what we want. I hope you are starting to see the problem. We were all taught permutations and combinations but how do you do this problem?