Designing Methodology

I appear to be going through a stretch where whatever I do, I’m not happy with: either it looks or works awful or just doesn’t plain work or it just takes too long. Any idea I have just doesn’t come like the way I imagine it.

Anyway I still have to push on.

I’m currently working on a project helping someone develop her reports so that instead of taking 6 hours, it takes less than an hour. I thought it would be something as simple as re-using a previous excel program to get the job done but it turns out her process is a wee bit more complicated. Well, maybe not a wee bit, but a lot more complicated. Whereas in the prior project,

the report had a consistent structural layout where all reports were the same, this new project has about 5 or 6 different structural layout for 8 reports in each reporting session, so it’s not a simple matter of re-using the old macro. Then on top of that, new columns with calculations have to be added and not all reports have consistent calculation methodology.

And the final insult on top of this complicated mess is the allocation of people which could differ from one reporting session to another. So in this project the repeating actions in producing the 8 reports got confusing.

I got a working version of the program that reduces the time spent developing the report from 6 hours to 5 minutes plus however long it takes to make minor adjustments and to cross check the totals. In the working version I left out the people part and the cross checking part (actually, most of that was done but a few minor things left out). And I just finished (like yesterday) these two elements for the next reporting session.

How did I go about doing this?

1.      Well, I first had a meeting with the lady who has to produce these reports every two weeks to see what she does. Since the report normally takes about 6 hours, we didn’t go through the actual process of doing the report; we just talked about the high level actions which I suspected left off a lot of stuff. (And it was later confirmed that there were a few other things going on.)

During the meeting I asked her for the original data she receives every two weeks and for the final outcome, so I would be working with real data and know what I was shooting for. One set of data is not enough – you really have to use a series of data over time so see how the number formats change or remain the same. Also weird things could pop up from time to time that might not be captured by that single data set. One question I asked was if she thought the data layouts and naming remained the same each reporting system. I wanted to see whether there was a level of consistency in the original data. Sometimes people are consistent and other times they are not.

2.      Once I have asked the questions (or watched the actual development of the report, if that is do-able), I then scrutinize the data and the end result to see what is being used and how. I consider if a pivot table can be used, or if formulas would be better, or if a macro would do the job. Generally a pivot table would be the first consideration but for this project, the layout of the original data was not conducive to it and the addition of differing calculations would make the use of pivot tables difficult if not impossible. Also, I later found out that sometimes adjustments or additions to the data had to be made and using a pivot table might preclude the ability to add or change. Formulas might have worked in this instance and if I hadn’t gotten familiar with macro programming, I might have used that route but I feared that formulas would lead to a bulky, creaky, slow file. So I decided on re-using a macro and building upon it.

3.      Next, I looked for repeating elements that is used by every report. What are the common elements for each report that I can re-use? I compared each reports’ headers to each other and to the original data. From that review alone I knew that while there were some common actions, there would be some other differing actions. So I decided, as an initial pass, to develop a macro programming for each separate report: create the first macro for the first report, copy it and then change and/or add to the macro to suit the second report. And then repeat for the 3rd, 4th and so on through to the 8th report. So I basically have a major macro to initiate variables, to clear out old data, to pull in new data and then 8 mini repeating sections for 8 reports, all in one main macro. This is where I’m at now. But I now can see what the repeating elements are and where each report diverges because I literally programmed the actions for each report.

4.      From these repeating sections for the 8 reports, I then developed those common actions into sub-modules to be called by the main macro. I still haven’t collapsed 8 repeating sections into one looping sections because I still have to deal with the non-repeating actions: namely the people. But I have created sub-modules of the repeating actions common to all 8 reports…well, more or less.

5.      Before doing the final looping action in the main macro, I decided to look at the handling of certain people. Sometimes the person is divided up into two reports or different sections in a single report or moved from one report to another report. Or the numbers just completely change. The lady told me who the individuals are and they remain the same for each reporting session, until they leave the company. In each of the 8 reports, there are about 9 different steps, 7 of which are the same for all 8 reports and 2 steps that are different from report to report, due to the people part. This is where I ran into a lot of problems. Some people’s handling were not handled the same from reporting session to another where others had the same actions or calculations from session to session. So, for the people part of the report, I decided to make the macro do a first stab at what the people allocation should be but then report at the front of the file what the reports are showing for that person as well as the original data. This front of the file is like a mini-report for the lady so she could see how certain people were allocated in the 8 reports. And, each of the 8 reports had a direct link back to the front of the file, so that if the lady needed to change the numbers in the front, she could do so and the change would flow back to the reports. This innovation was just completed on Friday, so hopefully this will help her out.

6.      Finally, I worked on the cross check tab and that was also just finished on Friday. There may be more cross checking but I haven’t thought of them yet.

Here’s what I have left to do: adding the looping action in the main macro to replace the 8 sections for each of the 8 reports. I want to loop through the reports rather than have 8 sections within the main macro. The two steps that differs from report to report that needs thinking though.

I also need to work on what to do when: properties are gone (adding properties are already taken care of); when those certain people are gone; when there are new special people; when new reports are required. Right now, the program is set up that each change in those special people requires a manual change in the program or each change in the report layout requires a new report section in the macro. I want to see if I design the macro so that changing, adding, or deleting special people or reports can be “automated” to some extent. That’s probably going to be a longer term thinking and will be done last, if it’s ever done.

But for right now, the main portions of the program is done and will reduce the time from 6 hours to around 5 minutes of generation plus 45 minutes (?)(I’m guessing here) of review. You can’t beat that.