Really interesting question!
My first thought was to use the Solver, but coming up with an appropriate equation and constraints proved to be tougher than I originally thought.
My next thought was to try all combinations and look for the best result. This actually worked pretty well. If you have your table laid out as you showed it, with the rating for Student1, topic1 in B2 (2), then this program should work.
Press Alt-F11 to enter the VBA editor. In the navigation pane on the left, right-click on Microsoft Excel Objects, select Insert, Module. On the sheet that opens up, paste this:
Code:
Public score As Double
Public groups As String
Public Const ShortWay = True
Sub temp1()
Dim prefs(14, 7)
t = Timer
For r = 1 To 14
For c = 1 To 7
prefs(r, c) = Cells(r + 1, c + 1)
Next c
Next r
score = 9999999
Call CalcIt("ABCDEFGHIJKLMN", "", "1234567", prefs, 0)
Debug.Print score, groups, Timer - t
End Sub
Sub CalcIt(whosleft, assigned, topicsleft, ByRef prefs, curscore)
On Error GoTo oops:
DoEvents
If Len(whosleft) = 0 Then
If curscore < score Then
score = curscore
groups = assigned
End If
Exit Sub
End If
stud1l = Left(whosleft, 1)
stud1 = Asc(stud1l) - 64
For i = 2 To Len(whosleft)
stud2l = Mid(whosleft, i, 1)
stud2 = Asc(stud2l) - 64
score1 = 15
score1x = 0
If ShortWay Then
For j = 1 To Len(topicsleft)
topic = Mid(topicsleft, j, 1)
scorew = prefs(stud1, topic) + prefs(stud2, topic)
If scorew < score1 Then
score1 = scorew
score1x = topic
End If
Next j
score2 = curscore + score1
topic = score1x
l2 = Replace(Mid(whosleft, 2), stud2l, "")
t2 = Replace(topicsleft, topic, "")
a2 = assigned & stud1l & stud2l & topic & "."
Call CalcIt(l2, a2, t2, prefs, score2)
Else
For j = 1 To Len(topicsleft)
topic = Mid(topicsleft, j, 1)
score2 = curscore + prefs(stud1, topic) + prefs(stud2, topic)
l2 = Replace(Mid(whosleft, 2), stud2l, "")
t2 = Replace(topicsleft, topic, "")
a2 = assigned & stud1l & stud2l & topic & "."
Call CalcIt(l2, a2, t2, prefs, score2)
Next j
End If
Next i
Exit Sub
oops:
Debug.Print "Someone goofed on this program!"
b = 1 / 0
End Sub
(For coding purists out there, I apologize! I'm in a hurry and don't have time to clean it up.)
Put the cursor in the temp1 sub, and press F5 to run it. It will take about 10 seconds and in the Immediate window will return something like:
17 AN3.BE1.CM7.DJ2.FH4.GI5.KL6. 6.738281
Student1=A, Student2=B, etc. The 17 is the overall score, the lower the better, 14 is perfect with everyone getting top choice. The string of letters next to it is the pairs. In this case Student A is paired with student N with topic 3, and so on. The last number is just the time it took to run.
(If you don't have an Immediate window, click on the View menu item and select it.)
What this does is take every possible pair of students, then pick the topic with the lowest combined score for those 2 students. Then pick another possible pair and repeat until everyone is chosen. Take the overall score and if it's better than the current best, save it.
If you don't get a really good score, you can change the line:
Public Const ShortWay = True
to
Public Const ShortWay = False
This will take MUCH longer (I don't know how long, I never let it finish), but will get the best possible score.
Good luck!
