Allocate choices to respondents

JLB123

New Member
Joined
Nov 10, 2014
Messages
18
Hi,

I've got a dataset of respondents in the format of:

Respondent 1 - Choice 1 - Choice 2 - Choice 3 etc.
Respondent 2 - Choice 1 - Choice 2 - Choice 3 etc.
[for 100s of respondents]

I am trying to allocate choices so that each respondent gets as many of their choices as possible

The issue is I have several constraints I need to address:
  • Choices are events to attend. Some events will run concurrently so some choice sets are incompatible
    • However, I can decide on schedule, so can try and schedule to minimize the # of sets that are incompatible
  • There is a limit to how many people can attend one event at once
  • Some events can run twice. Where this is the case, to the extent possible I want broadly similar numbers at each run of the event

Has anyone tackled a problem like this before and has any advice where to start? Best I can think of is picking a schedule, then allocating respondents (e.g. in time-slot 1 give them one of their events they chose, then in time-slot 2 give them another one of their events they chose). However, this has a couple of issues: (1) does not optimize schedule, (2) depending on the order I write events within a time-slot the ones towards the end have virtually no attendees (since I'm doing a "if respondent has chosen first event listed in time-slot and it does not breach constraints, allocate here, if not move on to second event listed in time-slot and check if they chose this..."

Thanks!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hello. You could address the issue of balanced number of attendees by counting how many want an event and divide that count by how many of that event will run, then use that result as your ceiling for allocation to the event. You could also use that count to see if you need more runs of the event. (e.g. if the event ceiling is 10 and you have 23 people wanting it, you will need 3 runs of the event, each with a revised ceiling of 8. I will think some more about the wider issue and hopefully post something more constructive tomorrow.

John
 

JLB123

New Member
Joined
Nov 10, 2014
Messages
18
Thanks John - think I may have (semi) solved - ended up just brute forcing it using solver (essentially using one cell to define every possible combination of events and another cell per respondent to define every possible combination of their choices, success defined as % of choices met). Ridiculously slow and limited to 199 respondents (unless I define some respondents as being linked to other respondents) so would be interested if there's a more elegant way!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

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