• Status: Solved
• Priority: Medium
• Security: Public
• Views: 561

# Seating Chart for weekly card Game

OK we have a weekly card game every monday. We have 3 Tables of 4 people each.
I want to be able to do a seating chart where everybody has a chance to play with everyone else at least once. Example if I assign each player a number:

Week 1
Table-1         Table- 2        Table-3
1                    5                  9
2                    6                  10
3                    7                  11
4                    8                  12

....Now I need to calculate Week-2, Week-3, Week-4...Etc. so that eventully as the weeks go by everyone will be able to sit at a table and have a chance to play with everyone else at least one.

0
ACSPanama
• 5
• 5
• 3
2 Solutions

Commented:
ACSPanama,

Press the button on Sheet1 to get a new set of players on each table. The code below is in the attached file.

Patrick
``````Sub tables()
Dim rng As Range
Dim celle As Range

With Sheets("Sheet1")
Set rng = Range(.Cells(1, "E"), .Cells(.Rows.Count, "F").End(xlUp))
End With

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
``````
card-tables-01.xls
0

Author Commented:
Very Nice - Questions:

1) Is this random, or does it follow a pattern?

2) If it follows a pattern, how many possabilities are there before the pattern repeats itself?

3) If it's random is there a possibility that the pattern repeats itself before all possabilities happen?

I need it to be organized where everyone gets a chance to play at a table with everyone else before it repeats itself.

0

Senior Risk ManagerCommented:

It is random, and therefore, yes it's possible the pattern might repeat itself before all possabilities happen.  Using the attached "seat generator" would give you a template to start with though, and as you got into weeks 4,5,6,etc. you could tweak the given pattern to make sure you had everyone play everyone else before a pattern repeated.

I've been working on a solution that uses a pattern seating arrangement.  So far 10 weeks is the fewest I've been able to have before everyone has played everyone at least once.  I'm pretty certain I can do better.  I'll try a few more things out and post a solution a little later today.

WC
0

Author Commented:
WarCrimes - Thanks for your input - I look forward to your possible solution.
0

Senior Risk ManagerCommented:
Here is a 6 week solution.

For the most part people are on the same table 1 or 2 times in the 6 weeks.  6 never meets anyone more than 2 times, but everyone else has 1 or 2 people they will see 3 or 4 times in the 6 weeks.  I think that's about as balanced as I can make it.

I used a patter for weeks 1-4.  In week 5, I fiddled around trying to eliminate some of the pairings that hadn't shown up yet, then in week 6, I shifted the original pattern up 2 slots (so someone the original pattern would have put in seat 1 of table 1, was now in seat 3 of table 3; and someone seat 3 of table 1 was now seat 1 of table 1).  Hope that makes sense.  Then that pattern is carried out for weeks 7-10, just because I left it there, but you'd probably want to restart after week 6, and my guess would be to shift players so they don't start week 1 in the same spot again.

Anyways, that's about all the time I can devote to this.  I hope it helps.

In the middle table on the attached worksheet, if you change the black cell from 1-12, the two bottom tables will update.  The middle table is who the black cell plays in each week (always includes himself).  The bottom table is a cumulative sum of how many times the black cell has played each other person.

Cheers,
WC
cardtableseatingchart.xls
0

Author Commented:
Nice Job WarCrimes - I have attached your excel file with some conditional formatiing just to liven it up.

I'll Wait for patrickab to have a chance to respond before awarding points. I like his system as well however it needs to have the random element resolved if possible.

cardtableseatingchart-v2.xls
0

Commented:
ACSPanama,

>I like his system as well however it needs to have the random element resolved if possible.

I've been out all day until right now.

My approach is totally random. I have made no attempt to check for duplicate selections because the number of combinations of 4 from 12 is 8! which is enormous. It is of course irrelevant as to what the table numbers are, what matters is that the mix of players is random. To ensure that each week's set-up is different to the previous n weeks worth of players would require a system of recording each week's players and checking new random set-ups against what had been used previously - and I haven't done that as yet because of the enormous numbers of combinations already generated by 8! which is =FACT(8) = 40320.

I reckon that if you're satisfied with WarCrimes solution, you should adopt it. Thanks all the same for waiting for my response.

Patrick
0

Author Commented:
Thanks patrickab,

Perhaps an easier way might be to adapt your approach into MS Access and record the results to a table and then compare those results to the next random search. You are quite correct the table at which a person sits is not relevent.

Anyone know how to adapt patrickab approach to a form with a button on it in MS Access to generate the same random results but compare them to previous results before outputting the results to be saved?
0

Senior Risk ManagerCommented:
I am ok with giving patrick some points, but not an even split.  I would give my answer, 26182558, 400 pts, and patrick's, 26179220, 100 pts.
0

Senior Risk ManagerCommented:
I am ok with giving patrick some points, but not an even split.  I would give my answer, 26182558, 400 pts, and patrick's, 26179220, 100 pts.
0

Senior Risk ManagerCommented:
I am ok with giving patrick some points, but not an even split.  I would give my answer, 26182558, 400 pts, and patrick's, 26179220, 100 pts.
0

Commented:
Suggested split is fine by me. Patrick
0

Author Commented:
Can either of you do the same thing but in MS-ACCESS instead of Excel? That way as I suggested above you could record the results to a table and then compare those results to the next random search and avoid repeats.
0

## Featured Post

• 5
• 5
• 3
Tackle projects and never again get stuck behind a technical roadblock.