I have a brother-in-law that plays golf. He asked me if there was a way to calculate the teams one could make from a list of 20 players, to play in four person teams, in a golf tournament. It wasn't hard to calculate that the combination of 20 players taken 4 at a time results in 4875 different possible teams, but:
1. How can excel be made to generate the actual four person teams? (Given a list of 20 names, of course - I decided to use the NATO alphabet from Alpha to Tango, and then use substitution to insert the names of the actual players in due course.) A macro was once posted by Myrna Larson (Microsoft MVP) <http://www.ozgrid.com/forum/showthread.php?p=148992>, and I tried to paste it into a suitably modified spreadsheet, but it does not run for me: it throws up a Compile error, and I don't know enough about macros to try and fix the problem.
2. Of course, nobody has time to play the games required by 4875 teams, and my brother-in-law pointed out that for his purposes it would suffice for every player to play at least one game with every other player, even if their were duplicates among the other two players in any foursome. I would guess that this relaxation of the conditions would drastically reduce the number of teams required, but I cannot even figure out how to mathematically express the problem of calculating the set of foursomes that would allow, in the fewest number of games, each player to play at least one round with every other player; much less turn it into an Excel problem.

Any comments or suggestions will be gratefully received. Thanks very much. pxd