PivotTable - group color by Stooge

[Dr. Demento]

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.

  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.

  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!

 

[alansidman]

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.