Counting the number of permutations

[BH]

Hi
Here is a poser!! Is there an easy way using an automated spreadsheet to determine how many times a group of numbers appear with each other?

I am organising a golf trip for 12 people and we are playing 5 rounds of golf. We will be playing in groups of 4.

I am trying to get the ideal scenario where everyone has at least one game with everyone else and trying to make it that people play with each other the least possible amount.

Doing it long hand I have come up with these results but it is taking eternity to keep reworking the matrix! as you can see, the most times two people play together is twice (which is good) but there are players still not playing together.

Teams over the five rounds
1 1 1 1 1
2 7 7 4 2
3 10 9 8 5
4 12 11 10 11

5 2 2 2 4
6 6 4 5 6
7 8 8 7 7
8 11 12 9 12

9 3 3 3 3
10 4 5 6 8
11 5 6 11 9
12 9 10 12 10


Matrix (sorry if the matrix does not appear on screen correctly but you get the idea)

1 2 3 4 5 6 7 8 9 10 11 12
1 2 1 2 1 0 2 1 1 2 2 1
2 1 2 2 1 1 2 1 0 2 1
3 2 2 2 0 1 2 2 1 1
4 1 1 1 2 1 1 0 2
5 2 2 1 2 1 1 0
6 2 2 0 1 2 2
7 1 2 1 1 2
8 2 2 1 1
9 2 2 1
10 1 2
11 2
12

Can anyone come up with an automated solution?? PLEASE

Regards

BH

 

[fneily]

Here's a couple of references you may want to look at. It includes some VBA code.

http://www.mrexcel.com/forum/showthread.php?t=38721

http://www.mrexcel.com/forum/showthread.php?p=1182768

 

[BH]

Thanks fneily

The code is very good at giving me all the combinations but I still need to pick the best permutations so that we all play a game with each other. Still having to go through the list long hand. Could really do with a counter that counts how many times two of the number come up together!

Thanks for your help

BH