Not able to create pivot

[abra]

Hello Everyone!

I took the help of few forum but did not get answer. can anybody help me
In the attached sheet, I want a pivot which display top 3 variables with highest number of records (Var1_ERR to Var5_ERR) based on the sector, report type etc.
I am not able to create the pivot to arrange the data properly. can anyone help
e.g. Suppose Question is --> display top 3 variables with number of records from the master sheet
My pivot should display like below
Var3_ERR 596
Var1_ERR 261
Var4_ERR 242
Advance thanks for your help

 

[Luke M]

Your raw data is arranged incorrect for that type of analysis. I've re-arranged the Var columns, and this lets the PivotTable be created as desired.

 

[abra]

Thanks Luke for your great help. As I am very new, could you please describe in detail how you arrange the data.
I would like to learn as I need to calculate for 1 lakh of records with more than 100 stat methods

Thanks in advance

 

[Luke M]

I used this macro to re-arrange the data. Might be able to use it "as-is", but if not, there's only a few things to tweak.

Sub RearrangeData()
Dim lastRow As Long, outputRow As Long
Dim varValue As Integer
Dim varHeader As String
Dim firstSet As Range
Dim DataRange As Range

'Determine where our data is
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set DataRange = .Range("A2:J" & lastRow - 1)
End With

Application.ScreenUpdating = False
outputRow = 2
'Prepare the output
For Each r In DataRange.Rows
    Set firstSet = r.Resize(1, 5)
    For i = 6 To 10
        varValue = r.Cells(i).Value
        varHeader = ActiveSheet.Cells(1, i + 1).Value
       
        firstSet.Copy Cells(outputRow, "N")
        Cells(outputRow, "S").Value = varHeader
        Cells(outputRow, "T").Value = varValue
       
        outputRow = outputRow + 1
    Next i
Next r

Application.ScreenUpdating = True
       
End Sub

 

[abra]

Thanks Luke for providing the solution

 

[Carolinejames]

Hi,

This is my answer.