pivot table question

[olympiac]

I have a huge amount of data filtered in a pivot table. The xample below is simplified to understand the feasibility of my request.
Heading:name Heading:animal
name 1 cat
name 2 cat
name 3 cat
name 4 cat
name 5 cat
name 6 cat
name 7 dog
name 8 dog
name 9 dog
name 10 dog

Is it possible to filter the pivot table by "cat" (column label) and keep all the 10 names (Raw label)? From name 7 to 10 I need empty value in this example.
Is it feasible?

 

[Jerry Sullivan]

Is the example you show your data source, or what your pivot table looks like before it is filtered by cat?

If it's the latter with cat and dog as column labels then it would look more like the first images below.

Excel Workbook
	F	G	H	I
2	Before Filter
3		Column Labels
4	Row Labels	Cat	Dog	Grand Total
5	Name 1	12		12
6	Name 2	14		14
7	Name 3	16		16
8	Name 4	18		18
9	Name 5	20		20
10	Name 6	22		22
11	Name 7	24		24
12	Name 8		26	26
13	Name 9		28	28
14	Name 10		30	30
15	Grand Total	126	84	210
Sheet

To display as shown in the second image after filtering...

• Go to Field Settings for the "Name" field
• On Layout & Print Tab
• Check "Show items with no data"

Excel Workbook
	F	G	H	I
2	After Filter
3		Column Labels
4	Row Labels	Cat	Grand Total
5	Name 1	12	12
6	Name 2	14	14
7	Name 3	16	16
8	Name 4	18	18
9	Name 5	20	20
10	Name 6	22	22
11	Name 7	24	24
12	Name 8
13	Name 9
14	Name 10
15	Grand Total	126	126
Sheet

 

[olympiac]

Thanks to the feature "Show items with no data", the pivot table displays now all the names in the table
What about if we want to filter this list and show only specific names by applying a filter?
Let's suppose that there is an additional field related to a country and we want to show all the people in a specific country that have got a cat.In this case, some names that do not belong to that country should not appear in the pivot.
Is it possible to apply a filter to the feature "Show items with no data"? If not, is there an alternative?
Thanks

 

[Jerry Sullivan]

Thanks to the feature "Show items with no data", the pivot table displays now all the names in the table
What about if we want to filter this list and show only specific names by applying a filter?
Let's suppose that there is an additional field related to a country and we want to show all the people in a specific country that have got a cat.In this case, some names that do not belong to that country should not appear in the pivot.
Is it possible to apply a filter to the feature "Show items with no data"? If not, is there an alternative?
Thanks

That's an interesting question. Let's say you have the Source Data below, and you want to display a list of names with Cats in Ireland.

Excel Workbook
	A	B	C	D
1	Source Data
2	Name	Country	Animal	Value
3	Name 1	Ireland	Cat	1
4	Name 2	Ireland	Cat	2
5	Name 3	Ireland	Dog	3
6	Name 4	Ireland	Dog	4
7	Name 5	Finland	Cat	5
8	Name 6	Finland	Cat	6
9	Name 7	Norway	Cat	7
10	Name 8	Norway	Dog	8
11
Sheet

If you filter for Cats and Ireland, you'll get this result with "Show items with no data" Checked for Names.
This includes people who are not in Ireland as you pointed out.

Excel Workbook
	G	H	I
5			Animal
6	Country	Name	Cat
7	Ireland	Name 1	1
8		Name 2	2
9		Name 3
10		Name 4
11		Name 5
12		Name 6
13		Name 7
14		Name 8
15	Grand Total		3
Sheet

If you filter for Cats and Ireland, you'll get this result with "Show items with no data" Unchecked for Names.
This excludes some people who are in Ireland, but don't have a cat (and your OP question was how to show these names).

Excel Workbook
	G	H	I
5			Animal
6	Country	Name	Cat
7	Ireland	Name 1	1
8		Name 2	2
9	Grand Total		3
Sheet

I don't know of a way to display this "desired result" in the PivotTable using only filters and settings from that datasource.

Excel Workbook
	G	H	I
5			Animal
6	Country	Name	Cat
7	Ireland	Name 1	1
8		Name 2	2
9		Name 3	0
10		Name 4	0
11	Grand Total		3
Sheet

One work-around which I used to produce the desired result image is to modify datasource to include records for 0 Cats and 0 Dogs.
It's not an ideal solution, but it would allow you to get the display you want. If you decide to use that work around, it would probably be best to have a separate field in your datasource for each type of animal instead of a single field for all animals.

 

[olympiac]

Smart solution however I cannot apply it to my data source because I have got more than 50 different items (let's say 50 different anilmals in our example)
Another work around is to create a pivot table with only one field (Raw label) where all the animals are listed and a main Page filter to choose the Country. The idea is to synchronise this pivot table with the main pivot table. By doing so, if I select the country "Ireland" and the animal "Cat" in the main pivot table, the second pivot will display all the people that have got a "cat" in Ireland.
Next step is to apply a Vlookup in a column next the main Pivot table (remember that the feature Show items with no data" is applied). By doing so, I can see the people that are listed in the second pivot table.
Next step is to call a VBA code to hide the rows with (iserror) as a result of the Vlookup.
This work around works fine but the problem is that unfortunately when I select multiple items (multiple countries) in the main pivot table, the second pivot table does not synchronise.
Do you know how to Select multiple items in one of the pivot table's page fields and get another table synchronised?

 

[Jerry Sullivan]

Do you know how to Select multiple items in one of the pivot table's page fields and get another table synchronised?

You could try the code below. Each time there is a change to PivotTable1,
it will synch the Visible state of each item in your Country Field in PivotTable2.

Copy this into the code space for the sheet that has PivotTable1

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet
        If Intersect(Target, .PivotTables("PivotTable1") _
            .TableRange2) Is Nothing Then Exit Sub
        
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Call Synch_PT_Filters( _
            PT1:=.PivotTables("PivotTable1"), _
            PT2:=.PivotTables("PivotTable2"), _
            sField:="Country")
    End With
CleanUp:
    Application.EnableEvents = True
End Sub

Copy these two functions into a standard module.

Public Function Synch_PT_Filters(PT1 As PivotTable, PT2 As PivotTable, _
    sField As String)
    Dim sVisibleItems() As String
    Dim pviItem As PivotItem
    Dim i As Long
    
    '---make array of visible items in PT1
    With PT1.PivotFields(sField)
        If .Orientation = xlPageField And _
            .EnableMultiplePageItems = False Then
                ReDim sVisibleItems(1)
                sVisibleItems(0) = .CurrentPage
        Else
            For Each pviItem In .PivotItems
                If pviItem.Visible Then
                    i = i + 1
                    ReDim Preserve sVisibleItems(i)
                    sVisibleItems(i - 1) = pviItem
                End If
            Next
        End If
    End With
   
    '---make visible items in PT2 match PT1
    With PT2.PivotFields(sField)
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        If sVisibleItems(0) = "(All)" Then
            For i = 1 To .PivotItems.Count
                If Not .PivotItems(i).Visible Then _
                    .PivotItems(i).Visible = True
            Next i
        Else  'ensure 1 item visible
            .PivotItems(sVisibleItems(0)).Visible = True
            For i = 1 To .PivotItems.Count
                If .PivotItems(i).Visible <> _
                    isMember(.PivotItems(i), sVisibleItems) Then
                    .PivotItems(i).Visible = Not (.PivotItems(i).Visible)
                End If
            Next i
        End If
    End With
End Function

Public Function isMember(sItem As String, _
        ByRef sArr() As String) As Boolean
    Dim i As Long
    For i = LBound(sArr) To UBound(sArr)
        If sArr(i) = sItem Then
            isMember = True
            Exit Function
        End If
    Next i
    isMember = False
End Function

You might need more than a standard Vlookup formula in a column next the main Pivot table.
If you are going to have multiple countries selected, you'll probably need a formula that can find matches for two criteria.

Good luck!

 

[olympiac]

The code works perfectly when PT1 and PT2 are in the same sheet.
How can I modify it to make workable when PT2 is in another Sheet?

 

[olympiac]

Resolved!

Set PT2 = Sheets("Sheet2").PivotTables("PivotTable2")
Many thanks for your support and your suggestions.

 

[Jerry Sullivan]

When referencing the PivotTables from the Worksheet_Change event code, just include the sheet name for PT2 with the reference.

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet
        If Intersect(Target, .PivotTables("PivotTable1") _
            .TableRange2) Is Nothing Then Exit Sub
        
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Call Synch_PT_Filters( _
            PT1:=.PivotTables("PivotTable1"), _
            PT2:=Sheets("MyOtherSheet").PivotTables("PivotTable2"), _
            sField:="Country")
    End With
CleanUp:
    Application.EnableEvents = True
End Sub

 

[jholme9]

I know this is an old post, so hopefully I'm ok dragging it up (sorry if not...)

I'm trying to implement this code with 4 filter variables. So basically I have 4 "sField" variables. I'm sure the implementation isn't anything too complex, but I have close to zero VBA experience. Can anyone help with how I'd set this up so that 4 filters would sync on multiple pivot tables?

Thanks in advance!
John