Visible Pivit Items in a Pivot Field

%3CLINGO-SUB%20id%3D%22lingo-sub-1713665%22%20slang%3D%22en-US%22%3EVisible%20Pivit%20Items%20in%20a%20Pivot%20Field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1713665%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20for%20an%20Excel%20formula%20that%20will%20return%20the%20list%20of%20Visible%20PivotItems%20in%20a%20specific%20PivotField.%20Any%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1713665%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1714107%22%20slang%3D%22en-US%22%3ERe%3A%20Visible%20Pivit%20Items%20in%20a%20Pivot%20Field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1714107%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606113%22%20target%3D%22_blank%22%3E%40Mgaill%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20custom%20VBA%20function%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20ListPvtItems(r%20As%20Range%2C%20Optional%20sep%20As%20String%20%3D%20%22%2C%20%22)%20As%20String%0A%20%20%20%20Dim%20pvf%20As%20PivotField%0A%20%20%20%20Dim%20s%20As%20String%0A%20%20%20%20Dim%20pvi%20As%20PivotItem%0A%20%20%20%20Set%20pvf%20%3D%20r.PivotField%0A%20%20%20%20For%20Each%20pvi%20In%20pvf.PivotItems%0A%20%20%20%20%20%20%20%20If%20pvi.Visible%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20s%20%3D%20s%20%26amp%3B%20sep%20%26amp%3B%20pvi.Name%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20pvi%0A%20%20%20%20If%20s%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20ListPvtItems%20%3D%20Mid(s%2C%20Len(sep)%20%2B%201)%0A%20%20%20%20End%20If%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20like%20this%20in%20a%20cell%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DListPvtItems(A3)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhere%20A3%20is%20a%20cell%20in%20the%20pivot%20field%20you%20want%20to%20list%20the%20visible%20items%20of.%20Or%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DListPvtItems(A3%2CCHAR(10))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eto%20list%20the%20items%20on%20separate%20lines%20(turn%20on%20Wrap%20Text%20for%20the%20cell%20with%20the%20formula)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am looking for an Excel formula that will return the list of Visible PivotItems in a specific PivotField. Any ideas?

2 Replies

@Mgaill 

Here is a custom VBA function:

Function ListPvtItems(r As Range, Optional sep As String = ", ") As String
    Dim pvf As PivotField
    Dim s As String
    Dim pvi As PivotItem
    Set pvf = r.PivotField
    For Each pvi In pvf.PivotItems
        If pvi.Visible Then
            s = s & sep & pvi.Name
        End If
    Next pvi
    If s <> "" Then
        ListPvtItems = Mid(s, Len(sep) + 1)
    End If
End Function

Use like this in a cell formula:

 

=ListPvtItems(A3)

 

where A3 is a cell in the pivot field you want to list the visible items of. Or

 

=ListPvtItems(A3,CHAR(10))

 

to list the items on separate lines (turn on Wrap Text for the cell with the formula)

@Hans Vogelaar 

Thanks a lot, it is working like this (without the "enf if"

Function ListPvtItems(r As Range, Optional sep As String = ", ") As String
' Custom VBA function to list Pivot list item in a Pivot Field:
Dim pvf As PivotField
Dim s As String
Dim pvi As PivotItem
Set pvf = r.PivotField
For Each pvi In pvf.PivotItems
If pvi.Visible Then s = s & sep & pvi.Name
Next pvi
If s <> "" Then ListPvtItems = Mid(s, Len(sep) + 1)
End Function