Formula for Seating Chart?

**skrepsky** · 05-08-2010, 11:12 PM

Hi All,
I work for a college where we set up a seating chart for our Board, when they meet at our school for their trimester meetings. We try not to have them sit with the same people time after time and in fact prefer no repeats within the past two meetings. I know there probably not a way to completely take care of this, but perhaps something that produces a nice outline that gets me most of the way?

I've started by assigning each of our guests a number and then assigning them a table number. So is there a way to create a logic formula (or any kind of formula) that would look to see who they sat with last time and then give them a table number that has occupants that are different from before?

**JBeaucaire** · 05-09-2010, 01:34 AM

Highlight J2:I29
It's important you start with J2 so column J is the primary column.

Then sort descending. The cells that still match the prior dinner will be in yellow.

Keep pressing Sort down until there are no yellows or until there are so few you can swap them manually. The yellows are the seats that match the same table as the dinner prior. This is done with conditional formatting.

To add a new dinner, copy the last two columns over one column, highlight them like before starting with the second column and going down to the bottom of the prior column, then do the sort trick again.

**skrepsky** · 05-09-2010, 02:21 PM

This sort of helps, but doesn't take care of the main problem which is that they are sitting with the same people. It doesn't necessarily matter if they sit at the same table, but what does matter is if they sit with the same people again. Perhaps would there be a way to make a cell contain their previous tablemates (i.e. the cell would have 2, 12, 13, 15,) and if any of the numbers matched up with their current one it would highlight? Also how exactly did you set up the conditional formatting so that it highlight the cell?

**JBeaucaire** · 05-09-2010, 03:07 PM

You can read up on conditional formatting by pressing F1 and calling up the Excel help file.

You can see the formula I used by highlighting a cell and looking in Format > Conditional Formatting.

Yeah, the names are a kicker. I'll have to mull that over.