DiffEngineX LLC

Difference algorithms work by finding the minimum number of differences between two sets of data.

When Align Rows is selected, DiffEngineX will insert blank rows into copies of your Excel spreadsheets in order to align similar rows. When the similarities are paired off with the same row numbers, the minimum number of differences can then be reported. If you have imported database rows in Excel, make sure you use Excel to sort the worksheets first (then Save them from the File menu) before using DiffEngineX to compare them. This is because DiffEngineX will not re-order rows in order to match them up.

DiffEngineX has two different row alignment algorithms. The checkbox "Use Alignment Plus" toggles between them.

What is the difference between the two row alignment algorithms?

before_a.xlsx and before_b.xlsx show two worksheets before row alignment. We can see both contain "Bob" and "Stuart" rows. The row in between both of them differs.

"Use Alignment Plus" Is Unchecked
rowalign_a.xlsx and rowalign_b.xlsx show the results of row alignment when "Use Alignment Plus" is unchecked. DiffEngineX has inserted a blank, yellow row in order to align the "Bob" and "Stuart" rows. The two different rows ("Robin" and "Peter") both end up in row 3 and have been colored because they are different. DiffEngineX has done its job: it has aligned similarities and reported the minimum number of differences.

"Use Alignment Plus" Is Checked
However for those interested in database data, there is the desire that unmatched rows ("Robin" and "Peter") don't end up with the same rows numbers i.e. "Robin" is paired against a blank row in the sheet it is compared against and vice-versa for "Peter". Checking "Use Alignment Plus" ensures these results are obtained. We can see "Robin" is colored red for a deleted row and "Peter" is colored green for a newly added row in rowalign_plus_a.xlsx and rowalign_plus_b.xlsx.

To summarize, "Align Rows" aligns similar rows. When "Use Alignment Plus" is checked, unmatched rows are explicitly paired with blank rows.

For most cases, only "Align Rows" needs to be used and "Align Columns" can be left unchecked.

Version 2.24 of DiffEngineX now supports Excel workbooks with over 255 individual worksheets.

DiffEngineX can be invoked programmatically from software as well as from the Windows command prompt. DiffEngineX accepts command line arguments. Please refer to earlier posts on how to do this.

For those of you using command line arguments, version 2.22 no longer displays various error message boxes which require a user to manually dismiss them before the process can exit.

Version 2.21 of DiffEngineX contains the following changes:

Fixes problem when comparing by Their Calculated Values and the workbooks were saved without formula recalculation.

Small problem with hyperlink navigation fixed. Only occurs when compact contiguous changes selected and range includes commas.

Better handling of comma separated value files when using command line arguments. CSV files are automatically converted into Excel workbooks during the comparison process. As such the contents of each csv file are placed into a worksheet in a new workbook. The name of this worksheet is the same as the filename minus the .csv extension. The sheets parameter must always be specified when comparing csv files. Note that comma separated value files with identical filenames cannot be compared. A sample command is given below.

"C:\Program Files\Florencesoft\DiffEngineX\DiffEngineX.exe"
/inbook1:a.csv /inbook2:b.csv
/report:report1.xlsx /outbook1:outbook1.xlsx /outbook2:outbook2.xlsx
/colordifferences /addhyperlinks /sheets:a,b

Fixes crash when Workbook Calculation is set to Automatic except for data tables.

Fixes problem when a merged cell is the last (bottom, right) cell in a worksheet.

Although DiffEngineX is not currently exposed as a COM component or DLL class library, it is possible to invoke DiffEngineX programmatically from software, as well as from the Windows Command Prompt. DiffEngineX can be driven by command line arguments. A full list of arguments is given in the help file available from the DiffEngineX menu item Help-->Help Topics-->Command Line Arguments. The list is also available on our Internet help page.

A snippet of C# .NET source code showing how to do this is given below. Although the string assigned to .Arguments has been split across several lines here, ensure that your code is on a single one.

(The /outbook1 and /outbook2 arguments are optional, if you don't want to save the DiffEngineX created reports to your filesystem. As /show has been used, the /report argument is also optional in the example below. Note that meaningful exit codes are only available from version 2.16.)

using System.Diagnostics;

namespace CallDiffEngineXExample
{
private void CallDiffEngineXProgrammatically1()
{
Process process = new Process();
try
{
string filename = @"C:\Program Files\Florencesoft\DiffEngineX\DiffEngineX.exe";
process.StartInfo.FileName = filename;
process.StartInfo.UseShellExecute = true;
process.StartInfo.Arguments = @"/inbook1:""C:\Users\Bob\test worksheets\original.xlsx""
/inbook2:""C:\Users\Bob\test worksheets\modified.xlsx""
/report:report1.xlsx
/outbook1:outbook1.xlsx
/outbook2:outbook2.xlsx
/compareexcelnames
/coloralternaterows
/addhyperlinks
/colordifferences
/show";
process.Start();
process.WaitForExit();
int exitCode = process.ExitCode;
}
catch
{

}
finally
{
process.Close();
}
}
}

Version 2.14 of DiffEngineX can compare the Visual Basic for Applications (VBA) code and macros contained in Microsoft Excel spreadsheets. The new difference report is in HTML format and is viewed in your default web browser.

Version 2.01 of DiffEngineX allows you to specify a minimum percentage change between two numbers before they are flagged as different.

We have recently written a concise description of DiffEngineX and guide to its use. It is recommended reading for everyone who uses the software. It can be found at

How To Use DiffEngineX To Compare Excel Spreadsheets

DiffEngineX can now compare Excel cell comments. This new functionality has to be explicitly turned on via the Options dialog. The comment differences are listed in a new sheet, called Excel Comments Comparison, on the difference report.

If a workbook of interest is compared against a blank spreadsheet, this option will effectively create a list of all the comments.

DiffEngineX can now treat numeric values as equal if they differ less than a specified value. This adds to its previous ability of being able to round numbers to a specified number of decimal places before comparing them.

NOTE: If you wish to compare the calculated values of formulae, rather than the formulae text itself, you should select the option Their Calculated Values.

Add Hyperlinks To Aid Navigation has been added to the Options dialog. This connects the difference report to every cell found to differ between two spreadsheets. Please refer to the Hyperlinks Help Entry for details.

Users can now discover new DiffEngineX features and auto-update the software, if appropriate, via a new menu item called Check for Updates available from the Help menu.

An option has been added to allow DiffEngineX to compare Excel defined names.

Excel allows names with either workbook or worksheet-level scope to be created. Workbook-level scope names are only compared when Whole Workbooks is selected on the main part of the user-interface. Likewise if /sheets is supplied as a command-line argument, workbook-level scope names are not compared.

Ranges and formulae referred to by names are always reported in R1C1 notation regardless of DiffEngineX's A1 or R1C1 notation setting.

This is because a cell reference in R1C1 notation is self-contained. Cell references in A1 notation can only be understood with reference to what Excel regards is the Active Cell at a particular moment in time, if they have any part of them that is a relative rather than absolute reference.

For example the Excel defined name and definition of CellBelow = Sheet1!R[1]C unambiguously refers to the cell 1 row down and 0 columns across, regardless of what the Active Cell happens to be at the time

However in A1 notation the same Excel defined name and defintion is

CellBelow = Sheet1!D18 when the Active Cell is $D$17

and

Cell Below = Sheet1!E18 when the Active Cell is $E$17

and

CellBelow = Sheet1!J3 when the ActiveCell is $J$2.

As such, due to relative references, it is better to compare name definitions in R1C1 notation.

Extra Point:
If a workbook of interest is compared against a blank spreadsheet, this new option will effectively report on all the defined names.

We have tested DiffEngineX with the 32-bit and 64-bit Windows 7 Release Candidate and can confirm our software is compatible with it.

As the software had already undergone modifications to be compatible with Windows Vista it did not need any further changes.

The original purpose of the alignment algorithm was to align rows and columns that match up between two worksheets. It was not concerned with unmatched rows. The intent was to minimize differences by alignment before a cell-by-cell comparison.

Users that were concerned with spotting what rows had been inserted and deleted found that sometimes an unmatched row in sheet #1 was paired up with another unmatched row in sheet #2.

This meant that the color used to indicate a modification was used, instead of the colors selected for additions and deletions. DiffEngineX by default works at the cell level, not at the row level.

Our first attempt to solve this problem was the launch of the Interpret modified cell as deletion and addition... feature found on the Extras dialog. However this only changes the color for the unique identifier portion of a row as selected via the Align Rows dialog box.

Now we have a far better solution. We now recommend users turn on the Use Alignment Plus feature which makes sure that all unmatched rows are paired up with blank rows. This way the appropriate color for either a new or deleted row is used.

Use Alignment Plus also works for columns.