Excel: parsing seating chart, names - How best to do this?

August 9, 2013 3:08 PM

I have an Excel Workbook with seating charts for our theatre. There are 4 pages, one for each production. Each page contains columns for 9 performance dates and rows for every seat in the theatre (about 300). I need to get this all into one readable sheet that I can use. Read on (tasty screenshots inside.).

When someone buys a season subscription, I put their name in each individual cell representing a seat they have. Therefore, for someone who buys two seats per show, their name is in the workbook 8 times (2 seats X 4 shows). See screenshot here: http://postimg.org/image/ykam19rpx/

Here is what I need: A list of all the individual subscriber names, with a listing of every date and seat number they have. This information is across 4 sheets, but within the same book. In the end, here is a mock-up example of what I hope to have: http://postimg.org/image/5pwnbg12v/

Having the info in readable order is more important than the exact layout. Also, I may end up drawing from this sheet to populate fields in a Word document (letter to each subscriber informing them of their seat assignments). May.

Do I need a pivot table? Something else that I don't know about?

I haven't done much formula work at all in about 10 years. And I don't know what I don't know. You guys rock.

posted by skypieces to Computers & Internet (7 answers total)

I think a pivot table would work for what you want. I'd put the names as rows, use the dates as columns headers, and populate with the seat numbers as the data. If you want to be able to filter by subscriber name, use the dates for rows and put the names into the columns and you should be able to filter by subscriber. Once you've got the data arranged the way you want it, you can copy the table and paste special-values into another sheet so you just have the data without the table behind it for use with a mail merge (if that's what you're thinking of doing.) you may have to play with the field settings on the table - count vs sum, whatever.

First things first, you'll need to get the data that's currently spread across 4 sheets into one.

Oh - if the pivot table is driving you batshit (they were counterintuitive to me at first and i'm in excel all day every day for work), you might play with the subtotal function and get to the same place in the end, with slightly more manual work.

I am at home on my phone so I can't give you anything more step by step than that right now. If you're still stuck on Monday (I hope not!) you can memail me the data and I'll take a shot at it.
posted by data hound at 3:37 PM on August 9, 2013

This is the kind of problem that relational databases are meant to solve.

You could use Zoho Creator, a web app, to do this, and it sounds like you could get away with their free plan for this. The other option would be something like Filemaker Pro, a desktop app.

Relational databases are a conceptual leap from spreadsheets. This is a not-bad description of the concept.
posted by adamrice at 3:39 PM on August 9, 2013 [1 favorite]

Also - you might need to have one pivot table per performance. but you should be able to get it all onto one nicely readable sheet.
posted by data hound at 3:39 PM on August 9, 2013

Do you need to do this once, or do you need a regularly used automated solution?

adamrice is completely correct that this is what relational databases (Access would do) are for.
It's a 5 second job in a decent database.

What version of excel are you using?
posted by Just this guy, y'know at 5:18 PM on August 9, 2013

Right.

I have a sort of solution.

It basically works like this. In a new sheet (probably one per performance, we can merge them together later) make a list of every seat (Column A) and every date for that seat(Column B).
You'll end up with 2700 rows.
(300 seats * 9 dates, see combined below)

Then stick this formula in column C:
=OFFSET(Sheet1!$A$2,MATCH(A2,Sheet1!$A$3:$A$300,0),MATCH(B2,Sheet1!$B$2:$J$2,0))
(You will need to tweak it a bit to fit where your data is)

What this is doing:
The offset function finds a specific cell (Sheet1, Cell A2 in this case) and then tells you what is in a different cell X columns away and Y rows away. So we use match to provide the X and Y values. Match looks at a bit of text and then tells you where in a list that text is.

I've included my fairly crappy mock up in pics below.

Source
Combined

You would then be able to sort the whole lot on column C of your merged cells and get a list ordered by subscriber listing seats and dates. Or throw it into a pivot table and sort it out that way.

It's very rough and ready, but it's a start. (It's also 2am, so please excuse the crudity of this model. I didn't have time to build it to scale or paint it.)
posted by Just this guy, y'know at 6:03 PM on August 9, 2013

I can send you my trial file if you like also.
Just memail me and I'll send it tomorrow morning.
posted by Just this guy, y'know at 6:08 PM on August 9, 2013

Response by poster: Wow. Got busy and decided to check back late to see how this went. This all makes tons of sense. Let me catch some Z's and I'll fiddle with this in the morning. Many, many thanks!
posted by skypieces at 9:27 PM on August 9, 2013