Dr. Demento
Well-known Member
- Joined
- Nov 2, 2010
- Messages
- 619
- Office Version
- 2019
- 2016
- Platform
- 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.
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.
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!
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!