+ Reply to Thread
Results 1 to 13 of 13

Sort data from drill down in Pivot

  1. #1
    Registered User
    Join Date
    05-03-2022
    Location
    Near Liverpool, England
    MS-Off Ver
    Version 16.61 for Mac
    Posts
    7

    Sort data from drill down in Pivot

    Hi

    I have a spreadsheet which has several pivot tables in. When I double-click on a value in one of those tables, the results are displayed in what appears to be a totally random order. Is it possible to have the output sorted into a user-defined order?

    If VB is the answer then I'll be brutally honest. I've never written code in VB in my life. My last attempt at writing code was COBOL in 1999 so if the answer is VB I would need to be hand-held all the way through.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Sort data from drill down in Pivot

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    Re: Sort data from drill down in Pivot

    Hi n654us and welcome to the forum,

    There is a User Defined Custom Sort order hiding in Excel. It is for things like sorting days of the week or month names but you can build you own custom sort order. Here is a link to that topic:
    https://www.bing.com/search?q=excel+...ANNTA1&PC=U531

    I'd expect you don't have the suggestion above but rather spaces in front of some of the text you are trying to sort AND numbers mixed in with text strings. This is a much more common problem.
    Last edited by MarvinP; 05-04-2022 at 12:20 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    05-03-2022
    Location
    Near Liverpool, England
    MS-Off Ver
    Version 16.61 for Mac
    Posts
    7

    Re: Sort data from drill down in Pivot

    Hi Marvin

    Thanks, but that's not what I was looking for, although I've never actually come across custom sorts previously. I guess I've never needed them.

  5. #5
    Registered User
    Join Date
    05-03-2022
    Location
    Near Liverpool, England
    MS-Off Ver
    Version 16.61 for Mac
    Posts
    7

    Re: Sort data from drill down in Pivot

    Hi Alan

    Attached spreadsheet as requested. The active tab when opened shows the drill down from the By Month pivot. There doesn't appear to be any logical sort applied. "As", "Type", etc. The only one I can see that's in some sort of order is the "Where" column but I'm sure that's just coincidental.
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Sort data from drill down in Pivot

    How would you like it sorted?

  7. #7
    Registered User
    Join Date
    05-03-2022
    Location
    Near Liverpool, England
    MS-Off Ver
    Version 16.61 for Mac
    Posts
    7

    Re: Sort data from drill down in Pivot

    Ideally, user-defined at run time. If that's not possible, then it's going to differ for each pivot.

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

    Re: Sort data from drill down in Pivot

    Hey n654us,

    On your "By Month" pivot table the "10" is text while all others are "numbers". That would explain why "10" is sorted less than 02. Text is treated as zero when sorted by numbers.

  9. #9
    Registered User
    Join Date
    05-03-2022
    Location
    Near Liverpool, England
    MS-Off Ver
    Version 16.61 for Mac
    Posts
    7

    Re: Sort data from drill down in Pivot

    Understood Marvin. But why does the pivot choose that particular column for the sort?

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

    Re: Sort data from drill down in Pivot

    I believe Pivots always start with a sort on the left-most Lable column. You can click in that column and reverse or custom the sort order.

    The 10 being text and the 02 being a number was my suggestion above on the confusion on sorting order.

  11. #11
    Registered User
    Join Date
    05-03-2022
    Location
    Near Liverpool, England
    MS-Off Ver
    Version 16.61 for Mac
    Posts
    7

    Re: Sort data from drill down in Pivot

    Attachment 779459

    I hope the screenshot has attached. If so, I can see nothing that is sorted in any kind of order at all. It just seems totally random. Even the first column, which only contains a P or blank, are not sorted in order.

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

    Re: Sort data from drill down in Pivot

    Selecting attachment 779459 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
    I believe that the drill down in the file attached to post #5 was from the By Operator pivot table, Air France having been selected.
    When you compare the drill down sheet to the Log sheet, filtered by the Operator column, the "As" column is in reverse order.
    My thought is that when the pivot table was drilled down Excel started at the last row of the source data that contained Air France and then worked its way up.
    I am not sure that this will be helpful in accomplishing your goal unless you sort/custom sort the source data in such a way that information you would like to see at the top of the drill downs is at the bottom of the source data (Hope that makes some sense).
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  13. #13
    Registered User
    Join Date
    05-03-2022
    Location
    Near Liverpool, England
    MS-Off Ver
    Version 16.61 for Mac
    Posts
    7

    Re: Sort data from drill down in Pivot

    The full spreadsheet of approx 39,000 rows is sorted by a hidden column called YMD (year/month/day, oldest to newest) followed by the "As" column. But in all of the pivot tables, any drill down doesn't put the output in that format at all. Selecting one airline showed all the dates in reverse order but then selecting a second showed that the dates appeared to be in reverse date order but then I get this part way through:

    1999-01-26
    1998-12-21
    1979-06-24
    1998-12-21
    1979-06-24
    1979-06-24
    1998-12-21
    1998-07-04
    1979-06-24
    1979-06-24
    1979-06-24
    1979-06-24
    1998-04-02

    It just doesn't make sense...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Problem using data model in pivot table to drill down to records
    By Excel_apprentice in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 02-28-2022, 12:30 PM
  2. Replies: 3
    Last Post: 10-20-2019, 12:15 PM
  3. Pivot drill-down - changing sort order
    By fizzy in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-17-2017, 11:06 AM
  4. Pivot Table Drill Down Doesn't Match Master Data
    By ifdinc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-21-2017, 04:04 AM
  5. Pivot drill down data to a SQL query
    By lafi2014 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-27-2014, 04:37 AM
  6. Change Default Formatting of Pivot Drill-Down Data
    By lsofia in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-31-2012, 09:53 AM
  7. Pivot table drill down sort rules
    By haulinthechilli in forum Excel General
    Replies: 0
    Last Post: 09-15-2010, 06:36 PM

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