Solved

# Seating Chart for weekly card Game

Posted on 2010-01-04
558 Views
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
Question by:ACSPanama

LVL 45

Assisted Solution

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

LVL 2

Author Comment

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

LVL 18

Expert Comment

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

LVL 2

Author Comment

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

LVL 18

Accepted Solution

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

LVL 2

Author Comment

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

LVL 45

Expert Comment

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

LVL 2

Author Comment

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

LVL 18

Expert Comment

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

LVL 18

Expert Comment

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

LVL 18

Expert Comment

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

LVL 45

Expert Comment

Suggested split is fine by me. Patrick
0

LVL 2

Author Comment

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.