CODEWITHSUNDEEP
vbaexcelpivot-table
Kalarian
Kalarian

Reputation: 5

How to get the highest number in a set data points for a subfield in a PivotTable?

In the attached screenshot below, I'm using x to get the 12.50% of "Short" in "First Grade." However, I'm looking to expand on this to find the largest value in the "First Grade" field and return the name. In "First Grade" for example it would be "Tall".

Sub PivotTest()

    Dim pvt As PivotTable: Set pvt = Sheets("Sheet2").PivotTables("PivotTable1")

    x = pvt.GetPivotData("Count Of Height", "Grade", "First Grade", "Height", pvt.PivotFields("First Grade").PivotItems(1)).Value

End Sub

PivotTable Screenshot

enter image description here

Upvotes: 0

Views: 110

Answers (2)

Pierre44
Pierre44

Reputation: 1741

I would offer a non VBA solution in a few steps: First switch the view of your pivot table from enter image description here

to

enter image description here

After that you can solve it with a max if and an index match with two criteria: In the example: 

{=MAX(IF(H3:H8="First Grade";J3:J8))}

=> This array formula gives you the max height for the first grade. Then with a double criteria index Match you can search for the max height of the first Grade: 

{=INDEX(H3:J8;MATCH(1;(H3:H8="First Grade")*(J3:J8=*Cell of the maxIf*);0);2)}

Or the whole formula together longer: 

{=INDEX(H3:J8;MATCH(1;(H3:H8="First Grade")*(J3:J8=MAX(IF(H3:H8="First Grade";J3:J8)));0);2)}

Works great on my Excel. I guess one could adapt this easily to VBA, but wouldn't be the cleanest.

Upvotes: 0

QHarr
QHarr

Reputation: 84465

So you can fiddle around with the following but it gives you a shape to work with. Apologies for any typos as am free typing this in as unable to paste at present.

The GetMaxInfo sub calls the function MaxHeightInfo, which takes the Grade as an argument, and returns an array containing the height description where the max count was found e.g. "Tall", and the count value itself. These are accessed by the (0) and (1) indices after the function call.

The function MaxHeightInfo uses the GetPivotData function to retrieve information for the specified Grade and sets maxCount to the highest value it finds. It stores the height description where this value is found in the maxHeight variable.

Option Explicit

Public Sub GetMaxInfo()

    Msgbox MaxHeightInfo("First Grade")(0)
    Msgbox MaxHeightInfo("First Grade")(1)

    Msgbox MaxHeightInfo("Second Grade")(0)
    Msgbox MaxHeightInfo("Second Grade")(1)

End Sub

Public Function MaxHeightInfo(ByVal myGrade As String) As Variant

    Dim myHeight()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet2") 'change as appropriate

    Dim pvt As PivotTable

    Set pvt = ws.PivotTables("PivotTable1")

    myHeight = Array("Short","Average","Tall")

    Dim currHeight As Long
    Dim maxCount As Double
    Dim maxHeight As String

    maxCount = 0
    maxHeight = vbNullString

    Dim testVal As Double

    For currHeight = LBound(myHeight) To UBound(myHeight)

        testVal = pvt.GetPivotData("Count of Height"),"Grade", myGrade,"Height", myHeight(currHeight)).Value

        If testVal > maxCount Then
            maxCount = testVal
            maxHeight = myHeight(currHeight)
        End If

    Next currHeight

    MaxHeightInfo = Array(maxHeight, maxCount)
End Function

Upvotes: 1

Related Questions