Better Output Method Help

[GregStewartPTC]

Good Morning

I am attempting to help a colleague with a report for work. I'm attaching a file with the data as it is exported from our system as well as after making some slight changes to simplify. What we are looking for is a way to calculate productive vs non productive hours per employee. The problem I'm running into is that the report is different every week based on the non productive codes that are used. Some weeks some codes are used, some are not, some are used more than others etc etc - so each week there are a different number of rows for each employee. I need a formula or macro that can be ran manually that will calculate and output the productive vs non productive hours. Some details regarding the attached spreadsheet:
Tab 1: Raw Data - this is the data in it's raw form when it is exported from our Cisco system. Important columns are - The first column which contains employee names, the fourth column which contains telephone activity codes, and the fifth column which contains the duration for each preceding code.
Tab 2: Formatted Data - this is the data after a macro is ran to simplify the reason codes from column four. It is simply changing the verbiage from the actual activity code to Productive or Non-Productive. I thought that this would be the best way to accomplish the goal as we have several available reason codes that employees can use.

What we would like to see is a macro/formula that can be manually ran and will output something in the format of:
Bunny, Bugs Productive - h:mm:ss
Bunny, Bugs Non-Productive - h:mm:ss
Either on the same worksheet or on a new tab would be fine as we're just using that data in a pivot table for a different workbook.
The actual report that we export has some 1-250 employees and their activities listed. I just attached a simple example, but can upload a more detailed report if necessary.

Any help is much appreciated. Thank you!

[JeteMc]

If I understand correctly then I would think that a pivot table would be the easiest way to do what you want. First thing would be to remove the merge and center formatting from column A of the formatted reason sheet. Next fill in the resulting blank spaces, the linked web site has a short video that shows a simple way to do that quickly (fewer than 6 key strokes and a right mouse drag).
After that it is just a matter of making the pivot table and using the tabular form of the report layout, adjusting the field settings > layout and print > Repeat item labels (Agent Names), filter the reason code to remove blanks, change count of duration to sum, and format the sum of duration to the fourth option under time (h:mm:ss;@)
Let us know if you have any questions.

[dflak]

You have to love how IT tries to help you out by providing data in human-readable form that is totally useless for data analysis. This is obviously either (A) a copy of the real pivot table or (B) the output of an Business Intelligence program.

I don't know how much "pull" you have with your IT department, but if you can convince them to provide the data in the format suggested by JeteMc, you'll be well ahead of the game. What you are asking for is Row Headers Repeat whether it's a pivot table or BI output, the terminology is the same.

The steps suggested by JeteMc can be automated by VBA if this is a recurring report. What this will do is convert the data to a normalized form that a pivot table could slice and dice anyway you want. The sad part is that the normalized data probably exists in some data table in exactly the format you need it.

[GregStewartPTC]

This works perfectly for this report. Thanks for getting back to me so quickly and providing the example. I'm marking this as closed now. +rep

[GregStewartPTC]

You have to love how IT tries to help you out by providing data in human-readable form that is totally useless for data analysis. This is obviously either (A) a copy of the real pivot table or (B) the output of an Business Intelligence program.

I don't know how much "pull" you have with your IT department, but if you can convince them to provide the data in the format suggested by JeteMc, you'll be well ahead of the game. What you are asking for is Row Headers Repeat whether it's a pivot table or BI output, the terminology is the same.

The steps suggested by JeteMc can be automated by VBA if this is a recurring report. What this will do is convert the data to a normalized form that a pivot table could slice and dice anyway you want. The sad part is that the normalized data probably exists in some data table in exactly the format you need it.

You're absolutely right! It is an output of a BI program, and if IT wanted they could probably provide me a more usable form of this data. Sadly, I don't foresee that happening anytime soon, so thank goodness for excelforum lol.

Thanks again for your help everyone!

[dflak]

I've worked with a BI Program to go from the format you are getting to one you are needing is a matter of clicking a couple of buttons. It's not a major coding development. In fact there's no "real" coding at all. The main issue would be political: will other users for the same report go along with the new format?

Are you interested in a VB solution that emulates JeteMc's suggestion?

[JeteMc]

You're Welcome. Thank You for the feedback and marking the thread as 'Solved'. I hope that you have a blessed day.