Unsolved problem - Simulation/VBA required?

phill at GE

New Member
Joined
Nov 25, 2005
Messages
4
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
ABCDEF
1PhillNicolaMarcoAnniePavelCHOICES
2LondonParisLondonDublinParis1
3BoZDublinParisStavangerLondon2
4ShannonStavangerBoZLondonBoz3
5PhillNicolaMarcoAnniePavelPLACEMENT
6BoZDublinLondonStavangerParis
7
8Averageplacement1.6
Sheet1


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

Cheers - Phill at GE
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,689
And what if there is a situation where there is no solution? For example 4 people pick the same 3 locations?
 

phill at GE

New Member
Joined
Nov 25, 2005
Messages
4
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,689
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,689
Which would be the right choice

Looking at your examle, it has 2 solutions, here is the second:
placement.xls
ABCDEF
1PhillNicolaMarcoAnniePavelCHOICES
2LondonParisLondonDublinParis1
3BoZDublinParisStavangerLondon2
4ShannonStavangerBoZLondonBoz3
5
6PhillNicolaMarcoAnniePavelPLACEMENT
7BoZStavangerLondonDublinParis
823111choice
9Average placement1.6
Sheet1


So, which would be the correct one?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,548
Messages
5,838,038
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top