Automated Solutions

Gathering Data

I always look for ways to keep data data. In my world reports are close to useless. You can not do a whole lot with a picture, but if your information is electronic your options are open.

One morning a few years back I noticed a co-worker standing at the fax machine for what seemed to be the whole day. I’m always interested in helping others automate their tasks so I asked about her’s.

She was printing off about 400 purchaser statements, sitting at her desk and writing the fax number on the statements, then standing at the fax machine for the next day and a half running them through. The IT department had promised they would automate the process, but that was a few years back.

I told her that with a little bit of SQL and some VBA we could gather the purchaser information, create a facsimile, then email or fax that information to the vendors. She spent the next few days obtaining as many email addresses as she could while I began coding her new system.

My first test run was 3 days later and by 5 days of coding we had most all the bugs worked out. Her entire process now took less than 45 minutes. Additionally, she began using the template to do research into purchaser inquiries because the system I built extracted the data more easily.

Four years later the IT department finally got around to coding her system.  They budgeted 600 hours to develop it. My Excel-based system only took 50 hours to code.

Comparing Data

One of the most time consuming challenges is how to compare one data set to another. I see some crazy techniques, the worst being where the user attempts to align data by moving matching rows up and down to pair with each other. Maybe you can relate to this?

A Divison Order Analyst recently asked me to create a more automated solution for comparing decks within the same spacing unit. Her reasoning was simple, for the most part the ownership in every well within a spacing unit should be identical.

I created a system that uses SQL to extract division order data from her Excalibur system, then uses VBA to compare each deck within the spacing unit.  She uses the software every day, and claims a savings of 1-2 hours per day.

For a more generic approach to comparing data sets watch this tutorial.