optimize schedule by equally dividing people over two groups

[lukas14]

Hello,

I am trying to divide 11 people over two lines in 15 days (with group rotation possible every 3 days only). What I mean with 'equal' in this case is that every employee gets to work the same number of days (optimally) with any of the other employees. Below is an example of how this schedule looks like.

	A	B	C
37		Line	Line 2
38	Day 1	ABCEH	DFGIJK
39	Day 2	ABCEH	DFGIJK
40	Day 3	ABCEH	DFGIJK
41	Day 4	ACGHIK	BDEFJ
42	Day 5	ACGHIK	BDEFJ
43	Day 6	ACGHIK	BDEFJ
44	Day 7	ABEGIJ	DCFHK
45	Day 8	ABEGIJ	DCFHK
46	Day 9	ABEGIJ	DCFHK
47	Day 10	ACEIJK	BDFGH
48	Day 11	ACEIJK	BDFGH
49	Day 12	ACEIJK	BDFGH
50	Day 13	ADFHJ	BCEGI
51	Day 14	ADFHJ	BCEGI
52	Day 15	ADFHJ	BCEGI

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I thought by calculating the number of times that a specific combination of two employees would occur (by using COUNTIFS in a 11 by 11 matrix), I would be able to adjust the schedule manually by trial and error until the numbers in the matrix below would be as close together as I can get (only multiples of three possible). While trying to optimize the schedule I figured there might be a much easier way to optimize this schedule.

	J	K	L	M
37		A	B	C
38	A		6	9
39	B	6		6
40	C	9	6

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Help would be much appreciated

Excel 2010
Windows 7 enterprise