PivotTable - group color by Stooge

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
619
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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

Forum statistics

Threads
1,226,516
Messages
6,191,500
Members
453,660
Latest member
Wp1902

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