All that learnin' paid off

A couple posts back, I wrote about this horrendous script I had to make for work. To sum it up, I had to iterate through, make comparisons, and write data when I found matches. Doing it iteratively, I ended up with nested loop 3 levels deep. Needless to say, the script was really slow and inefficient. I knew enough to recognize the fact, but I really could not think of a way to improve upon it.

Over the weekend, the problem got shoved to the back part of my mind that works on things without me really thinking about it. Finally the idea struck me that I could use a better data structure to perform the task. In my algorithm design and analysis class, we learned about hash functions and how to use a special table of keys and values to figure out the location of the desired data. I used something similar to that with VBScript's dictionary data structure.

Using the same assumption that I was dealing with pre-sorted data, I was able to make a dictionary for storing each first occurrence of a Supervisor ID in the data and the Excel row number as the value. I had the program create a dictionary of this information for both the Members sheet and the Addresses sheet. By using the dictionaries, the script could just jump to exactly where the desired data resided and write data that way. It took me ten minutes to do this morning what it took me nearly a week to do last week. Too bad I hadn't thought of it earlier.

Moral of the story: either pay attention to data algorithms classes, or at least know enough about it to look things up or ask a good friend.