Musical Chairs using Excel.?

[Victor Delta]

I wonder if anyone can help me find a solution to this problem please?


I run training sessions for a growing group of about 50 volunteers
approximately every 3 months. In order to help the learning process, I
allocate them to tables of 6 or 7 with the aim of enabling them, over a
period of time, to meet and work with as many of their colleagues as
possible. For obvious reasons, I also try to ensure that each table has the
widest possible mix of experience.


To manage this, I have a spreadsheet which records in Column A the names of
the volunteers (most experienced at the top, newest at the bottom). The
subsequent columns record, from left to right, the table numbers that they
sat on at each meeting. A blank indicates when an individual was unable to
attend a session.


My question is about to how best to allocate table numbers at future
sessions. I have tried allocating places randomly but, interestingly, that
does not always give the widest mix of experience on each table (you can, by
chance, end up with virtually all the 'new' members on one table).
Furthermore, I would like to ensure that no one sits on the same table as
someone they worked with at the last session (or, preferably, the last 2 or
3 sessions).


Has anyone any ideas please as to how I can solve this problem using Excel?


TIA

V

 

[Shane Devenshire]

Hi,

Probably the correct way to do this is to use the Solver feature

The file name is SOLVSAMP.XLS.

It is usually found in C:\Program Files\Microsoft Office 12\Samples

It includes sample covering the following topic areas:

Product Mix
Shipping Routes
Staff Scheduling
Maximizing Income
Portfolio of Securities
Engineering Design (Electric circuits)

the one that is probably applicable to you is Staff Scheduling

 

[Victor Delta]

Hi,

Probably the correct way to do this is to use the Solver feature

The file name is SOLVSAMP.XLS.

It is usually found in C:\Program Files\Microsoft Office 12\Samples

It includes sample covering the following topic areas:

Product Mix
Shipping Routes
Staff Scheduling
Maximizing Income
Portfolio of Securities
Engineering Design (Electric circuits)

the one that is probably applicable to you is Staff Scheduling

Shane

Thanks for that, I've installed the Solver add-in. However, can you please
suggest how I should actually use it to solve this problem? Presumably I
shall need a way of allocating tables and then a formula that counts how
many people are sitting at the same table as someone else they sat with last
time. And then the system needs to iterate until the total is zero (or
minimised).

V

 

[Victor Delta]

Shane

Thanks for that, I've installed the Solver add-in. However, can you please
suggest how I should actually use it to solve this problem? Presumably I
shall need a way of allocating tables and then a formula that counts how
many people are sitting at the same table as someone else they sat with
last time. And then the system needs to iterate until the total is zero
(or minimised).

V

I've got my head round this problem a bit more now, thanks to some help and
suggestions. What it comes down to is that I need a way of identifying if
two people sat on the same table at consecutive meetings. See the following
table:

Col A Col B Col C Col D
Name Feb April
Ann 2 3
Bill 1 6
Carol (Blank) 4 (Blank indicates Carol was not
present at February meeting)
Dick 2 3

What I need in Col D is a formula that indicates 'Y' in rows 3 and 6 to show
that Ann and Dick sat on the same tables in both February and April.

Can anyone suggest a way of doing this please? Array formula?

Thanks so much,

V