• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Not able to create pivot


New Member
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

Excel Ninja
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.



New Member
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

Excel Ninja
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