Getting average from information in 2 separate pivot tables question

[Anonmau5]

Hello Excel Community


I have a problem I've been working on for about 4 hours a night for this past entire week and I can't figure it out. I would greatly appreciate any help that will lead me in the right direction to getting this solved so I can go back to sleeping.


The situation:
I have two pivot tables that pull data from two sheets in the workbook.


- The first pivot table "A" shows an average of the persons closed cases per day for each person.
- The second pivot table "B" shows the amount of hours the person worked each day.


The information in sheet 2 for pivot table "A" has a list of all the cases each person closed that day and the chart is set to count the total for the day and display it.


The information for pivot table "B" has a list of all the punch "in/out" times and displays the sum of those duration.


What I am trying to do is have a pivot table or a very low maintenance chart that will take the total cases closed for the person that day and divide it by the hours they worked and show their "average" for that day. I have tried to find a way to use the data from those pivot tables and the only way I have found was to manually add each persons data off the pivot tables to the manual chart. This is not an option as there is too much to track like this manually.


I also tried creating a pivot table using the information from the two sheets that provide the data for pivot table "A" & "B" however when I do that you loose all the unique field labels at the top in the data sheets and only have about 4 options to sort and work with on the pivot table bar. If I could get all the different options on the right to come up with it pulling from one sheet when I set it up for two I could probably make it work. Then it would just be a matter of listing both totals next to each other and doing a calculated field to divide the one by the other.


Again I greatly appreciate help from anybody that can help restore my sanity! :P


- Brian

 

[konew1]

you could use a column to the right of pivot table using vlookup to bring over the hours worked from the other pivot. When you create the vlookup formula type the cell address that holds the first name in the pivot, (don't use the mouse to select the cell)

 

[Anonmau5]

Thank you for the reply. I really do appreciate any help with this.

I could do a vlookup formula in a column to the right however the table that I need would have to show month to date records. So for this I would have to do a vlookup formula for each person for each date of the month.

If there are any other ideas that could produce these kind of results without so much manual up startup/upkeep I would appreciate it. Right now it's only about 7 people but it could grow to about 15 and for each date it would start to get very tedious.

Thanks!

- Mau5

 

[konew1]

I don't think it can be done easily. Maybe someone can help with VBA

 

[Anonmau5]

I'm open for any suggestions. I'll try and do some research to see if it can maybe be done with VBA. In the meantime if anybody has a suggestions or a resource that might help get this accomplished I'm open for all ideas!

Thanks again

 

[konew1]

You need describe the original data better.
By formula:
You should be able to easily maintain a list of names on column A and days in the month in row 1
in B2 a formula to get the cases closed for the person that day and divide by the number of hours worked.
You asked for cases/hours. That will return zero for someone who worked and didn't close any.
Copy the formula down and across as far as required.
the formula would be structured something like =index(cases_closed,match(name),match(date))/index(timeworked,match(name),match(date))
You need to describe your data better to get useful help.

Anyone else is welcome to respond - I have to go

 

[Jerry Sullivan]

Brian, You could probably accomplish your objective using an SQL query that uses the two source data tables directly to calculate the cases/hour for each name.

If you'll post a small screen shot showing the headers and a few rows of data from both tables, I'll provide instructions on how to set that up.

 

[Anonmau5]

Thank you for the responses.

The original data is pulled and all closed cases are listed for the month as seen in the picture below. (Had to change some of the title names and data listed but is all in same format)

From this the first pivot table lists each person and then lists the total number of cases closed for that month by counting each of the dates in the "close time" field.

The other data source is the clock in/out times:

Here the data for clock in/out time is listed for each employee (month to date) then moves to the next person. Here the employee name is listed Last, First (Either middle initial or full middle name). The 2nd pivot chart that shows total hours worked per day lists each persons name and then adds up the "total hours" for the "date" that they worked.

Also each of these are on their own worksheet within the same excel doc.

 

[Jerry Sullivan]

To use a query approach, the tables can't have any fields without headers (like Cell E1 of the clock in/out table).

Also, it will be much simpler if both tables have a field that has the same unique identifiers for the employee's names. Does the closed cases table have an employee ID field? If not, can the employee names as formatted in the ~Case Owner field be added to the clock in/out table?

The alternative would be to have a 3rd table that maps Employee ID and Case Owner names that could be used by the query.

 

[Anonmau5]

Unfortunately the closed cases table doesn't have an employee ID field. Both of the tables just pull "raw data" that can't be changed. I could add another header to the clock in/out table so that I could convert the names listed there to the same format as the closed cases table. Would still have the middle initial or full middle name listed as it comes from HR though.

E1 isn't actually blank sorry. I meant to cut that out as it's not related to anything.

Thanks again for looking at this with me. What I thought was going to be simple turned into a real pain in the side.