Florencesoft

By default DiffEngineX uses the color red for a deleted cell, green for an added cell and purple for a modified cell. However when is a modified cell not really a modified cell, but rather part of a row that has been entirely deleted and replaced by another row that just happens to be occupying its old position?

When Align Rows is specified, you select a column or columns that specify the parts of a row which make up its unique identifier. If the unique identifier is present in one worksheet, but not the other then the row has either been deleted or it is a new one. As such coloring the identifying part of the row purple is inappropriate, as what really has happened is that there is one deletion and one addition.

The Extras dialog now has an option called Interpret modified cell as deletion and addition... which lets you choose how DiffEngineX colors the unique parts of each row.

You may wish to get Excel to sort the color highlighted sheets afterwards on a color of your choice (only available in Excel 2007) to get all the deleted and added rows to line up. Remember to only sort on the unique parts of a row.

When determining whether a row has been added or deleted, it is helpful to only look at the unique parts of each row.

Excel 2007 provides functionality to delete duplicate records, but it does not actually report on what they are.

We are currently writing a new application that is not connected with Excel, but does report on duplicates imported into it. If anyone is trying to detect duplicates we can provide a beta copy of our application.

Duplicates can confuse the analysis of differences. If one dataset has two copies of a record and it is compared against another dataset with only one copy, then a difference analysis will report the first dataset has a record not present in the other one.

Last week DiffEngineX was used to compare two Excel 2007 spreadsheets each with at least 750, 000 rows. The comparison took approximately 4 hours 30 minutes to complete. The user 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.

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.

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.

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.

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