Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Pivot Table - Totals Aren't Working

Posted on 2011-03-25
13
Medium Priority
?
283 Views
Last Modified: 2012-06-27
Attached is an Excel file with a macro that creates a Pivot table and the other file contains the Pivot table itself.  I was given this on a previous question, but didn't notice that the totals don't work.  After trying some things manually, it said I couldn't change this part of the pivot table.  I can make it work if I turn on the macro recorder and create the pivot table from scratch, but then it wouldn't include the start and end times like I want.  Thank you.
logonEvents.csv
LogonReport.xls
LogonReportStarter2.xls
0
Comment
Question by:rvfowler2
  • 6
  • 6
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35229959
Could you clarify what exactly is wrong with the pivot table as it is in the workbook you provided?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232464
The totals are wrong.  They don't add up to the sum of the daily hours the person put in for the week.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35232525
That's just a formatting issue - you need to format the Hours field to display as [hh]:mm rather than hh:mm
HTH
Rory
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35232539
In other words, change this line:
.PivotFields("Total Hours").NumberFormat = "hh:mm"

Open in new window

to this:
.PivotFields("Total Hours").NumberFormat = "[hh]:mm"

Open in new window

0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 35232613
Thanks, that worked.  Missed an easy one.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232682
Actually, it fixed that issue, but if you do it, I have tons of other issues.  Whoever helped me before totally messed up what I had that was working.  All I asked for in a previous question was to add a start and ending time.  I think I'm going to manually record the process and start over.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35232911
If you are referring to this PAQ: http:/Q_26831553.html then that was me. Since you gave no feedback or explanation of a problem then, or now, not much I can do about it. (I don't really see how formatting the output causes you a problem - the values are still the same).
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232997
Just make the change as you indicated top [hh], then run the report.  I've included the data file that I cleaned up a bit this a.m.  I'm still looking through it, but wondering if your code simply calcs the elapsed time between beginning and end times rather than add up the elapsed times because sometimes people log in and out a couple of times per day.

 Copy-of-logonEvents.csv
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35233175
The code adds up the same formulas you were using originally. Can you give me an example where it is wrong? as far as I can see, the formulas total to 606:57 hours of work, as does the pivot table.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35233371
Yes, sorry, for the most part, it seems that I need to so some data cleanup.  It would be nice if the code could account for orphaned logon and logoffs.  I did run the old code which I am copying below, and it came out just a bit better than your code in producing totals closer to accurate (compare Randy to Randy), but there were still problems.  See attached report as the result of the old code.
Sub FormatLogonReport_OLDER()
'
    ChDir "R:\LogonScript"
    Workbooks.Open Filename:="R:\LogonScript\Copy of logonEvents.csv"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="R:\LogonScript\LogonReport.xls", FileFormat _
        :=xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
    Application.DisplayAlerts = True

'ENTER HEADERS
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Rows("1:1").Select
    Selection.Font.Bold = True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Userid"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Workstation"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Day"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Event"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Hours"
  
    Cells.Select
    Cells.EntireColumn.AutoFit

    Columns("A:G").Select
    Range("G1").Activate
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

'FORMAT COLUMN AND ENTER HOURS CALC
    Columns("G:G").Select
    Selection.NumberFormat = "h:mm;@"
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "f").End(xlUp).Row
    Dim rng As Range, cell As Range
    Set rng = Range("F2:F" & lrow)
    For Each cell In rng
        If UCase(cell.Value) = "LOGOFF" And UCase(cell.Offset(-1, 0)) = "LOGON" _
            Then cell.Offset(0, 1).Formula = "=(d" & cell.Row & "+E" & cell.Row & ")-(d" & cell.Row - 1 & "+E" & cell.Row - 1 & ")"
    Next cell
    
    'INSERT THE PIVOT TABLE
    Windows("LogonReport.xls").Activate
    Cells.Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Copy of logonEvents'!$A:$G").CreatePivotTable TableDestination:= _
        "'[LogonReport.xls]Copy of logonEvents'!C9:C14", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
        
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Userid", _
        "Day")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Hours")
        .Orientation = xlDataField
        .Caption = "Sum of Hrs Worked"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Userid")
        .PivotItems("administrator").Visible = False
        '.PivotItems("temp").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Day")
        .PivotItems("(blank)").Visible = False
    End With
' FINAL FORMATTTING
    Columns("K:K").Select
    Selection.NumberFormat = "[h]:mm"
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    Columns("I:I").Select
    Selection.Font.Bold = True
    Columns("I:K").EntireColumn.AutoFit
    Range("J2:K2").Select
    Selection.Font.Bold = True
    ActiveWorkbook.Save
    
    'Windows("LogonReportStarter.xls").Activate
    'ActiveWindow.Close
    
End Sub

Open in new window

LogonReport2.xls
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35233437
Small point, but the only difference there is in the sorting, which is your code, not mine - I only added the columns to the pivot table. :)
What do you want to do with orphaned items? Ignore them? If so, you could add a check to see if the Day (or date) is the same as the row above in addition to checking for LOGON followed by LOGOFF?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35233696
Yes, I think it's best to ignore the orphaned items.  If you only added the start and end times columns, not sure why the totals of the two reports would be different in some places.  As I mentioned you could compare Randy with Randy on the two reports.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question