Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Pivot Table - Totals Aren't Working

Posted on 2011-03-25
13
Medium Priority
?
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

715 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