# Testers wanted for Excel comparison application



## mathias.brandewinder (Aug 2, 2009)

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 (Aug 3, 2009)

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 (Aug 3, 2009)

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 (Aug 3, 2009)

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 (Aug 3, 2009)

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 (Aug 4, 2009)

mathias.brandewinder said:


> 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 (Aug 4, 2009)

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 (Oct 5, 2009)

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 (Jan 2, 2010)

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 (Jan 2, 2010)

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



pawan said:


> 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
> ...


----------



## mathias.brandewinder (Aug 2, 2009)

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


----------



## pawan (Jan 2, 2010)

mathias.brandewinder said:


> 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!
> Mathias


Your are welcome.


mathias.brandewinder said:


> 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,
> Mathias


Let the comments be hidden till the user moves mouse over on the comments. That is still good enough and avoids a lot of clutter when there are too many changes (or red cells).


mathias.brandewinder said:


> 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!
> Mathias


 
In the version I made, the two sheets are compared using a third sheet which copies the values of one of the sheets onto itself and compares the values with the value on the other sheet using conditional formatting and highlights the cell in a different color if there are differences. So the comparison is done in a sheet altogether different from the sheets being compared . So the issue of comments in the original sheet does not arise. Actually your reply got me thinking whether the comments can also be compared. but lets not make it very complicated. Formulae and value comparison is good enough for most uses.


mathias.brandewinder said:


> 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.
> Mathias


Unfortunately i use a company laptop and my company's policies do not allow me to install any software including .NET on to my laptop. Neither do i know .NET. That is why I could not go load your application and preferred not to have it in .NET 


mathias.brandewinder said:


> 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!
> Mathias


What I did in my workbook is that at the top of the comparator sheet there are two input cells where the user inputs the names of the sheets to be compared. It is not a tool by itself, i used it in my project workbook. It is a no frills simple idea implemented in excel itself. It only compares two sheets at a time (in my case two worksheets from the same workbook. However these worksheets are two versions of inputs to the model - so there can be comparison on what has changed between the two versions) But the same idea can be taken outside the workbook and developed into a proper "tool" with many more features as you are trying to do.


mathias.brandewinder said:


> 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?
> Mathias


My position may not change much on the .NET. There will be lot of people like me.The "parellel" version sounds interesting


mathias.brandewinder said:


> 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


 
Have a Great 2010!! All the best for the development. Lets hope it gets ready soon and I hope to convince my IT folks to let me install it on my laptop.


----------

