PivotTable - group color by Stooge

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
620
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Morning.

I have a PivotTable that has the following characteristics:
Filter: Inclusion (Y/N), Year
Row: Stooge, Color
Value: count

The first image is the original output. However, I want to group all colors that have counts less than 20 BUT I want to have the colors be grouped under each Stooge, not all together (see second image for desired output).

The code below hides the appropriate colors (>=20) but doesn't group the remaining colors. I realize setting the range as I have maybe the issue, but I can't figure out what the appropriate code is. If I use the pvtItem.LabelRange, I get the color under ALL Stooges, not just the one I want to group.

VBA Code:
  For Each stg In tvp.PivotFields("STOOGE").PivotItems
    For Each pi In pf.PivotItems
      If tvp.GetPivotData("Count", "STOOGE", stg, "COLOR", pi.value) < 20 Then
        Set rng = ProperUnion(rng, tvp.GetPivotData("Count", "STOOGE", stg, "COLOR", pi.value))
        Debug.Print stg, pi, tvp.GetPivotData("Count", "STOOGE", stg, "COLOR", pi.value)
      Else
        pi.Visible = False
      End If
    Next pi
    
    ' ~~ Group low COLOR counts by STOOGE & rename
    rng.Group  ' <-- This doesn't work as expected
    
    'What next??
    
  Next stg

I tried using the pvtLabel and using the count of pvtField to extract the desire range (STOOGE#1, STOOGE#2, etc), but as I step thru my code, the pvtItem are presented alphabetically and if anywhere in the table the value of that pvtItem is less than 20, it captures it.

VBA Code:
  For Each stg In tvp.PivotFields("STOOGE").PivotItems
    lSTG= lSTG+ 1: Set rng = Nothing
    For Each pi In pf.PivotItems
      If tvp.GetPivotData("Count", "STOOGE", STG, "COLOR", pi.value) < 20 Then
         Set rng = ProperUnion(rng, pi.LabelRange(lSTG))
'        Set rng = ProperUnion(rng, tvp.GetPivotData("Count", "STOOGE", STG, "COLOR", pi.value))
        Debug.Print STG, pi, tvp.GetPivotData("Count", "STOOGE", STG, "COLOR", pi.value)
      Else
        pi.Visible = False
      End If
    Next pi
  Next stg

I'm out of ideas. Well, I do have one more - to test if the COLOR pvtItem exists under the pvtField = STOOGEname, but I don't know how to make that work either.

Am i SOL??

Thanks, y'all!
 

Attachments

  • Picture1.png
    Picture1.png
    13.2 KB · Views: 4
  • Picture2.png
    Picture2.png
    25.3 KB · Views: 3

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Cannot see details in the pictures you have posted. All black. Suggest you repost using XL2BB function so that we can see raw data and expected output.
 
Upvote 0
Crap! Thanks for the feedback, Alan.

I'm not able to use XL2BB but here's a better graphic. The data on the left is the normal/original pivottable output; the right-hand column is the desired output.

After tinkering around, I've come up with code that addresses some of the issues I was having with my previous code.

VBA Code:
      Set pfCOLR = tvp.PivotFields("COLOR")
      Set pfSTG = tvp.PivotFields("STOOGE")
      pfCOLR.ClearAllFilters: pfSTG.ClearAllFilters
     
      ' ~~ You MUST start at bottom and work up, o/w ranges get jacked
      For cntSTG = tvp.PivotFields("STOOGE").PivotItems.Count To 1 Step -1
        Set piSTG = tvp.PivotFields("STOOGE").PivotItems(cntSTG)
        Set rngU = Nothing
       
        For cntCOLR = pfCOLR.PivotItems.Count To 1 Step -1
          Set piCOLR = pfCOLR.PivotItems(cntCOLR)
          If pvt_existPivotItem(piSTG, piCOLR, tvp.DataFields(1).Name) = True Then
            Set rng = tvp.GetPivotData(tvp.DataFields(1).Name, piSTG.Parent, piSTG, piCOLR.Parent, piCOLR)
            If rng.value < 20 Then _
              Set rngU = ProperUnion(rngU, rng.offset(, -1))  ' ~~ Offset needed b/c testing PivotData value but need to group based on PivotLabel range
          End If
        Next cntCOLR
If Not rngU Is Nothing Then Debug.Print piSTG, rngU.Address

        On Error GoTo 0
        ' ~~ Group low COLR counts by STOOGE & rename
'        rngU.Group

' ??? Potential solutions ???  https://www.mrexcel.com/board/threads/multi-level-grouping-in-pivot-tables.485601/ _
                    https://www.excelanytime.com/excel-pivot-tables-grouping-group-items-group-data-and-group-date-values-using-vba/#Group%20Specific%20Text%20Field%20Items%20in%20a%20PivotTable%20report
     
      Next cntSTG

And here's how I test if a PivotItem exists within a separate PivotField:
VBA Code:
Function pvt_existPivotItem(ip1 As PivotItem, _
                            ip2 As PivotItem, _
                            fd As String) As Boolean
Dim pvt As PivotTable
  Set pvt = ip1.Parent.Parent
Dim pf As PivotField
  Set pf = ip2.Parent
Dim pi As PivotItem
Dim rng As Range
 
  With pvt
    For Each pi In pf.PivotItems
      If pi = ip2 Then
        On Error Resume Next
        Set rng = .GetPivotData(fd, ip1.Parent, ip1, pf, pi)
        On Error GoTo 0
        If Not rng Is Nothing Then
          pvt_existPivotItem = True
          Exit Function
        End If
        Set rng = Nothing
      End If
    Next pi
  End With
 
End Function

However, because Grouping is done by Range and the LabelRange includes ALL ranges where a PivotItem is located under all other PivotFields, even if I select a single cell I get a jumble of groupings.

Thanks, y'all!
 

Attachments

  • Picture1.jpg
    Picture1.jpg
    100 KB · Views: 0
Upvote 0

Forum statistics

Threads
1,226,527
Messages
6,191,568
Members
453,665
Latest member
WaterWorks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top