Chantastisoft is proud to present a second piece of software geared toward making scientific research easier. Introducing Data Compare-o-Matic!
As part of my work at the School of Nursing, I was asked to find easy-to-use software for conducting comparison of double-entered data. This method of data entry has two people independently enter the same set of data. The two data sets are then compared for accuracy. Discrepancies show where one of the data enterers had made a mistake.
We at the Meta-Analysis Research Center needed software to not just show the discrepancies, but easily allow for the necessary changes. I explored a few options. Before my arrival, the team used Epi Info, which has a good implementation of data comparison through Microsoft Access. The software is a bit old and it bugs out at over 100 columns of variables. Our data set is already in 20 sets of roughly 200 columns each, so further splitting of the tables to fit Epi Info’s requirement would have multiplied the necessary work. Scripted statistical packages allow for data comparison but we the method to be accessible for everyone on the team and none us are super proficient scripters.
A few months ago, I was asked to find a way to copy data from an Excel table of one format to another format. The original has data in sets of four pages of four columns and 25 rows each while the new table has the same data expressed in one row per set. We were going hand transpose these data but I decided to look for a better way. I looked into writing an Excel macro, and following the same strategy I used to learn Javascript for Anthropomotron (I Googled the task I was trying to solve at the moment, e.g. “excel vba move cells“), I picked up enough VBA to write a macro that asked for the starting cell of both tables and then copied the data automatically. The macro, Travis Copy-o-Matic (named for the coworker who needed it), does its job fantastically and it will save him and us as a whole a lot of time in data entry.
Anyway, while looking for a solution for data comparison, I realized that what I needed to do now is similar to what Travis Copy-o-Matic does. The key difference is that instead of rote copying one way, the macro has to let the user make a decision on which a direction a datum is to be copied when it encounters a discrepancy. With Travis Copy-o-Matic as the base I added the necessary parts to make a data comparison tool. I learned how to make the macro compare two cells in different tables, then display their contents in a dialog box for the user to examine. That led to UI design (Copy-o-Matic had just one big copy button to press and two dialog boxes to ask for the location of the two tables). The early version of Data Compare-o-Matic then had two buttons: one to choose the value in table 1 as the correct one to copy to table 2, and another button to do the opposite if table 2 had the right value.
Testing revealed the need for a third button. What if both values are wrong? I put in a write-in button that led to another dialog box to allow the user to override the contents of the cell in both tables. At the end, this is the UI I came up with:
I made the main buttons oversized and separated by space to avoid confusion. The discrepancies are shown in a big font size since that’s what’s most important. I rather like it all put together.
So that is how Data Compare-o-Matic came to be. Unlike Copy-o-Matic, which does one thing one specific way, I think Data Compare-o-Matic has utility beyond our research project so I am making it publicly available. You can download it on its own special page on my website.
I also made a tutorial video to show potential users how the macro works. I tried to make it a little more interesting than the usual tutorial so you may want to watch it even if you have no intention of using the macro for anything. Seriously, I tried to make the video funny so please watch it and help it go viral.
httpvh://www.youtube.com/watch?v=xzB5aXKyQGA
Enjoy and let me know what you think!