+ Reply to Thread
Results 1 to 13 of 13

Pivot Table descending sort order of (Blanks) + VBA

  1. #1
    Registered User
    Join Date
    09-05-2014
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    10

    Pivot Table descending sort order of (Blanks) + VBA

    Hello,

    I have a database from which I'm forming a pivot table. The problem is I have blanks as ID, so when I sort descending by the DataField, the (blanks) are on top. I would love them to appear under every other data, or it would be perfect if they would be just sorted corectly, so the blanks would find themselves between he other IDs.

    I think the picture will explain everything. I want (blanks) to be sorted correctly among other data, or if not possible, to show under the filled-in IDs.

    07205165288992381914.png

    I'm using a VBA code to sort:

    Code:
    Please Login or Register  to view this content.
    Is there any way to help me or achieve to desired effect in different way?

    Thank everyone for help.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Pivot Table descending sort order of (Blanks) + VBA

    I think the empty values, belong to the above criteria.

    If so , you need a formula, or VBA to fill the data.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    09-05-2014
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    10

    Re: Pivot Table descending sort order of (Blanks) + VBA

    Hey, thank you for an immediate answer.

    I'm not sure if I understand. I can fill the blanks with what data? I tried to put for example 0 instead of blanks (so that they should be shown last), but then still they are on the top - only (blanks) changes to 0

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Pivot Table descending sort order of (Blanks) + VBA

    I expect that EPP and EWW belongs to the value (ID) 12345.

    on this moment the ID cell in EWW is empty.

    So it (EWW) whill show up on the empty ID.

    I expect you want it to show with the ID 12345.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot Table descending sort order of (Blanks) + VBA

    Perhaps select the column in your source data (not pivot), press f5, Special... and chose Blanks and press OK. Then type = and press the Up arrow key and then press Ctrl+Enter. Now refresh the pivot table.
    Remember what the dormouse said
    Feed your head

  6. #6
    Registered User
    Join Date
    09-05-2014
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    10

    Re: Pivot Table descending sort order of (Blanks) + VBA

    Oh, that is my fault, I didnt explain it well. It is not like this.
    EPP does not belong to ID 12345. It belongs to a blank, because I have no information about this data.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot Table descending sort order of (Blanks) + VBA

    You could just manually drag the blank item to the bottom of the pivot.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Pivot Table descending sort order of (Blanks) + VBA

    Then use the (normal) sort function on the ID column in the Pivot table.

    It will make the blanc items go to the end.

  9. #9
    Registered User
    Join Date
    09-05-2014
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    10

    Re: Pivot Table descending sort order of (Blanks) + VBA

    @romperstomper
    It seems to work. I will be able to check it again carefully tomorrow. The only thing I need is this to be done automatically, so maybe I will be able to do it in VBA.

    @oeldere
    But I need to have the data sorted by Values, the order of ID doesn't matter. Only that blank values doesn't look good on top because their value is usually smaller.

    If there is a unique column id the data source, let's say the name of a company, even the blanks have on their right side the company's names, does it change the situation and maybe then I can sort blanks accordingly to a value and not put them just after everything?

    Thank you guys for help

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot Table descending sort order of (Blanks) + VBA


  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Pivot Table descending sort order of (Blanks) + VBA

    @bezwlosy

    Please take your time tor read the link below.

    http://www.excelguru.ca/content.php?184

  12. #12
    Registered User
    Join Date
    09-05-2014
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    10

    Re: Pivot Table descending sort order of (Blanks) + VBA

    I read this, you are right. It's my fault. I also posted here: http://www.ozgrid.com/forum/showthread.php?t=190803
    I am sorry, from now I know how to do.

    If anybody will still be wanting to help, the way romperstomper suggested, doesn't work for me correctly, because if I have another Pivot Field in front of everything, and I try to drag manually (Blanks) to the last, among this previous Pivot Field, nothing happens.

    I'm wondering why, if I change blanks to 0, it is still sorted on top if this is the last number?

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Pivot Table descending sort order of (Blanks) + VBA

    post the excel file on the forum, without confidential information.

    i will take a look at it and will come back to you.

+ 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. Automatic sort by descending order
    By anapaulacasseta in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2014, 10:49 PM
  2. [SOLVED] Sort by descending order, but the blank spaces come on top
    By billj in forum Excel General
    Replies: 3
    Last Post: 07-18-2013, 04:18 PM
  3. Pivot table to show certain column in descending order
    By rushdenx1 in forum Excel General
    Replies: 1
    Last Post: 01-04-2012, 05:32 AM
  4. Can you sort a chart out in Ascending/Descending Order
    By dandavis1 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-26-2010, 09:52 AM
  5. Sort in descending order
    By shahcu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2008, 03:01 AM

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