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.)
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
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.
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.
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
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))}
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
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.
Sunday Dinner image courtesy of Shanks-kun
Nice use of Doughnut Chart! How about adding an extra doughnut to add “chair”.
A reminder: The data label on your chart doesn’t not display properly in Excel 2010.
wmfexcel FRIKKEN BRILLIANT! I hadn’t thought about going further and adding chairs. Very cool addition.
Thanks for letting me know about the incompatibility of the data labels. You taught me something.
OzData My Pleasure! I am inspired by your doughnut. 🙂
btw, may I ask how do you embed your Sway onto your page?
What do the 0 and 1 cells or the 3 and 0 cells mean?
livefyreuser Ah! Ok.
In order to make the rules work, Excel counts the number of people in the rule and the number of those people at each table.
In the Must Not Sit Together rule, any number larger than 1 is not good.
If 8, 17 and 20 must not sit together, that’s 3 people in the rule. Excel goes through all the tables and tells us how many of those people are at each table. 0 and 1 are the only acceptable answers.
For the Must Sit Together, we want the numbers to match. If 30, 11, 6 and 15 must sit together, that’s 4 people. We need a table that shows 4. Unacceptable values are 0 – 3..
Thanks for the explanation.
Regards.
OzData Yes. I tried that but did not success.
After I pasted the code onto my page, only URL showed up.
I’ve checked with WordPress Support and found that they don’t support iframe… but I saw your Sway on your page. Therefore I am wondering if any modification to to code is required. Appreciate your tip. 🙂
such a wonderful post. I would like to know more about such topics and hope to get some more helpful information from your blog I really liked the way you highlighted some really important and significant points. Thanks so much, I appreciate your work. thanks for sharing this post
Tableau Guru
http://www.sqiar.com/services/tableau-software-consultants/
wmfexcel I do have a plugin called Advanced IFrame. I don’t know if that’s required or not. But I didn’t do any modifications to the Sway embed code. I just pasted the embed code into the text editor.
Try the Iframe plugin and see if that helps.
OzDataThanks for your suggestion. Cheers,
wmfexcel did you get it to work?
Let me know. I might be able to help. Embedding the Sway shouldn’t be so difficult.
OzData thanks so much for your help. I think I have figured it out… coz I am using wordpress.com, not wordpress.org. ~_~
wordpress.com don’t allow plugins
wmfexcel Ah! I had that suspicion.
Do you plan to get a self-hosted site? What’s your website?
OzData Thanks for your suggestion. Nonetheless, I know little about web posting. I think I will stick to WordPress.com for the moment, which is good enough for a weekend blogger like me. 🙂
Here’s my site:
http://wmfexcel.com/
Feel free to visit and leave your comments.
Cheers,
I’ve been searching for days for a program or method to assign names. How can your system be expanded to seat more that 30 guest?
Thanks
How many people would you want to accommodate? It would be pretty easy to extend the list and add more tables–if you wanted to get that detailed.
Hi Oz, i’m in the same situation, you’re spreadsheet is perfect but how do i add tables, my room has 200 people and 40 tables of 5.
That would call for extending the layout to include the extra people, tables and rules. Then open up the formulas to include the extra data.
If you’re interested in doing a screenshare tutoring session, we can arrange some time.
How can you block out certain seats from filling? I would like to block out sections for social distancing. I also need to look at it from a linear perspective rather than the doughnut view. What would you suggest to use instead of the doughnut? I am working on a seating chart for a church. I would also need to print out the seating assignment for people to see where they are assigned to sit. Is there a way to take this data and use it for the printable seat assignments?