Florencesoft

Last week a customer used DiffEngineX to compare two Excel 2007 spreadsheets each with at least 750, 000 rows. The comparison took approximately 4 hours 30 minutes to complete. The customer confirmed they were pleased with the results. Align Rows was turned on. We generally recommend the data should be pre-sorted in Excel using functionality available from its Data tab or menu before a comparison, if necessary.

We have compared spreadsheets with a million rows ourselves and seen results in a couple of minutes. So why do some comparisons take hours and some only minutes? The answer is that DiffEngineX is much faster when the number of differences buried in a million rows is small, rather than large. With a large number of differences, more blank rows have to be inserted to get the data to line up and this is a time-consuming, Excel mediated operation.

This is the first report of DiffEngineX being used with such a large amount of data. The key points are that the comparison may take hours and that you should ensure the Align Rows feature is selected. Unless your data is guaranteed to be in sorted order, you should get Excel to sort it before a comparison. (This does not apply when comparing formulae based models.)

In the days of Excel 2003, one customer told us he used DiffEngineX to compare two worksheets containing 50, 000 rows a piece.

With such large amounts of data, it is probably difficult to spot the differences in the colour highlighted worksheets. That is why we recommend using the Extra dialog to turn on the Hide Matching Rows feature.

When we updated DiffEngineX to work with Windows Vista and Excel 2007, we internally re-factored the software to be faster and to take up less memory. We needed to do this because of the greatly increased upper row limit in Excel 2007.

However as a consequence it made DiffEngineX faster at comparing spreadsheets when run on Windows XP with previous versions of Excel as well. If your spreadsheets contain tens of thousands of rows you will notice the difference.

Since the first release of DiffEngineX we have added the following features:

• An easier registration system that no longer depends on unique Product Codes


• The ability to Hide Matching Rows on the colour highlighted sheets making it easier to see just the differences


• The ability to Hide Different Rows if you are interested in seeing what rows are shared between two worksheets


• Highlighting of differences at the character level and automatic cell expansion to show large amounts of text on the difference report


• Grouping of like adjacent changes so that only one change to a block of equivalent formulae is reported


• The ability to ignore differences in capitalization and white space


• The ability to ignore small numeric differences


• An increase in the maximum number of rows and columns that can be used for alignment purposes


• The ability to compare any file format that can be imported into Excel


• Original cell addresses (before row and column alignment was carried out) of differences listed on difference report

We have also added a whole host of other small changes.

Although in general we offer free updates, if you are using Version 1.28 or lower and wish to upgrade to the latest version we make a small charge.

We purchased a 3 year code signing digital id from VeriSign this week. Our previous one was about to expire.

Digital ids allow software publishers to digitally sign the applications they produce. Code signing provides a guarantee that the software has not been tampered with or altered in transit and that the software is produced by who says they produce it. Windows Vista warns users that unsigned software comes from an unknown publisher when installing it.

DiffEngineX's Options dialog contains several useful features.

The two most powerful are Color in red precisely the parts of formulae and text constants that differ and Compact like changes with contiguous.

The effects of turning the first option on are shown below.



The differences between two spreadsheet cells are highlighted using the colour red on the difference report. Additionally cells will be made larger so that lengthy amounts of text can be easily read.

Compact like changes with contiguous is intended for workbooks that contain blocks of equivalent formulae. Equivalent formulae are different in the sense that they reference different cells, but the different cells are the same relative offsets away. Financial models often contain blocks of tens to hundreds of equivalent formulae which are modified as a whole unit. When this option is turned on, only one change will be reported on the difference report, instead of hundreds of individual changes.

The main version of DiffEngineX is a stand-alone executable that runs outside of Microsoft Excel. This is what the vast majority of users download.

DiffEngineX is also available as an Excel add-in. It has slightly different functionality. Over the past year, one customer needed DiffEngineX to compare two Excel workbooks, which use a third-party add-in to dynamically fetch database information. When the executable form of DiffEngineX compared the workbooks, it did so without loading the third-party add-in and as such did not give the desired results.

However the add-in version of DiffEngineX did load the third-party add-in before comparing the worksheets. It gave the desired results.

The add-in version is available on request.

One of the major differences between Excel 2007 and 2003, it that the maximum number of rows has increased from 65, 536 to 1, 048, 576. The maximum number of columns has increased from 256 to 16, 384.

We have to assume that at some point a user will try to compare two spreadsheets containing a million rows. I compared two sample spreadsheets each containing a million rows with a slightly modified copy of DiffEngineX (Version 1.27). A computer with 3.5 Gb of physical memory was used.

The old version of DiffEngineX worked away until it ran out of memory!

To solve this problem, I made the internal data structures used by DiffEngineX more memory efficient. Aligning a million rows is on a very different scale to aligning 50, 000 rows and so at the same time I made the algorithms faster as well. One small part of DiffEngineX was taking quadratic time and its inefficiency was made glaringly obvious when trying to deal with a million rows. Fortunately it was very easy to convert it to take linear time.

The new version of DiffEngineX can compare a million rows in a time measured in minutes, not hours. However the actual insertion of a blank row in order to line up existing rows requires DiffEngineX to make a call to Excel. This is intrinsically slow and so there may be pathological cases that will take longer.

As a consequence of making DiffEngineX work with Excel 2007, it now works faster with Excel 2003 as well.

I should point out that for most users a comparison of even a million rows should take minutes. If it is taking hours then it is a fair bet that the user is missing out a step. If you are comparing rows of data, as opposed to formulae based models, then you must get Excel to sort your data first in both workbooks. Sort functionality can be found under Excel's Data menu or tab. Then re-save your Excel workbooks. When you start up DiffEngineX make sure the Align Rows box is checked.

I should point out that a prior sorting step is not always needed. DiffEngineX will insert blank rows to get existing rows to line up (and therefore not be flagged as different), but it will not reorder rows. That is why they must be sorted first. Data imported into Excel from databases and third party applications will likely need sorting first. If the data originates in the form of Excel workbooks, it probably does not require sorting.

Please refer to the below tutorial for full details.

Tutorial: How to Compare Two Excel Lists