Comparing Data Sets


Last Thursday I had the privilege of teaching a group of division order analysts about the VLOOKUP function. I consider VLOOKUP to be an essential tool for data manipulation, so I knew it would be the primary function I focused on during our short 55-minute class.

But I also knew that data is often not as neat and clean as I would hope for. I almost always have to tweak some field, and this almost always involves using Text Functions like LEFT, RIGHT, MID, &, LEN, FIND, and REVERSETEXT. So how did I squeeze these into an hour?

The answer is that I didn’t. I chose instead to record tutorial videos and temporarily place them on this site along with the workbooks so that each attendee could practice these at their own pace. I chose to stick with just the VLOOKUP, going over it for about half the hour, then inviting attendees to come to this website for more practice.


I have always known that comparing data can be a difficult thing for most folks. The quintessential wrong, but most often used way to compare data is through ocular inspection. That’s a fancy way of saying that people eyeball two data sets to see if they match. You can probably relate right? If you have ever tried positioning two data sets side by side and then moving one of them up and down to match the other one then you know what I mean.

I don’t use this method and haven’t for years. About 8 years ago I developed a function that creates a cross-tab summation. I thought it would be cool to be able to sum items based on the fields I chose, and return a grid of the summation based on another field I chose. For instance I could sum dollars based on account and company number, and show a different column for each month in the year.

This function became an instant hit, but I did not realize its potential until just a few years back when it occurred to me that I had created a data set comparison tool. If you use pivot tables for comparing data then you too have discovered what I mean.

I was fine with the steps I was going through. I could compare most data sets in a matter of a few minutes. That all changed when I began preparing for the 2010 NADOA Conference in San Antonio. I began to think that if I would automate just a bit more, I could come up with a really sweet tool, that more people could learn how to use.

That thought culminated in the newest feature on TheDataWand. After about two days of coding I have this fairly stable. I have found a few issues since the NADOA conference, and will no doubt be fixing bugs here and there as this gets vetted by more and more people.

The featured video shows this newest addition to TheDataWand at full speed. For a tutorial on using this feature go to TheDataWand help section and view the three videos for the FIND 14 functionality.

Posted in: News & Announcements on October 21st by steven


No comments yet

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment