This is hard... well for me...Need help with a macro to summarize

[Dorn]

I've been trying to solve this problem with functions and I just can't get it
to work, I was wondering if any of you programmers out there would know an
easier way.

The problem:
My data comes in with tons of extra cells and is really hard to quickly use.

The solution:
I need to make a macro that will summarize the data into the format on
sheet2 of the following example workbook:

http://students.washington.edu/rdorn/ExcelProblem.xls

Sheet 1 shows how the data is sent to me.

The company I work for has about 150 employees so this is just a sample.
One of my big problems is that we are broken into teams and I can't export
the data for just one team. So I end up having to browse the entire exported
file to extract information for the people I want to see. Is there a way
that I can manually enter the name of the employee on sheet 2 and enter the
dates, and then just run a macro to do this for me? Any and all help would
be much appreciated.

When I tried to use functions to do this I kept running into problems
because the data isn't always in the same place (but its always referenced in
the same way to the word "Agent:" as shown in the above sample). Not to
mention if an employee didn't come in then there is no exported data so my
function would jump to the next employee and use their hours (which obviously
messes everything up).

Thanks,
Dorn

 

[Dorn]

FYI: the data I need for each employee under each date is the "total time"
which is located on the same row as the date and in column "G" of my sample.

 

[PY & Associates]

Where are the DATES in row 1 come from?
Try fill up row 2 to show the result you expect please.

 

[Bob Phillips]

Here is a UDF

Function GetTotals(Source As String, Resource As Range, MatchDate As Range)
Dim i As Long
Dim iLastrow As Long
Dim iStart As Long
Dim iEnd As Long
Dim tmp

iLastrow = Worksheets(Source).Cells(Worksheets(Source).Rows.Count,
"A").End(xlUp).Row
On Error Resume Next
iStart = Application.Match(Resource.Value,
Worksheets(Source).Range("B:B"), 0)
If iStart > 0 Then
iEnd = Application.Match("Agent:", Worksheets(Source).Range("A" &
iStart + 1 & ":A" & Rows.Count), 0) + iStart
If iEnd = 0 Then
iEnd = iLastrow
End If
On Error GoTo 0
For i = iStart To iEnd
If Worksheets(Source).Cells(i, "A").Value = MatchDate.Value Then
tmp = CDate(Worksheets(Source).Cells(i, "G").Value)
End If
Next i
End If
GetTotals = tmp

End Function

enter =GetTotals("Sheet1",$A2,B$1) in b2 and copy over

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dorn]

The dates in row one could be manually entered by me, but I'm assuming they
should be formatted as text to match the source data. As for an example of
what I expect in row 2, I'm having trouble with my ftp client so I'll try to
explain:

Under each date I need the total hours worked for that day, which is located
in the same row as the date I just need it to copy the contents of the cell
referenced by the date. Example would be cell G25 and for the columns on the
right that information can be found next to the names of the employees
example would be row 22 under the appropriate headings. Thanks for the help!

 

[PY & Associates]

Bob Phillips has given a working solution.
I trust you are happy with it.
For our curiosity, what do you expect in sheet2 columns K to N please?

 

[Dorn]

Unfortunately I haven't used user defined functions before (haha I'm pretty
sure this should be on the newbie discussion board) I tried to save it as a
macro and it gave me a "syntax error" and highlights the first row. As for
the columns k through n they reflect the employees status in our phone
system, logged in is self explanatory, acd means that they will be randomly
distributed phone calls of callers who call our sales teams non acd means
they will only receive calls intended only for them and dnd means they won't
receive calls. I was hoping to be able to pull the time for those categories
in the same format of the times pulled from the "total times" for each of
those dates. You guys are really helping me out here, thanks a bunch!

 

[PY & Associates]

We did not have any problem with Bob's function.
for your benefit, we converted this function as subroutine as follow. Tested
OK

Sub t() 'Function GetTotals(Source As String, Resource
As Range, MatchDate As Range)
Dim Source As String 'added
Dim Resource As Range 'added
Dim MatchDate As Range 'added
Dim i As Long
Dim iLastrow As Long
Dim iStart As Long
Dim iEnd As Long
Dim tmp

Source = "sheet1" 'added
Set Resource = Range("A2") 'added
Set MatchDate = Range("B1") 'added

iLastrow = Worksheets(Source).Cells(Worksheets(Source).Rows.Count,
"A").End(xlUp).Row
On Error Resume Next
iStart = Application.Match(Resource.Value,
Worksheets(Source).Range("B:B"), 0)
If iStart > 0 Then
iEnd = Application.Match("Agent:", Worksheets(Source).Range("A" &
iStart + 1 & ":A" & Rows.Count), 0) + iStart
If iEnd = 0 Then
iEnd = iLastrow
End If
On Error GoTo 0
For i = iStart To iEnd
If Worksheets(Source).Cells(i, "A").Value = MatchDate.Value Then
tmp = CDate(Worksheets(Source).Cells(i, "G").Value)
End If
Next i
End If
' GetTotals = tmp
Range("b2") = tmp 'added

End Sub

You have to adjust the code to suit your need. Currently it works for
range("B2") only