Unsolved problem - Simulation/VBA required?

[phill at GE]

Hi,

I need to put ~20 people in ~20 jobs in different locations. Each person gets to choose a favourite 3 locations (ranked). The aim to to place each person in one of their top 3 choices, and to minimise the average pick over the group.

Clearly this can be done by hand but the chances of selecting the best combination gets very slim with more people. Here is an example of what I am trying to do. (apologies for old HTML add-in but new one doesn't seem to be working for me)

rotation picker for mr.excel.xls
	A	B	C	D	E	F
1	Phill	Nicola	Marco	Annie	Pavel	CHOICES
2	London	Paris	London	Dublin	Paris	1
3	BoZ	Dublin	Paris	Stavanger	London	2
4	Shannon	Stavanger	BoZ	London	Boz	3
5	Phill	Nicola	Marco	Annie	Pavel	PLACEMENT
6	BoZ	Dublin	London	Stavanger	Paris
7
8	Averageplacement		1.6
Sheet1

Any solutions to this unresolved previous posting would be extremely welcomed!!!

Cheers - Phill at GE

 

[GlennUK]

And what if there is a situation where there is no solution? For example 4 people pick the same 3 locations?

 

[phill at GE]

Someone would be asked to revise their choices or a 'forced' placement might ensue.

To get round this it is usual that we ask for a 4th/5th favourite. The chances of 6+ ppl choosing exact same 5 placements are slim (especially given the nature of picking, but I won't go into detail)

Any ideas on the solution to this prob? I can't believe the collective genius on this board cannot slay this beast!!!

Best regards,
Phill

 

[GlennUK]

On other words, in the solution you are asking for, you'd want an indicator that all was not right when more than 3 people pick the same 3 places? ( so that you can then go and ask them to revise their choices, or ask for a 4th/5th favourite )

So are you going to want a method that will work with 4 and 5 choices too then?

 

[GlennUK]

Which would be the right choice

Looking at your examle, it has 2 solutions, here is the second:

placement.xls
	A	B	C	D	E	F
1	Phill	Nicola	Marco	Annie	Pavel	CHOICES
2	London	Paris	London	Dublin	Paris	1
3	BoZ	Dublin	Paris	Stavanger	London	2
4	Shannon	Stavanger	BoZ	London	Boz	3
5
6	Phill	Nicola	Marco	Annie	Pavel	PLACEMENT
7	BoZ	Stavanger	London	Dublin	Paris
8	2	3	1	1	1	choice
9	Average placement		1.6
Sheet1

So, which would be the correct one?