Compare | Ranges

As blocks of formulas are copied, pasted and modified throughout the workbook, it becomes useful to know if two blocks of cells are the same.

To ask the question more directly: how can you tell if the range at location 1 contains the same formulas that are contained in the matching cells in the range at location 2?

It might take 30 seconds or even several minutes to perform the tedious task of manually comparing the formulas or values in one range with those in another. Even then, you would not be able to prove to someone else that you had not made a mistake while performing the comparison.

Fortunately, there is an alternative: the Compare Ranges tool. This tool can determine the differences between two ranges. This comparison is typically completed within seconds, saving a reasonable amount of time and effort. The same comparison can also be repeated by somebody else.

For ease of identifying which ranges have been compared, the Compare Ranges tool will color the selected ranges according to the Range Selection color specified in the Compare Color Options.

Once the discrepancies have been identified and noted, a sibling tool, the Remove Compare Modifications tool, can then be used to remove any alterations that were made to the affected worksheets during the comparison process.

Note: The Compare Ranges command compares the contents of the cells in the respective ranges in the selection. It does not compare the cell formats or comments in those ranges.

One of the options making up this command, Highlight Differences On Worksheet, will alter your worksheets. Though there is an option to Remove compare modifications, you would be wise to keep a copy.

This command is useful whenever you need to find the differences between two blocks of cells.

When the OAK4 | Compare | Ranges command is chosen the dialog box shown below will appear. It contains:

The first thing to decide is the ranges you want to compare. OAK will attempt to initialize the Compare Range options dialog using the current selection.

This command compares, one-by-one, the cell contents of each range. Cell formats, cell notes (comments) and embedded objects are not compared.

The command will write a difference report for the pair of ranges compared if that option was checked on the dialog box.

There are 3 principal options that determine how to execute the cell comparison. These are: Write Report; Highlight Differences; and Group Block of the Same Modification. Each of these options is useful under different circumstances and is described in more detail below. Note that at least one of the first two options must be selected in order to perform any comparison exercise.

Highlight Differences. This option causes every cell, which differs between two worksheets, either by its location or content, to be compared and highlighted wherever a difference is identified. The command also has an auxiliary option, which enables you to specify the exact color in which differences would be shown.

To update your color preference, simply click on the Set Colors button located in dialog box. A secondary dialog box, entitled 'Comparison Colors', will open. This allows you to specify a unique color for each of the modification actions that OAK will make during the comparison processes. You can set the color for an action by clicking on the respective Set Color button and choosing a color from the available color palette or from the Custom colors library.

The Highlight Differences option enables you to instantly detect any discrepant cells between two ranges. It is especially useful when trying to distinguish between slightly modified copies of the same range. The default (and recommended) option is to conduct all comparisons with this option turned on.

Write Report. This option generates an output report containing a list of all the cells that are different between the compared ranges, along with the contents of the discrepant cells. The structure of the report is a single workbook that contains an overview of all the changes detected between the two ranges.

It is important to note that even if the Highlight Differences option is selected and the discrepant cells are evident on the worksheet, the Write Report option offers a convenient solution to the often necessary requirement of documenting all cell differences between two versions of the same worksheet. Furthermore, the individual reports contain hyperlinks that lead to the worksheets on which the discrepancies occur. In a large workbook, such functionality can be particularly beneficial for navigation.

The default setting is to generate a report at the end of the comparison process.

Group Blocks of the Same Modification. One of the newest options incorporated into the Compare tool suite, is the option to condense the output report into a summary of only distinct discrepancies between the different ranges. By selecting the Group Blocks of the Same Modification option, OAK will only report one difference for every distinct row or column discrepancy between both ranges.

When performing a comparison, you can choose to compare either the Formulas or the Values in the cells in those ranges. The difference between these two options is the way in which cells containing formulas are compared.

Using the Formula itself. The default option is to compare the formulas in the two ranges. This means that in those cells, which contain formulas, the formulas are compared with no regard for their resulting values. Thus, for a given pair of cells that are compared, if the formulas are the same, but the values are different, no difference is reported for that pair of cells. It should be noted, however, that when comparing cells that do not contain any formulas, the values would then be compared.

•	Using the Calculated Value. When the Calculated Value option is chosen, the inverse is the case: the formulas within the cells are ignored and the comparisons are now made between the actual values, which the formulas evaluate to.

•

Case Sensitive Comparison. This option allows you to “force” OAK to treat texts with different capitalization profiles as being different. The option can be used in conjunction with both Formula and Calculated Value comparisons. In the case of the former, OAK is sensitive to differences in the capitalizations of the names used in the formula, while the latter is a proxy for testing that ordinary cell texts in both ranges have the same capitalizations throughout.

When both the Write Report option and the Formulas test type option are selected, the differences between formulas can be reported in either one of two styles: A1 or R1C1. These two options simply alter the presentation of the output report but do not affect the results of the actual worksheet comparison.

Using the A1 style will cause discrepant formulas to be reported with cell references of the form C25 or D18; while the R1C1 style will report the same formula discrepancy using cell references of the form R[i]C[j], where i and j are integers other than zero.

The advantage of using A1 style is that the formulas are easier to understand. Using R1C1 style, however, is more compact because similar formulas are grouped together. For example, consider the following worksheet:

If we write the formulas using A1 style cell referencing, each formula is written differently with the middle formula simply written as: “=D4*(1+E3/100)”. Using R1C1 style cell references, however, all three formulas can be grouped together using the following notation: “=RC[-1]*(1+R[-1]C/100)”. This makes the output reports more compact but less transparent.

The Compare Ranges command will not succeed if one of the worksheets is protected. You must first unprotect the protected worksheet before running the command.

As mentioned earlier, the Compare Ranges command only compares the contents of the cells on the respective ranges; it ignores the presence of cell notes (comments), and embedded objects. It is possible for two ranges that are reported as having no differences to be different in terms of these items.

When comparing worksheets containing no formulas (only numbers), it makes no difference whether the Formulas or Values test type option is chosen for the comparison. Furthermore, the Formula style option is only effective when both the Write Report option and the Formulas test type option are jointly selected.

Aside from using another product, a comparison of values could be performed using conditional formatting, or a block of TRUE/FALSE formulas equal to the difference between the compared cells.