Condense Multi-layer Report

[leatherhen99]

Happy Friday All!!!

I'm looking to take a canned report and create a quick timesheet that does all the calculations for me. The system is what it is (we just installed it), so I have to make lemonade out of the lemons that I have...and I'm at a loss in how to simplify the report. As a side note, I'm not the end-user, so it has to be something that will get them what they need with zero intervention from the end-user...other than to input the information into our timekeeping system.

A report generates into an excel report, and I've create formulas to get the information into a pretty table so that it calculates for the timekeepers - instead of having to do math based on what they're seeing.
An agent's daily time can fit into 3 categories... all REG time, all DIFF time or time can be part REG and part DIFF in the same segment. The following is one day for one employee...

I have multiple row criteria, and I can't even get it to pull the way that i need it to... if it has to be two columns, this is the way that i need it to look...

I would like to pivot this into one column so that DIFF and REG are in one date column...instead of two... The only option that i can think of, is to filter the DIFF column, make a copy of it, identify the new rows in the D column, and add that to the pivot table so they are all in the same column. This is a mockup, but this is how I want it to look... right now, i'm creating two different pivot tables, and then merging them into another tab...

Is there another option that I'm missing?
If there's an easy way to do it with VBA, I'm up for it so that it's automated.

Thanks!!!
Heather

 

[Rijnsent]

Hi Heather,
you could use your source data as the source of a pivot table. And with Pivot tables, you can calculate fields (by e.g. adding up Diff & Reg), see this tutorial: Excel Pivot Table Calculated Item
And for the first field "EMPL1", I don't know where you get that from, is that somewhere in your source too?
Hope that helps a bit,
Koen

 

[leatherhen99]

Good morning,

The EMPL1 is the employee. I removed the name for security. I am pivoting the information twice right now. I do one pivot table to include REG, and one to include DIFF (those two pivot tables separately are built to include those lines that have both REG and DIFF), and then I copy and paste those two pivots into a third sheet so REG and DIFF are all within one table. I understand the calculated item, but I don't know what fields to pull into a pivot to separate the three correctly. Do I need a field that is common for all three in Rows (within the Pivot Table), and then somehow create a calculated field to pull in REG, DIFF or the combination of them both into the appropriate fields?

 

[Rijnsent]

Hi Heather,
I think I'm missing something. So the first of your screenshots is not the original data? If you'd happen to be able to post a small data sample (through e.g. a dropbox link) that would be most helpful.
Koen