Pivot/Chart: sorting data

Topic

New Reply

I have a bunch of data that I have collected for a MS office upgrade and I’m trying to sort the data.

I feel as though I’m missing something simple, but I’m just not seeing it.

Attached spreadsheet:
Tab One – My original data is on . The data I want to work with is highlighted
Tab Two – I created a fat-fingered manual table of what I’m wanting the data to appear as so that it can be charted .

I need to some how make the Basic/Intermediate/Advanced the part of the sorting schema and then sort by the application types.

Help/Suggestions please.

Reply | Quote

Replies

Your data is not setup correctly do to create the pivot table you want.

It needs to be more relational and less flat. To use the setup you have you can do it with SUMPRODUCT formulas (see attached)

Steve

Reply | Quote

Attached is a New Pivot tab. In the Sample Data, I removed what looked like personal info.

Reply | Quote

Steve, this is the approach I was working on when your post came though… though I was considerably slower at the coding.

tfspry, this nailed the true essence of what I was wanting to achieve! I’m learning a LOT from this post. It took me a bit to pick apart what you did, but I think I found all of the elements. I have to say that I don’t think I would have ever found the Field Settings>Layout and Print modifications on my own. Now, to play with it more so that I fully grasp it.

THANK YOU to both of you!

Reply | Quote

Perhaps I’m missing something, but tfspry’s pivot does not appear to produce the info you want, as far as I can see? (e.g. it shows LA as all Advanced level, when that is not what the data says).

Reply | Quote

Rory,
Yes you seem to be correct. I only have XL2003 so can not check out the details of tfspry’s pivot table. But it seems to me it is based on a row field of location and Word-expertise and data fields of Word, Excel, PPT, OL-expertises.

I don’t believe it gives what the OP wanted since it is the count of ALL of the expertises when WORD-expertise is of the various levels. It is not looking at the individual expertise levels of XL, PPT, or OL. To do what the OP wants requires a different data setup if a pivot is desired, or a formulaic approach.

Steve

Reply | Quote

Agreed. Since musical1 is using 2007 or later (judging by file type), one could also use SUMIFS rather than SUMPRODUCT which would be slightly more efficient (if there’s a lot of data).

Reply | Quote