Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is hard... well for me...Need help with a macro to summarize
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is hard... well for me...Need help with a macro to summarize
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is hard... well for me...Need help with a macro to summarize
Where are the DATES in row 1 come from?
Try fill up row 2 to show the result you expect please. "Dorn" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is hard... well for me...Need help with a macro to summarize
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.C ount, "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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is hard... well for me...Need help with a macro to summarize
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" wrote: Where are the DATES in row 1 come from? Try fill up row 2 to show the result you expect please. "Dorn" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is hard... well for me...Need help with a macro to summarize
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" wrote in message ... 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" wrote: Where are the DATES in row 1 come from? Try fill up row 2 to show the result you expect please. "Dorn" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is hard... well for me...Need help with a macro to summar
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" wrote: 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" wrote in message ... 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" wrote: Where are the DATES in row 1 come from? Try fill up row 2 to show the result you expect please. "Dorn" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
This is hard... well for me...Need help with a macro to summar
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.C ount, "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 "Dorn" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: Where are the DATES in row 1 come from? Try fill up row 2 to show the result you expect please. "Dorn" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to summarize data | Excel Worksheet Functions | |||
Macro - Too hard for me!! | Excel Discussion (Misc queries) | |||
Macro -Far too Hard for me !! | Excel Worksheet Functions | |||
Macro - Too hard for me!! | Excel Discussion (Misc queries) | |||
Macro or VB Code to Summarize Info.from 4 workbooks | Excel Programming |