Testers wanted for Excel comparison application

[mathias.brandewinder]

Greetings!

I am a long-time Excel fan, as well as a .NET developer. I have written a small application in my free time, which allows you to open two workbooks, and compare them to track the differences. I wrote it because in the course of consulting projects, quite a few times, I had to figure out how multiple versions of a workbook were different, an issue which becomes tedious once the worbooks grow large.

I plan on releasing this application for free, as an open-source project, and would really like feedback on it. I have a few ideas of my own on possible improvements, but nothing beats real users to spot issues or suggest enhancements!

So if you are interested and have time, you can download the application at

http://www.clear-lines.com/akin.aspx

Thanks in advance for any feedback!

Mathias

[Richard Schollar]

Hi Mathias

Is this version specific (eg just xl2007, or just xl2003) or does it cater for all versions? Am I correct in thinking that you would 'load' two workbooks and the application would then list the differences? That's a pretty big endeavor if so (and also potentially very useful) - I will try it out when I am home

[mathias.brandewinder]

Hi Richard,

It works with 2003 and 2007, and your description is about right: you load the two workbooks, and once this is done, Akin highlights the differences between the worksheets.

I really want to make this app useful to the community if I can, so your feedback is really welcome - thank you!

Mathias

PS Great avatar!

[Richard Schollar]

Avatar courtesy of one Dave Hawley from Down Under

I installed the app (completely painless, I already had .NET3.5 SP1) and then tried it out with a spreadie that was mainly a data store. The comparison was pretty fast given there were 21,700 populated cells on the sheet. I liked the grid viewer which highlighted the differences by colouring the row/column headers red.

One thing I will say, I would have liked to have seen a summary of the cells with differences (eg a list with three columns perhaps col1: cell address, col2: Original Value, col3:Modified Value). There were only two differences between the sheets which meant I didn't have any trouble locating them, but if there were many in one particular column, it would have been a pain having to scroll through the page to see them all (+would missing some). A downloadable report (eg to csv) would be nice listing all the cell differences (in format as outlined above).

In the grid view, it would be nice to filter by error values (eg double click on a particular column and all the errors in that column are presented in a filter view - very much like Autofilter does it in Excel proper).

I really liked the ability to compare the formulas. I could see this having a lot of uses (especially in any corporate environment with evil SOX EUC auditors). I will try it out with some more complex spreadsheets with a few more differences between the original and modified version.

A very valuable tool though - are you really going to keep it free to use?

[mathias.brandewinder]

Thanks so much for the feedback!

It's great that you had .NET 3.5 SP1 already on, because it is a bit of a pain to install, and unfortunately its installation is not as widespread yet as I would hope...

The report is a great idea - and pretty easy to implement, too so this one definitely enters in the list of features for the next release.

One idea I am toying with to improve navigation to errors is to add buttons that go to the next difference in the current column or row - so you wouldn't have to scroll manually.

I have to think through the AutoFilter-like idea. Believe it or not, I actually never used this in Excel and it's pretty nice... Just to make sure I get it, are you thinking of clicking on the header on a column (or row), and this would hide all the rows (columns) that have no difference?

Thank you for the positive feedback on the "compare formulas", this is a feature which was important to me. I really want to add also "compare named ranges", but this is more challenging.

And yes, I do intend to keep it free! If it becomes "incredibly successful", I MAY have a dual version one day, with a free and a "premium" version (there will ALWAYS be a free, community version, though), but as of now, I am just having plenty of fun developing this, and toying with technology. And I'd much rather have lots of happy users, with lots of feedback to make it a project I can be proud of, rather than go through the hassle of setting up a business/support infrastructure for a handful of customers. Besides, one of the things I always appreciated about the software community is that it IS a community. You get free help all the time, and give back when you can...

[Richard Schollar]

Originally Posted by mathias.brandewinder

One idea I am toying with to improve navigation to errors is to add buttons that go to the next difference in the current column or row - so you wouldn't have to scroll manually.

Yep - great idea.

I have to think through the AutoFilter-like idea. Believe it or not, I actually never used this in Excel and it's pretty nice... Just to make sure I get it, are you thinking of clicking on the header on a column (or row), and this would hide all the rows (columns) that have no difference?

Yes - I would envisage that if you could see there were differences in column C say (ie it is red) then you could click on the col C button and it would automatically hide all rows that didn't have a difference in col C (I think this would be very useful). Clicking again would release the filter. Same thing with a row button - click on it and it collapses all the columns that don't have a difference in that row. Does this make sense? This would make it even better than Excel's autofilter (which just does column filtering).

Thank you for the positive feedback on the "compare formulas", this is a feature which was important to me. I really want to add also "compare named ranges", but this is more challenging.

Yes I can see that could be tricky (especially with dynamic named ranges which expand and contract dependent on the data within the sheet). I have to say, for me this would be much less important than the functionality you already have.

I assume that you must have Excel installed (2003 or 2007) to use Akin? Does it use Excel like an automation server to do the processing?

Something else that might be nice would be to load two workbooks and produce a report based on the differences between all sheets (of the same name, naturally!). It would save going thru every sheet at the same time. In this case I think iti definitely would help to see a list of all the differences so that the observer could focus in on the important ones. Maybe something for alter releases?

And yes, I do intend to keep it free! If ... as of now, I am just having plenty of fun developing this, and toying with technology. And I'd much rather have lots of happy users, with lots of feedback to make it a project I can be proud of...

Great sentiments!

[mathias.brandewinder]

Hi Richard,

I really like the idea of the AutoFilter feature. I am thinking I could implement it so that you can filer on a row, a column, or the entire sheet, hiding every row and column where there is no difference, just displaying rows and columns where there is at least one difference. I am busy with a project right now, but as soon as I have free time I'll get to it. Stay tuned

The named range idea is coming from the fact that I have experienced situations where sheets contain lots of named ranges, and when people start to mess up with these, it is VERY hard to debug it by hand.

The suggestion you make regarding doing a full workbook comparison is also on my radar; the issue I am dealing with is, what if the sheet names don't match? I can reasonably assume that if 2 sheets have the same name, they should be the same, but if there are some differences, I would like to try to automatically figure out which ones should match, maybe based on the % difference between them. I have to think that one through... So yes, probably in a release soon, but the other suggestions will likely take precedence! One step at a time

Technically, Akin is written in .NET, in C#, and uses Interop to open the files. It requires Excel 2003 or 2007 on the user machine, uses it to open the workbooks, and fill in a "simplified" representation of the workbook focused on the values/formulas.

Thanks again for the feedback, it's really valuable - lots of good ideas, lots of fun ahead!

Cheers,

Mathias

[mathias.brandewinder]

Just wanted to announce that I finally had time to get back to Akin, my free worksheet comparison application, and just released an updated version here:

www.clear-lines.com/akin.aspx

There are no new features yet (working on it!), but I have completely re-written the display code, with considerable performance improvement: the time required to display a 200x200 comparison went down from 20s+ to virtually instantaneous...

Any suggestions and criticism is highly welcome!

Mathias

[pawan]

Hi Mathias
I had composed a detailed post but unfortunately i exceeded the time limit and .....phew it went. I have composed again in notepad. Here is my experience when i tried, my feedback and a few thoughts

1) It is definately a good idea and a very useful tool

2) I had done this for comparison between two sheets using conditional formating.This compared only the value not the formula. How it worked was that there is one sheet which is called comparator which would compare the two sheets to be compared. The conditional format formula (in the comparator sheet) searches for the comparable cells in the two sheets and equates the cell value to the value in one of the sheets and marks the cell red if it is different from that in the other sheet. The drawback is that user still will have to juggle between the sheets to see what had changed once the cell was highlighted red. To prevent this i am planning to insert, using VBA code, a comment saying that the value in the other sheet is so and so.

3) A good tool should have a formula comparison mode as well as a value comparison mode.

4) On your full workbook comparison, i have an idea when the worksheet names do not match. Let the user match the worksheets in one workbook to be compared with the other workbook. Calculating %age difference to find out which worksheet to be compared may not be practical.

5) I would prefer the tool in Excel not in .NET. Many users may not have .NET

All the best

[mathias.brandewinder]

Dear Pawan,

First, thanks a lot for taking the time to send me your feedback! Especially so given that you had to do it twice - Sorry you lost your initial message, I hate when that happens to me!

I think your idea using conditional formatting, and inserting comments, is interesting. I am considering using a similar idea in Akin. The problem I see with inserting comments is that first, it is not always easy to see them, and then, if there are already comments in the sheet, you probably don't want to overwrite them. My current thinking is to generate a third sheet, which copies the "original" sheet and adds comments, and possibly add automatically a macro which can then go over every comment automatically and replace the content of the sheet with the modified content, if the users wants to. Still thinking about it, though - but maybe you can use that idea in your project!

Regarding the value and formula comparison, Akin actually already has that feature, maybe you missed it? There is a combo box on screen, right above the excel-like display, where you can switch between value and formula.

Thanks for the input regarding the full workbook comparison! This is also a feature I am currently thinking about. Like you, I considered matching sheets by best % match, and name. The difficulty in my opinion is that you would want the application to do an automatic match which makes sense by default, using these 2 criteria, but also allow the user to match sheets any way he/she wants - without making it all complicated!

On the .Net question, I totally understand your position, and I thought hard about it. The issue I have is that without .Net, I lose lots of capabilities. .Net provides very nice user interface, and much faster computations. One option I am considering is to release a "parallel" version as a VSTO excel add-in, which would still require .Net, but integrate the application completely inside Excel, so that the user doesn't have to leave excel, and can do all in one place. Does this sound like an interesting option to you?

Again, I really appreciate the feedback. I plan on getting a new version out in the not-too-far future, hopefully in a quarter. Stay tuned, and please don't hesitate to contact me if you have further thoughts or criticism!

Thanks again, and have a wonderful new year!

Mathias

Originally Posted by pawan

Hi Mathias
I had composed a detailed post but unfortunately i exceeded the time limit and .....phew it went. I have composed again in notepad. Here is my experience when i tried, my feedback and a few thoughts

1) It is definately a good idea and a very useful tool

2) I had done this for comparison between two sheets using conditional formating.This compared only the value not the formula. How it worked was that there is one sheet which is called comparator which would compare the two sheets to be compared. The conditional format formula (in the comparator sheet) searches for the comparable cells in the two sheets and equates the cell value to the value in one of the sheets and marks the cell red if it is different from that in the other sheet. The drawback is that user still will have to juggle between the sheets to see what had changed once the cell was highlighted red. To prevent this i am planning to insert, using VBA code, a comment saying that the value in the other sheet is so and so.

3) A good tool should have a formula comparison mode as well as a value comparison mode.

4) On your full workbook comparison, i have an idea when the worksheet names do not match. Let the user match the worksheets in one workbook to be compared with the other workbook. Calculating %age difference to find out which worksheet to be compared may not be practical.

5) I would prefer the tool in Excel not in .NET. Many users may not have .NET

All the best