Custom order of pivot items

Posted on 2014-01-26
Last Modified: 2014-01-29
Dear Experts,
I have a simple pivot table that is generated by code with a user-chosen data set. Since I want to print the result along with other tables, I need to constrain the number of rows to a fixed number. My strategy is to sort the PT by the single data field (a sum, descending), then group the pivotitems that exceed a certain number of rows into an "other" category,  My problem is I'd like to retain the descending sort based on the data field, but force "other" to always be at the bottom row.  I know I can set the pivotitem.position, but I can't seem to understand how excel assigns item positions, and their relationship to the ordinal position in the list.

Here's  a snippet:
 With PT 'ActiveSheet.PivotTables("SR4")
        iNumSppRows = WorksheetFunction.CountA(Range("J" & iSumRowStart & ":J80")) - 2 'remove total row
        iLastSppRow = iSumRowStart + iNumSppRows  'store a pointer to the last row
         If iNumSppRows > 11 Then
            Range(Cells(iSumRowStart + iMaxListSize, 10), Cells(iLastSppRow, 10)).Select  '10 is that max spp well accept
            'store the list of spp in a range, then group
            Selection.Group   'group the >10 spp into an OTHER group

          ' NB The distinctions between Species and Species2 below.
          'Hide Species, collapse species2, rename group1
          'And finally, move it to the bottom of the species list
            .PivotFields("Species2").ShowDetail = False
            .PivotFields("Species").Orientation = xlHidden
            .PivotFields("Species2").AutoSort xlDescending, _
        "Sum of BF_Total", ActiveSheet.PivotTables("SR4").PivotColumnAxis.PivotLines(1) , 1
     '      .PivotFields("Species2").PivotItems("Group1").Position = 16 '  PROBLEM
            .PivotFields("Species2").PivotItems("Group1").Caption = "Other"
         End If
   End With
Question by:RJT_VT
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 43

Expert Comment

ID: 39816430
Unfortunately I don't believe this is possible.  have you seen otherwise?

When it comes to data warehousing and business intelligence, excel is very limited.  You may be interested in Qlikview ( a very simple and easy to use reporting tool.

Author Comment

ID: 39816621
I have seen a solution that suggests one can sort all pivotitems in a recordset to reorder the position, but the description was cryptic.  Hard to believe you can't replicate what takes two seconds with a mouse!  
This needs to be distributed, so qlikview won't work.
LVL 43

Expert Comment

ID: 39816783
Qlikview is a distributed system but we won't go down that path and too big a learning curve...

If you can do it via the mouse, have you tried recording a macro while you're doing it?  I know the number of rows will vary but that's easily fixed.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 85

Accepted Solution

Rory Archibald earned 250 total points
ID: 39817336
Probably easier to reverse what you are doing and move each of the original top 10 items back to position 1 in reverse order:
   Dim n As Long
   Dim vSortData

   With PT   'ActiveSheet.PivotTables("SR4")
      .PivotFields("Species").AutoSort xlDescending, .DataFields(1).SourceName, .PivotColumnAxis.PivotLines(1), 1
      With .RowFields(1).DataRange
         vSortData = .Cells(1).Resize(10).Value
         If .Count > 11 Then .Cells(11).Resize(.Cells.Count - 10).Group   'group the >10 spp into an OTHER group
      End With
      ' NB The distinctions between Species and Species2 below.
      'Hide Species, collapse species2, rename group1
      'And finally, move it to the bottom of the species list
      .PivotFields("Species").Orientation = xlHidden
      With .PivotFields("Species2")
         .PivotItems("Group1").Caption = "Other"
         ' move each of the top 10 back to position 1 in reverse order
         For n = UBound(vSortData, 1) To LBound(vSortData, 1) Step -1
            .PivotItems(vSortData(n, 1)).Position = 1
         Next n
      End With
   End With

Open in new window


Author Closing Comment

ID: 39817648
Worked like a charm. Thank you for an elegant solution that both solves the problem and adds measurably to my knowledge!
LVL 85

Expert Comment

by:Rory Archibald
ID: 39817822
Glad to help. :)

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question