Listening to My Customers

We're on our third year with our automated operational reporting, which has been rolled out to both sides of the house. It kind of gives me a secret thrill to think that once a week, everyone comes in to work in the morning, opens up the file and lets the program do its magic. And if they have done what they needed to do during the past week, the report is done in about five minutes.

Now, I have handed over the consolidation of the reports, which is itself done by a program (of course! With around 70 files to collect, you need a program to do that, as well as a signal announcing when someone had a problem.) The cool thing is sometimes she (the new person doing the consolidation) has a suggestion for improvements or she tells me of a problem she has.

This past week she told me about an area where the managers consistently skip, probably because the instruction was not clear. So after talking a bit, I took her suggestion, worked on a solution and implemented it. We'll see if the change improves the situation over the next few weeks.

Another time, she complained about the number of managers failing to save their files with the correct filename, such that the consolidation macro would fail to pick up the files. When she made that complaint, I had just recently figured out a solution to my own problems: I was fixing a lot of filenames manually when I thought "Hey, there should be a way to write a macro to do this." So when she made that complaint, I told her I might have a way to fix it.

One filename rule was that the first three letters had to be all caps and the other rule was that there had to be spaces between hyphens in certain positions of the filename. These were the two rules consistently broken, so I developed a macro to address those rules. Then there were the outright misspellings of the business units. This problem was a bit trickier than the first two rules but I came upon a neat solution that I have been re-using in various permutations lately.

For the two business rules, the macro basically asks if all of the letters are in caps or asks if there are spaces between the hyphens. I won't go into the details of the solution because I think most people will think of some variation of the solution. But the misspellings problem is the more interesting problem because when you first think of it, you think that the number of misspelling permutations grows large and you can't possibly capture all of the variety of spellings. But one of the thing I have learned is that it turns out there is a limited number of misspelling - maybe up to 5 kinds of errors. So what I did was ask her, "Who consistently misspells and do they spell the main word correctly?" Usually, if there is a misspelling, it is because an extra space or character was added. I made a list of those who frequently misspell, along with the key name of the business unit (not the entire name but a single distinctive word that is spelled correctly most of the time) and the proper full spelling for the business unit. To repeat, this list consists of a) the business name, b) a key distinctive word that is generally spelled correctly and c) the full proper naming of the file. The macro will then go through the files in a folder, compare the filename against the list, searching for the key distinctive word, and if the macro finds one of the key name within a filename, rename the file to the proper name on the list.

This solution doesn't solve all of the misspellings but it has dramatically cut down on the manual correction of file names. The improvement was very noticeable. And I am happy to know  that I made her life just a little bit easier. There is just one less thing to do in the morning.