Months ago, my friend Michael asked, “is Excel for the common man?” That question has stirred my attention and imagination like I’ve just turned 21 years old again. Excel can be used for so many things and it’s been fun to come up with uses for Excel that are outside of business and finance applications.

Here’s a question I’ve come up with as a “commoners” use for Excel:

How Can Excel Help Build A Seating Plan?waiting_chairs_by_kangoedin

Rather than take you deep into the guts of this thing, this blogpost will be an overview of the thought process. So let’s start thinking!

(You can download the seating chart spreadsheet at the link near the end of this blogpost.)

GOAL

An application that will help determine where people will sit at an event.

WHAT DO WE KNOW?

36 Guests
6 Tables
6 Seats per Table

guests2

CHALLENGES

Excel’s cells make it easy to plop 36 names into cells … and then reality strikes. This is where we start thinking.

  • Some people will insist on sitting together at the same table.
  • Some people must not sit at the same table.

QUESTION: Can we develop rules in Excel to get everyone seated appropriately? This next image shows where we’re going to place the seating assignments. The tables are represented by columns A thru F.

empty chart

Before We Start: Layout & Strategy

I’ve placed the guest list in Excel column A. Also, rather than deal with full names and the space the text would take up, everyone is assigned an ID, numbers 1 thru 36.

Also. One thing we have to address is: what if  person isn’t assigned a seat?

That’s the role of the Assigned column. If a person isn’t assigned a seat, there is a formula designed to say, “check” otherwise, the cell will be empty.

=IF(COUNTIF($M$3:$R$8,[@ID])=1,””,”CHECK”)

Notice that number 2 has been assigned to Table A (cell M3) and cell C3 is empty because Mitsuru has a seat assignment.

mitsuru

People Can Be Crazy Sometimes

We have the list of 36 guests and we just can’t have a good time because:

  • Philip (7) and Wardell (6) must not sit together
  • Dave (24), Gail (16) and Terry (4) insist on sitting together

Here’s the complete list of rules that we need Excel’s help with:sunday_dinner_by_shanks_kun-d3bxdn6

Cannot Sit At The Same Table
7, 6
24, 11, 14
Must Sit At The Same Table
1, 11, 5
17, 10, 18
1, 8
24, 16, 4
28, 10
31, 15
2, 12

Let’s first look at the Must Not Sit Together

After the guests have been assigned to a table, we need to know if a table has more than one of the people in the rule; e.g., no table should have more than 1 of guests 24, 11 or 14.

Here’s the formula that’s used:

{=SUM(COUNTIF($F12:$H12,M$3:M$8))}

wrong people sitting together

Sitting both 24 and 11 at Table A, shows that there are 2 people at Table A who are in the rule against 24, 11 and 14 being together. Also note the skull and crossbones where there should be a checkmark.

The next image shows all of the “MUST NOT SIT TOGETHER” with assigned seats and there are all checkmarks, no skull-&-crossbones.

not sitting together all fixed

The MUST SIT TOGETHER guests have to be treated differently. If there are 3 people who want to sit together, we need Excel to tell us that all 3 are at the table.

must sit together

all done

The following image shows the guests who still remain unassigned to a table.

remaining guests

Next, the image shows that all guests are filled in and all rules have been satisfied.

final seating chart

With all of the guests filled in,

NAMES

Using Doughnut Charts in Excel, we can even have a visual of the seating arrangement

seating pic

DOWNLOAD THE
SEATING CHART
SPREADSHEET

COMMENTARY

Thinking about the challenge this way is an example of finding a “good enough” solution. This solution is partly manual. One solution would be to shuffle names around in the spreadsheet while eyeballing until everything looks good. The problems there would be the risks of not catching rule violations, assigning the same person to multiple tables, and assigning some people to no table.

At the other extreme would be programming Excel to find a solution automatically. With 36 people, the number of seating possibilities equals:

371,993,326,789,901,000,000,000,000,000,000,000,000,000

I didn’t pursue this partly because I wasn’t interested in a fully automated solution. And I’m not sure how difficult (or easy) it would be to program a solution. Beyond that, I like having the control to move names around manually, and have Excel give me alerts. Therefore, the solution feels more robust, and takes advantage of a lot of what Excel has to offer.

Chairs image courtesy of kangoe din
Sunday Dinner image courtesy of Shanks-kun