+ Reply to Thread
Results 1 to 13 of 13

Pivot: sort by two columns

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Pivot: sort by two columns

    I have a worksheet with the following columns:
    DEPTID, POSITION, PROGRAM, VENDOR Name, and Grand Total

    From this data, I want to create a pivot table, with DEPTID, POSITION, VENDOR Name and Grand Total for my fields. I can do that just fine, but I want my DEPTIDs to be in Ascending Order, and within each Dept ID I want my Grand Totals in Ascending order. How do I do that?

    I appreciate any help.
    Last edited by jomili; 01-27-2012 at 03:27 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Pivot: sort by two columns

    Hi jomili,

    I think you need to group the row data and then sort within the group. See if http://www.excelforum.com/excel-gene...-in-excel.html doesn't help. I think I gave an example on it. If not then supply a sample workbook and we'll see if we can make it do what you want.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Pivot: sort by two columns

    Hi,

    if you click twice on the grey label of right-most row field (maybe DEPT ID), you can open the pivot table window: click on Advanced then- in the new window -select Autosort option- Ascending -using field "Grand total".

    I hope this is useful.

    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pivot: sort by two columns

    Marvin,
    I didn't find anything in the link that would help with me present situation. See the attached workbook to see if this makes my problem any more clear. I'm open to either a manual method within the PT setup, or a VBA solution.

    Canapone,
    See the attached workbook. I tried your solution, but couldn't get it to work. If I sort by Grand Total, I lose my sort by DeptID. I need both.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Pivot: sort by two columns

    Hi, I've set ascending order by clicking -also- on "Position" label in M3.

    Hope it helps
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pivot: sort by two columns

    A-ha! I knew there had to be a way! Thank you so much for point it out.

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Pivot: sort by two columns

    Hi,

    forgot the subtotals in the pivot table.

    Regards
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-30-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2013
    Posts
    1

    Question Re: Pivot: sort by two columns

    I have a spread sheet that I do want to sort by different columns however it is not sorting by the second column. My first column is (Dept) which is text data type and the sorting works fine however, the second column is DueStartDate which is a date field and it is not working/sorting with or without the Dept being sorted. The example worksheet above has formatted the POSITION as TEXT and VENDOR_NAME as general on the source tab. I have tried to set my date values in my source tab with formatting (Number format is Date) and it makes no difference. Even if the excel is assuming they are Text fields I would have thought it would still sort the 1's before the 2's as in (assume d/m/y format (i.e. I live in Australia) 1/2/2014 is before 2/1/2014 in alphabetical order however, it does nothing, no sorting whatsoever. Can someone provide a solution.
    pivot.png

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Pivot: sort by two columns

    GryPTonite, hi and welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    Also, Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff)

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Registered User
    Join Date
    10-02-2015
    Location
    Switzerland
    MS-Off Ver
    Excel 2013 through Office 365
    Posts
    1

    Re: Pivot: sort by two columns

    I can't seem to be able to use that technique if the values of the cells are COUNT(x), am I correct?

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Pivot: sort by two columns

    vinnie2k,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  12. #12
    Registered User
    Join Date
    03-24-2020
    Location
    Poland
    MS-Off Ver
    Office 365
    Posts
    1

    Re: Pivot: sort by two columns

    Hi canapone,
    I have no idea how did you click "-also-". I can not see such option. :|
    I will be grateful for step by step instruction how to sort by two columns.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Pivot: sort by two columns

    Administrative Note:

    Hello panArbuz and Welcome to Excel Forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1