List Vs Table

In this instance, an I Get It! client had to consolidate the weekly reports of 70-90 field representatives into a list in a master workbook, generate summary reports, and then upload the data into a centralized database.

Not only was the data consolidation taking up too much of the client’s time, but the real power of the information – helping facilitate business decisions – was not being realized. The client realized that if they could reduce the time required to consolidate data, more time would be available for analyzing the information and making decisions.

I Get It! helped this client find the perfect balance of training and custom application development that helped them speed up the data consolidation process and focus more on the real analysis that needed to be done.

By automating this once grueling task, the client got a promotion from robot to analyst. Remember, people who facilitate critical business decisions are a lot more valuable than those that merely copy and paste. If you sometimes feel that your job could be done by a robot, let I Get It! Development help you get a promotion!

Let’s look at a typical example of a weekly field report for this client. The report below is easy for the field staff to fill out and easy for a human to read, but it’s a terrible format for consolidation or a database upload!

We can’t use a PivotTable to solve this problem; that converts a list of data into a report like the one above. What we want is the opposite conversion – what we call the Un-PivotTable.
The analyst was trained in the Microsoft Excel features described in this article. Using these, the process was shortened from a grueling 4-6 hours to a relatively painless (though still really boring) one hour. Training was obviously effective, but pure training wasn't enough in this case. I Get It! recommended the use of a piece of custom software to eliminate the drudgery completely and free up even more time for business analysis.  This completely automated solution shortened the process to about 23 seconds!
We’ve placed facsimile reports in a zip file on our web site if you’d like to try your hand at consolidating the data. You can try both our tips for manually working on the spreadsheet and the included macros for automating the task.

The Challenge: Too many reports, too little time!

The field reps are typically without internet access and don’t want to enter data once into Excel and then again into a web application. The headquarters staff and field personnel have compromised on the above report which must be emailed to the analyst every Monday morning by 10am Pacific time.

In our case study, there are 11 different types of services performed for the customer. Some services are always included in the cost of the product, others are included in the various service packages. For instance, a customer who purchased the Bronze package gets Services 1 through 5 without additional cost, but Services 6-11 are billable by the hour in half-hour increments.

The analyst must prepare a report showing the total hours of service performed as well as a breakdown of hours of service for each of the 11 services performed. Further, the report must have the total hours of service performed broken down by the service package purchased (if any). Finally, a breakdown by service package and service performed is created. Management has always requested trend reports showing the period-to-period change in services and the billable hours vs. included hours of service but this report has never been created due to time constraints. A large number of other reports are desired but considered infeasible.

See how we addressed the problem(s) using: