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?
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.)
An application that will help determine where people will sit at an event.
WHAT DO WE KNOW?
6 Seats per Table
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.
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.
Notice that number 2 has been assigned to Table A (cell M3) and cell C3 is empty because Mitsuru has a seat assignment.
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:
Cannot Sit At The Same Table
24, 11, 14
Must Sit At The Same Table
1, 11, 5
17, 10, 18
24, 16, 4
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:
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.
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.
The following image shows the guests who still remain unassigned to a table.
Next, the image shows that all guests are filled in and all rules have been satisfied.
With all of the guests filled in,
Using Doughnut Charts in Excel, we can even have a visual of the seating arrangement
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:
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.