Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Pivot/Chart: sorting data

    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.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    Attached Files Attached Files

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    musical1 (2011-03-05)

  4. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Attached is a New Pivot tab. In the Sample Data, I removed what looked like personal info.
    Attached Files Attached Files

  5. The Following User Says Thank You to tfspry For This Useful Post:

    musical1 (2011-03-05)

  6. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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!
    Last edited by musical1; 2011-03-05 at 21:49.

  7. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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).
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  9. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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).
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •