The Short Guide To DiffEngineX
DiffEngineX is an implementation of the classic "diff" algorithm as applied to comparing Excel spreadsheets. This means that when you turn on its Align Rows functionality it will insert blank rows in order to get similar rows to line up in automatically made copies of your spreadsheets. It does not reorder rows. When aligning rows DiffEngineX does not consider the whole row, only the columns you specify as important when determining which rows in spreadsheet #1 are similar to the rows in spreadsheet #2.
After the optional row alignment, DiffEngineX performs a cell by cell comparison of your spreadsheets. Hide Matching Rows found on the Extras dialog allows you to quickly see all the differences at once on the two color highlighted sheets.
I am comparing rows of data / lists of members / lists of inventory.
What does this mean for me?
DiffEngineX can compare rows of data or lists perfectly well, but with one caveat. This results from the fact that it will insert blank rows to align similar rows, but will not reorder rows. The rows of data need to be pre-sorted first. Excel can do this for you via its Sort functionality under its Data tab or menu. After sorting in Excel make sure you re-save the changes. If you need to sort on a combination of columns, you need to hit Excel's Add Level button.
If your data is already in sorted order you can miss out this step. Make sure you turn on DiffEngineX's Align Rows and Use Alignment Plus functionality. Typically Align Columns can be left turned off.
When dealing with large numbers of rows, you may want to turn on Hide Matching Rows found on DiffEngineX's Extras dialog.
For more information, please refer to our tutorial on comparing lists.
I am comparing formulae based spreadsheets.
What does this mean for me?
Obviously it means you can forget about the need to pre-sort your worksheets. However if new rows have been inserted in the middle or at the top of your spreadsheets, you will need to turn on Align Rows. Make sure Use Alignment Plus is turned off. Typically we have found in formulae based spreadsheets that Use Alignment Plus inserts too many blank rows - it is only intended to be used with data based spreadsheets.
What are the reports that DiffEngineX produces called?
Typically DiffEngineX will generate three new workbooks per comparison. The first two will be color highlighted copies of your spreadsheets and will have the same names as the original ones, except for a number appended on the end. The third one will be the difference report and will typically be called Sheet1. Each sheet in the difference report lists the differences found on the worksheet of the same name in your original spreadsheets.
How can I see differences reported at the character level?
If your spreadsheets have a large number of differences, highlighting differences at the character level will greatly slow down comparisons.
Checking Highlight Character Level Differences on the main part of DiffEngineX's user interface will show differences at the character level directly on automatically made copies of your worksheets.
Checking Color in red precisely the parts of formulae and text constants that differ on the Options dialog will show character level differences on the difference report.
Is there anything else?
Turning on Add Hyperlinks to aid navigation via the options dialog, links each reported difference to both the color highlighted copies and your original spreadsheets (Excel 2007 and above only).
Viewing the two color highlighted sheets side-by-side with synchronous scrolling is often an easier way to see the differences than by inspection of the difference report alone. Click here for details on how to do this.