pivot table question

olympiac · Oct 26, 2011

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 · Oct 27, 2011

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

6

Name 2

14

7

Name 3

16

8

Name 4

18

9

Name 5

20

10

Name 6

22

11

Name 7

24

12

Name 8

26

13

Name 9

28

14

Name 10

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

6

Name 2

14

7

Name 3

16

8

Name 4

18

9

Name 5

20

10

Name 6

22

11

Name 7

24

12

Name 8

13

Name 9

14

Name 10

15

Grand Total

126

Sheet

olympiac · Oct 27, 2011

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 · Oct 28, 2011

olympiac said:
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 · Oct 28, 2011

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 · Oct 31, 2011

olympiac said:
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

Code:

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.

Code:

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 · Nov 1, 2011

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 · Nov 1, 2011

Resolved!

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

Jerry Sullivan · Nov 1, 2011

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

Rich (BB code):

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 · Dec 1, 2011

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

pivot table question

olympiac

Board Regular

Excel Facts

Jerry Sullivan

MrExcel MVP

olympiac

Board Regular

Jerry Sullivan

MrExcel MVP

olympiac

Board Regular

Jerry Sullivan

MrExcel MVP

olympiac

Board Regular

olympiac

Board Regular

Jerry Sullivan

MrExcel MVP

jholme9

New Member

Similar threads

Forum statistics

Share this page

pivot table question

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock