Randomized Seating Chart

A

abbyrussell

New Member
Joined
Jun 18, 2015
Messages
2
5 tables
4 people per table
20 total participants
5 days

How can I ensure that no two people sit with each other every day?

Thank you,

Abby Russell
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Welcome to Mr Excel!

Here's one way you could do a simple rotation so no one gets to sit with the same person twice.

Or another way of looking at it, everyone gets to meet 15 people over the five days, i.e. 5 days x 3 other people on the table.

Excel 2010
ABCDEF
Person #
Day 1
Day 2
Day 3
Day 4
Day 5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]Table 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]Table 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]Table 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]Table 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]Table 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Table 5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]

</tbody>
Sheet1​
 
Upvote 0
Thank you so much! Did you use a formula? If the size of the group were to change, how would I go about doing this?
 
Upvote 0
You're welcome.

I just used a simple rotation. The column 1 people (1-5) stayed the same, column 2 people (6-11) moved down 1 row at a time, ... column 4 people (16-20) moved down 3 rows at a time. You could use a formula, but it was quicker here just to do it.

The numbers worked out nicely because there were 5 tables. If instead there were, say, 6 tables, then the column 4 people would move down +3 rows, + 3 rows and then be back where they started from after two days.

Sometimes it's possible to find an easy solution to a particular arrangement.

But I believe the general problem: i people, j tables, k days, is fiendishly difficult.
 
Upvote 0
Hi Stephen - that was great. I have a similar situation.

I'm running a networking event later this month, where I will have 10 tables and 40 people attending.
I will have the people split into groups of 4 & they rove around to these 10 tables in this time, each advising the other 3 people sitting there a little bit about themselves. My aim is that they will all get to meet the other 39 people at for sometime during the night.
My dilemma is to work out how I ensure that I don't double up with people, so I guess I need an equation / way to work this out, if you get my meaning.



StephenCrump said:
You're welcome.

I just used a simple rotation. The column 1 people (1-5) stayed the same, column 2 people (6-11) moved down 1 row at a time, ... column 4 people (16-20) moved down 3 rows at a time. You could use a formula, but it was quicker here just to do it.

The numbers worked out nicely because there were 5 tables. If instead there were, say, 6 tables, then the column 4 people would move down +3 rows, + 3 rows and then be back where they started from after two days.

Sometimes it's possible to find an easy solution to a particular arrangement.

But I believe the general problem: i people, j tables, k days, is fiendishly difficult.
Click to expand...
 
Upvote 0
Welcome to the Forum!

This problem is sufficiently interesting and difficult that it has its own name: the Social Golfer Problem.

There are no general solutions, but there's plenty of material out there, including on this site, if you care to google the term.

For example, here's one way you could have nine rotations without anyone meeting more than once:


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
112345678910111213141516171819202122232425262728293031323334353637383940
215913261017371121481225141822291519233316202437263034382731353928323640
316112527929351015413172481421341216223918232837192631362032353827303340
411215292222834363035491436525333771024328131927111723401618313820212639
518242825113931922254152138614263271835409202327103036371213313416172933
611417392835373182732422313351623266122136713283891524301020254011192934
712236382232532326293745183461333397151727811203092128311016193512142440
811921402121833381731471630520282962427349222635101423381113323715253639
917233121320363163440410293951227386152237893233111824261419283017212535
Sheet1


Source: http://web.archive.org/web/20050407...rc.ic.ac.uk:80/~wh/golf/solutions.html#10-4-9
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
407
Fluff
Fluff
W
  • Solved
Help Me With My Crazy Idea
Replies
4
Views
136
WH2000
W
T
  • Question Question
Problem with this code to put pictures into cells in Excel
Replies
1
Views
337
rlv01
R
A
  • Question Question
How to make a formula advance 8 rows when dragging fill handle
Replies
3
Views
141
Peter_SSs
Peter_SSs
A
  • Question Question
Estimated completion dates
Replies
5
Views
329
felixstraube
F

Forum statistics

Threads
1,223,918
Messages
6,175,365
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top