Well, The situation presented in a file is only similar to one in description.
There are 29 students
There are unique 26 songs.
Note that in some cells you have either space after song title or a typo. With these, there would be 29 unique titles.
But let's focus on 29/26 situation. 3 songs shall be assigned to 2 students, the remaining 23 one song to one student each.
As students made first-second-third... choice it would be nice to assign then their 1st choice if possible, if not 2nd choice, and so on.
In sample file "The bigger picture", even if present in preferences of Laurens Bintein and Rune Noë was not assigned to anyone.
As for the solution - I don't have (at the moment) wholy automatic one..
I tried solver for whole set, but having few songs which are present only in one or two students choices, and not having all choices of students filled in (one student gave only one answer) didn't led me to acceptable results.
So this semi-automatic is described below.
First I'd locate the songs which are on the list of only one person, these are: All together now, Green Day, Land of the Free, Rage Against, Rock the casbah, The man.
They are conditionally formatted with red font, to be better visible
See the list of unique titles in column P and total count of a song in Q (sorted on Q).
Iv'e sorted list of students and preferences (columns B:H) based on lowest popularity of their selected song (see column I):
and obviously students who selected the unique song will be assigned this one, like Julie Ampe and Marie Barbier theit respectively 4th and 1st choice (The nan and Green Day), and so on.
Then for songs selected by two persons: Alright, Help is coming, The bigger picture, What's going on
They are conditionally formatted with green font, to be better visible.
Some choices are pretty obvious also here.
Fee Meersschaert has the first choice of Help is coming (it's also 3rd choice of Michelle Hillebrant, so worse one).
Rune Noë 3rd choice (the same as Fee's) is Alright - obviously assigned.
Laurens Bintein 3rd choice (and 5th of Rune's) is The bigger picture - assigned.
and finally Ella Ghesquière 3rd choice (it's 5th of Margaux) is What's going on - assigned.
all these preassigned songs are marked with green background.
The rest of students - they selected more frequent choices - i tried to use solver for assignment.
I started with assigning all of them their first choice as a solver variable, made constraints that the choice cannot be larger than the total number of their answers - column H:
and cannot be lower than 1. The next constraint is that choice has to be an integer number, and count of any of assigned songs cannot be larger than 2 (column R):
By the way - in column L is a title of a selected song:
And as for goal for solver - I wanted to have as many first choices as possible - if not second choices, and so on. but as some songs are very frequent I decided to weigh this position with the frequency, to promote their selection.
so the weighted choice is (column N):
and for the goal I used also requirement of possibly small variation. It showed to be mych beter solution than forcing every song being assigned at least once in constraints. But as variation would be a small number, I emphasized it's meaning by multiplying by large factor (1000).
and the goal cell N33 is:
Well, after running Solver with evolutionary engine we have our results :-)
And final note - the results could and will be different every (almost) time you run solver. This is quite typical for evolutionary engine. But their quality is similar. results of some runs are presented in columns U:Y
Bookmarks