Seating Plan

Hey,

I'm working on a seating plan for our christmas party this year.

Complete randomization of a list I can do, but I need conditions. We have 67 people, and 7 departments. I need seats 1-7 to be filled by 1 person from each department, then seats 8-14 to be the same and so on.

Ideally, adjacent seats would be boy-girl-boy-girl etc.

Each department has a different amount of people. There are 3 tables.

Sample attached. I don't expect to have it all done for me, so any pointers/guidance would be good enough. I've used INDEX with MATCH before but I can't quite work it of for this job.

EDIT: I've posted this elsewhere too: http://www.mrexcel.com/forum/excel-q...ml#post4678259

Thanks!

Welcome to the forum!

Unfortunately, your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Please do not ignore this request: providing those links is not optional. Thanks!

EDIT: link now added - thank you.

This is a very difficult task since there are so different amount of people in each department and not equal amount of ladies and gents.
Department no 7 is all women and rather large. Department no 4 is only 5 people.
The best I can think of is to make an acceptable Male/Female placement manually, number the male and female seats, and then use a randomgenerator to place the names.

I have to be completely honest here: it would be quicker and easier to do this task the old-fashioned way than try to find an automated solution.

Look at this link for a tutorial and template

http://blog.contextures.com/archives...ng-with-excel/

Thanks all. I think you're right that it's a step too far to fully automate it as required.

So to simplify it, I'm dropping the boy-girl-boy-girl requirement. Then, is it possible to combine randomness with some orderliness? By that I mean:

A.......B
-----------
1.......10
2.......6
3.......4
4.......14
5.......9
6.......7
7.......5

So column A is all the departments and Column B is the user count in each department. Can I do anything that will randomly populate rows in a specified column with a department number but only total the users count, and not put the same department number on following rows (except department 4 as they'll need to be together in 2 or 3's probably). To end up with 10X 1, 6X 2, 4X 3, 14X 4 etc.

I'm doing this at the moment, but there's no maximum for each number: =IF(ISBLANK($A12),"",RANDBETWEEN(1,7))

Thanks!

I'd approach this as a hill-climbing exercise with the objective of maximizing entropy.

Say you have three round tables of, say 20 people each. That's 20 adjacent pairs at each table, 60 adjacent pairs total. Each adjacent pair is defined to have entropy based on their gender and department, as follows:

Gender	Dept	Entropy
Same	Same	0
Same	Diff	1
Diff	Same	2
Diff	Diff	3

You could redefine that as you choose, depending on whether you regard gender or department as the more significant (or the same) in a pairing.

Start with a random assignment, and calculate the sum of the entropy for the entire seating.

Then, in a loop, try making every possible swap (that's 60 * 59 possibilities), and recalculate the entropy. If it increases, do the swap; if not, don't.

Repeat the loop until no swap increases the entropy.

It would require VBA, and I'm not volunteering, but hill-climbing is a well-established algorithm.

A variation of that algorithm that keeps you from getting stuck in a local maximum is called annealing. In that case, you sometimes (probabilistically) accept a swap that makes the entropy lower, with the probability going down as the algorithm proceeds.

Here's an idea I had but it's not complete:
Assume that we are just dealing with males.
Line up the seats in one long row. Take the department with most people in and distribute them evenly over the long row of seats. That will maximize the distance between the group that need it the most.
Next take the department with the second most people in. Distribute them evenly between only the seats that are for instance to the left of the first group.
Next take the department with the third most people in. Distribute them evenly to the left of the ones from the first department that still has no neighbor and then distribute the rest to the left of the second department.
And so on.

Like I mentioned, I have no idea how to extend this idea into using both sexes.
I don't have the energi or the brains to take this much further, I'm hoping for someone else to pick up the ball.