Fast way to determine filtered values in a Pivot Table

Question

I need to check if a host of pivot tables are correctly filtering out the right items. Right now I am scrolling down and eye-balling to see if certain entries are excluded and nothing else. But each of my fields have 10,000+ items and it's taking forever and I am worried that I might miss something. Is there some way that I can get excel to simply list the values excluded in a Pivot table?

Answer 1

A few years later, but: In VBA, the PivotField object has members .HiddenItems and .VisibleItems in addition to the basic .PivotItems. Looping through .PivotItems and checking for .visible = True is slow, but simply converting .HiddenItems or .VisibleItems into an array is fast.

Here is a sample function for hidden items. If you replace "hidden" with "visible" throughout this function, it will give the visible items.

Function PivotFieldHiddenItems(myField As PivotField) As Variant
    
    Dim i As Long
    Dim hiddenItems As PivotItems
    Dim returnArray() As Variant
    
    Set hiddenItems = myField.HiddenItems

    If hiddenItems.Count = 0 Then
        'If no hidden items, return empty array
        PivotFieldHiddenItems = Array()
    Else
        'Send the hidden items to a 1 dimensional array
        ReDim returnArray(hiddenItems.Count - 1)
        For i = 0 To hiddenItems.Count - 1
            returnArray(i) = hiddenItems(i + 1)
        Next
        PivotFieldHiddenItems = returnArray
    End If

    'To demo, show results in message box
    MsgBox Join(PivotFieldHiddenItems, "," & vbLf), , "Hidden Fields: " &     
    myField.Name

End Function

Answer 2

If you have access to the original data table, I believe the simplest method would be to add an additional column to the right, which will hold a value indicating presence in the pivot table. Something like [assuming the unique ID is in A1, and the pivot table is in column A of sheet2], this cell would be in eg e1, and copied down]:

=ISERROR(MATCH(A1,'Sheet2'!A:A,0))

This will now show TRUE where there was no match found on the pivot table, and FALSE otherwise.

Now you could have an additional pivot table which picks up the expanded original data set, filtered to show only items which have TRUE in the new field.