Mr. Excel Seating Chart Challenge: 100 people 10 tables 11 Sessions * Can everyone meet everyone?

Mr. Excel, Bill Jelen, contacted me with a question that he'd been asked:
Can you arrange seating such that every person in attendance will sit at a table with every other person, at least once? The parameters:

- 100 people
- 10 tables
- 10 seats t each table
- 11 rounds

I found this fascinating because it presents a real world scenarios where the ultimate goal can't be achieved. We have to go back to the person who made the request and tell the truth. Then we have to ask if there's any flexibility. Can we get more tables, bigger tables or add more rounds to the 11?

I came up with a solution that requires 19 rounds.

After thinking about my days as a wrestler, and round-robin tournaments, I could see adding people to 5-person teams, and then create an agenda that gets each of the 20 teams to meet, rather than try to work with 100 people.

In a real scenario, my 19 rounds would be a suggestion. The boss/client/friend/co-worker who made the request would have to decide if it's an acceptable solution, or not.

For a list of my Excel courses at Lynda/LinkedIn:


There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.


My book: Guerrilla Data Analysis 2nd Edition

[ComicCollectionComplete]

Oz, your solution impresses me so much. There’s always an emphasis on learning technology, but project analysis is often overlooked. I really loved the way you approached this project and gave us examples of how to interact with clients and thinking outside of the box.

[MrXL]

Discovering the Round-Robin wrestling website was slick.
Breaking this into a problem with 20 teams of five makes it much easier to think about!
Practically... I wonder if the five people on the team would become clique-ish after a few rounds.
Imagine if Five Excel MVPs walked into a bar and sat down with Five Power Point MVPs. Would we have one discussion about our shared interests or two mini-discussions?
Great video!

[GrainneDuggan_Excel]

Always know your context, ask questions, consider the consequences of not achieving the goal, how can we adjust the goal to satisfy our needs - then decide the approach. Real life is messy

[anupk]

Superb! And what a sensational background music! Tons of salute!

[wayneedmondson]

Hi Oz. Great stuff! My solution.. free drinks at the bar.. let nature takes its course.. haha!! Thanks always for your great insights :)) Thumbs up!!

[wesszep]

Hi Oz, hope you are well, want to wish you a happy new year, and may this year brings you a lot of problem to solve.😁And wish you and your family be blessed, nice and peace year.

[spilledgraphics]

Oz, how did I overlook this video: 😬🤯

With respect minute 2:30 ... From season 1, episode 3 of the television series “Star Trek: Discovery: “Universal law is for lackeys; context is for kings.” 😁 Impressive layout on your solution without using VBA... 😵

Last but not least, loving the matrix-like backgrounds 😁

[GeertDelmulle]

Oz, Consider it an optimisation problem: How high can you get the
I’m talking the original 10x10/11 problem here. 65%? A little higher? Some claim: 72%…
With a lot of manual labour, I got it up to a little over 80%, semi-automatic, I got it up to some 78%.
What about you? (It wasn’t clear to me from your video)

[barttitulaerexcelbart]

Nice video Oz! you must have spend quite some time to research this. Is the name for this probem only "round-robin-scheduling" ? I searched for this on google scolar to see if there were some academic journals, like maybe from operations management. looks like this scheduling is used in computer science....

[gaslitworldf.melissab]

Tell me, who, doesn't think a great mind is also really sexy? Whew!

[jessmore]

Hello sir.. I have a ? ☺️ if I have 14 columns of data w/ various staff in each & I would like to merge 3 staff name columns with 3 outcome (that’s the name of the columns) what is the easiest way to do this? I need to pivot this data. TIA! If you have a video on this please let me know, I’ve looked and cannot find it.

[mohitchaturvedi]

One more demand to pwr query - My office requirement is query should run for each files placed in a folder and append query result one after one. is there any way of doing it?

[excel-in-g]

So 1 person needs to meet 99 other persons. That person can meet 9 others each round. There are 11 rounds. That's 99 different persons that single person can meet that way. Sounds like that person asking Bill may have a solution.
At this for almost an hour and getting nowhere. Correction: getting into trouble and fast I may add, and into deeper "s" at each take I go. Owe, so stinky...