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!

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.

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

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(Sheet2!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" <Dorn@discussions.microsoft.com> wrote in message
news:E555AD74-9B9B-4F05-81E6-DC092F661E43@microsoft.com...
> 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!
>
>
>
>

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,Sheet1!$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
--

FWIW the test file with my implementation can be found at

http://www.savefile.com/files.php?fid=7155399

HTH

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
>
>