Pivot Table Assistance Needed

[mistye525]

Hello,

A little background. I'm trying to create a map of seats sold. The data I pull from my system will have multiple lines for each seat depending on the status of the seat. I am struggling to come up with a less time consuming solution to combine the data. The attached sheet shows the pivot table I was able to come up with from my raw data and a little tweaking. Now, I need to take it one step further but I'm stuck.

I would like to end up with only one line for this set of data. What's the easiest way to do that?

Excel Forum Example 07.29.13.xlsx

[oeldere]

Please add the desired (expected) result in your file.

[mistye525]

I would like to take the data on Sheet3 and make it so that it's all on one line like on the Desired Result tab. Ultimately, what I will end up with using all my data is something like Final Desired Result. Once I can get all of the data on the same line for each tm_row_name, I can use conditional formatting to color the cells, which will create the seating map I will need to work from.

[oeldere]

Ok, and what your gonna do with thise line of information.

Count, analyze.

What's the goal of this step?

[mistye525]

I'm going to analyze.. The live data will have around 50 or more different tm_row_names. So I need to get to one line for each tm_row_name.

[oeldere]

Maybe like this.

I used a macro to rearange the data.

[mistye525]

That was quick! Thank you! To analyze in the way I want, I would like to retain the column labels (those are my seat numbers), and end up with the characters H,S, and O on the same line as the row name. If I can do that, when I'm done, the characters will automatically create a seat map for me when I use conditional formatting. I've attached a new file that shows what I'm trying to ultimately end up with.

[oeldere]

OK, maybe it's clear for you, but to me it's only collored lines with letters in it.

I bet you gonna do something with that data of information (or am I wrong)?

[mistye525]

Ha! Yes, that's essentially what it is, but for me, it's a visual representation of seats in a theater. The colors will quickly show me where I have gaps in seating that I need to fill.

[oeldere]

I get the point what you're trie to achieve.

I don't think that can be done by pivot table (as you asked for).

You will need some-one to write a macro for that (i suppose).

I can't help you enough with that.

Please wait till another forummember will step in.

[mistye525]

Thanks for your help, I appreciate it!