Solved

# Timesheet Analysis -- Quickly add up times for individuals

Posted on 2014-09-19
94 Views
I am using Excel 2013 ... vba

I have uploaded a sheet. It has two columns with headers "Name" and "Hours".

There are two named ranges...

The range "Name" has duplicate entries.   Each entry has an entry in the "hours" column.

I would like a lightning fast analysis.  I need the total hours for each "Name" entry.   Ideally, a 2 dimensional array would be great.

I would prefer it in VBA, but open to suggestions.

At first, this problem seemed easy.   It's not  so easy!!
Accumulated-Time.xlsx
0
Question by:peispud

LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40332404
Why not do it as a PivotTable? Fairly easy (apart from needing headers), and the user can edit it if necessary.

Otherwise:

Columns("A:A").Select
Selection.Copy
Columns("D:D").Select
ActiveSheet.Paste
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("D:D")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
introw = 2
Do
Cells(introw, 5).Formula = "=SUMIF(A:A,D" & introw & ",B:B)"
introw = introw + 1
Loop Until Cells(introw, 4) = ""
0

LVL 32

Expert Comment

ID: 40332548
Assuming the columns are sorted by name, you can use the Subtotal routine to add subtotals at each change. Again, this will need headers.

If you have a fixed list of names, you can copy the list to a separate list with single entry per person and then use the SUMIF function on a separate sheet.

Assuming data on sheet 1 columns A & B and formula on sheet2 (or elsewhere):

=SUMIF(Sheet1!A:A,A1,Sheet1B:B)

where A1 contains the name to be summarised, copied down for the complete list.

This will be as quick as clicking to the next tab as this will update with each recalculation. Additional names will just need adding to bottom of list and formula copied down to accommodate.

Thanks
Rob H
0

Author Closing Comment

ID: 40333303
Thank you both for the answers.  My question was answered quickly and correctly so this question should be closed.

I will be re-asking the question again though.    I've recently discovered the  "scripting dictionary" object and hope to integrate it into the solution.

Thank you.
0

## Featured Post

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.