Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need help creating a formula to summarize data!
Hello kind excel guru's!
I am trying to summarize data a receive from another program that exports to Microsoft excel. The company I work for has about 150 employees, the program that tracks their hours exports the information into the format on sheet one of the following workbook: http://www.savefile.com/files/4788638 I want to get an easy to implement function that will extract the number of hours per employee per day which is located in the cell to the right of the cell labeled "Total Hours" which is on a row with the date that the number of hours corresponds with. The problem I'm having is that each employee logs in and out of our phone system (the phone system is where the program that tracks hours gets its data) a varrying number of times per day. Also if an employee doesn't come in during a day then there won't be any data. Can anyone think of a way that I can do this? A macro might be more appropriate, any suggestions would help! |
#2
|
|||
|
|||
Need help creating a formula to summarize data!
My first recommendation is to ask the ACD support group to provide you
with a proper report instead of that one, with the following columns and values in every row (like rows of a database) - Agent name, agent number, date, sign on time, sign off time, total hours, etc. etc., just like a data base - one row per agent per day, with the various breakdowns and other attributes of the day in the columns. Processing that report would be easy. Frankly, the ACD should provide the report you need. What you have is one ugly report! Nevertheless, failing that, try this - it worked for me, with both missing employees and missing dates within employee: Insert two additonal rows in sheet 2 under the employee names and above the dates. Enter the following formulas: B2: =MATCH(B1,Sheet1!$B:$B,0) B3: =IF(ISNA(B2),IF(ISBLANK(C3),65000,C3),B2) Copy these across to the last employees column. Ensure the column to the right of the last employee is blank. Enter in B4 =IF(ISNA(B$2),"",IF(ISNA(MATCH($A4,OFFSET(Sheet1!$ A$1,B$3-1,0,C$3-B$3),0)),"",TIMEVALUE(INDEX(OFFSET(Sheet1!$G$1,B$3-1,0,C$3-B$3),MATCH($A4,OFFSET(Sheet1!$A$1,B$3-1,0,C$3-B$3),0))))) Copy across to last employee and down to to last date. This may need refinement. It is not pretty! I noticed that the total time I got for Employee1 is 84:59:21, which is different from what the report says in Logged In Time (84:22:37), but my total does equal the total of the "Total Times"for each date for Employee1. |
#3
|
|||
|
|||
Need help creating a formula to summarize data!
OOOPS! forgot to provide you with one necessary detail ...
In the column immediately to the right of the last employee on sheet 2, in row 3, put a large number that is guaranteed to be greater than the number of lines in the report, like say 65000. I know this could be done more neatly, but if this method works, why not go with it. DOR |
#4
|
|||
|
|||
Need help creating a formula to summarize data!
Applying essentially the same set of formulas as in the earlier post (with
some amendments to suit your actual layout / data) Sample with implemented construct at: http://www.savefile.com/files/6298881 Summarizing_Data_Dorn_wks_08112005.xls In Sheet1 ------------ Using 3 empty cols to the right of the data Put In J1: =IF(A1="Agent:","x","") In K1: =COUNTIF($J$1:J1,"x") In L1: =INDEX(B:B,MATCH(K1,K:K,0)) Select J1:L1, copy down In Sheet2 ------------ Employee names are listed in B2 across "Dates" are listed in A2 down, e.g.: ----------------- Emp1 Emp2 Emp3 10/17/2005 10/19/2005 10/20/2005 etc Put in B2, and array-enter (press CTRL+SHIFT+ENTER): =IF(ISNA(MATCH($A2&"_"&B$1,Sheet1!$A$1:$A$300&"_"& Sheet1!$L$1:$L$300,0)),"", INDEX(Sheet1!$G:$G,MATCH($A2&"_"&B$1,Sheet1!$A$1:$ A$300&"_"&Sheet1!$L$1:$L$3 00,0))) (Adapt the ranges to suit the extent in Sheet1's col L) Copy B2 across & fill down to populate the grid This should suffice for the desired summary table visual, but do note that the "dates" in col A and the "times" extracted in the grid are all text (as per source data in Sheet1), so as it is, no further calcs on these dates / times can be done. If you need to do calcs on these, use Sheet3 which replicates this Sheet2 but houses formulas to convert the "times" to real times. Dates in col A is easily converted to real dates via Data Text to Columns. In Sheet3 ------------- To convert dates in col A ------------------------ Copy & paste over col A from Sheet2 Select col A Click Data Text to Columns Click Next Next In step 3 of the wizard, under "Column data format:" Check the "Date" button, select MDY from the droplist Click Finish For the "times" conversion ------------------------- Put in B2: =IF(Sheet2!B2="","",TIME(LEFT(Sheet2!B2,2),MID(She et2!B2,SEARCH(":",Sheet2!B 2)+1,2),RIGHT(Sheet2!B2,2))) Format B2 as Custom, Type: [h]:mm:ss Copy B2 across & fill down to populate Now we can sum the total times for each employee, etc -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dorn" wrote in message ... Hello kind excel guru's! I am trying to summarize data a receive from another program that exports to Microsoft excel. The company I work for has about 150 employees, the program that tracks their hours exports the information into the format on sheet one of the following workbook: http://www.savefile.com/files/4788638 I want to get an easy to implement function that will extract the number of hours per employee per day which is located in the cell to the right of the cell labeled "Total Hours" which is on a row with the date that the number of hours corresponds with. The problem I'm having is that each employee logs in and out of our phone system (the phone system is where the program that tracks hours gets its data) a varrying number of times per day. Also if an employee doesn't come in during a day then there won't be any data. Can anyone think of a way that I can do this? A macro might be more appropriate, any suggestions would help! |
#5
|
|||
|
|||
Need help creating a formula to summarize data!
Don't know why it didn't work for me earlier <g, but here's a refinement
for the array formulae in Sheet2 which uses TIMEVALUE to convert the text "times" returned by the INDEX(...) to real times In Sheet2 ------------ Put instead in B2, and array-enter: =IF(ISNA(MATCH($A2&"_"&B$1,Sheet1!$A$1:$A$300&"_"& Sheet1!$L$1:$L$300,0)),"", TIMEVALUE(INDEX(Sheet1!$G:$G,MATCH($A2&"_"&B$1,She et1!$A$1:$A$300&"_"&Sheet1 !$L$1:$L$300,0)))) Format B2 as Custom, Type: [h]:mm:ss then copy B2 across & fill down to populate the grid, as before Now you can sum the total time for each employee in row11, e.g. in B11: =SUM(B2:B10) Leave the text "dates" in col A as it is, otherwise the matching with the source data in Sheet1 won't work (Sheet3 could hence effectively be dispensed with) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
|
|||
|
|||
Need help creating a formula to summarize data!
FWIW the test file with my implementation can be found at
http://www.savefile.com/files.php?fid=7155399 HTH |
#7
|
|||
|
|||
Need help creating a formula to summarize data!
I've run into a little problem, when an employee didn't work a day there is
nothing listed on the spreadsheet with the source data, so when the formula looks for the date it goes down the list to the next employee and enters the hours that they worked on that date. Is there anyway to stop this from happening? "DOR" wrote: OOOPS! forgot to provide you with one necessary detail ... In the column immediately to the right of the last employee on sheet 2, in row 3, put a large number that is guaranteed to be greater than the number of lines in the report, like say 65000. I know this could be done more neatly, but if this method works, why not go with it. DOR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
How do I get a formula to use data from a fixed location(NOT vicev | Excel Discussion (Misc queries) | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
formula to determine the first column containing any data | Excel Worksheet Functions |