Compare and Merge for Excel Help


Compare and Merge for Excel is an Excel Add-In that can save you hours of time comparing, analyzing, and reconciling information. With a few clicks you can compare sheets, ranges, tables, and other lists and review the differences. Then you can optionally merge the results into a new sheet/range.

Compare and Merge runs in an Excel Task Pane and interacts with the current Excel workbook allowing you to select ranges, tables, and lists and instantly compare them and analyze the results. The results can be merged using the Task Pane where the Add-in provides an intuitive way to view and merge the differences cell-by-cell. Compare and Merge can also find rows that are added, removed, and/or if there a duplicates rows in either of the compare ranges and merges these into the results.


You need to purchase a subscription (with free 30-day trial) to the Data Plus for Excel SaaS application to use Compare and Merge. See How To - Purchase SaaS App and Assign Add-in Licenses for more information on acquiring a license and the Compare and Merge sign in process.



Getting Started

Get started by following the steps presented on the start screen. The process is simple:

Find Ranges to Compare
Set Left and Right Compare Ranges
Set Compare Options
Run Compare
View/Analyze Results
Optionally, resolve differences

Compare and Merge will guide you through the process in just a few clicks (or taps).


If you have never used Compare and Merge, the Create Sample Compare button is a good option. This will create sample data and automatically run a compare for you. You can the view the results which are annotated with helpful comments that explain the results. You can also change compare options, re-run the compare and view how the results are changed.


If you have previously saved a Compare, you can run it with one click/tap from the "Compares" view.

Getting Started

Find and Select Ranges To Compare

To start the compare process you find and select the ranges you wish to compare, known as the Left and Right compare ranges. There are multiple ways the app helps you to find and select ranges.

Use Find Ranges to have the app automatically scan the workbook for the ranges and tables within each sheet. Each range or table has a preview of the data and a link to jump to the range in the workbook. Use the "Set Left" and "Set Right" buttons at the top of each range to select that range as the Left or Right compare range, respectively.

Use Select Ranges to have more control over range selection. These options "watch" as you navigate around the workbook to help find ranges based on your selections. There are 3 options using this method:

  • Select Sheet this method will automatically find the first contiguous range or table in each sheet as you select sheets in the workbook.
  • Select Cell this method will automatically find the first contiguous range or table starting at the cell you select in the workbook.
  • Manual this method allows you to manually select ranges (using mouse, keyboard etc.) in the workbook to compare.

All three methods will present a preview of the data and buttons to set either the Left or the Right range.

When the Left and Right Compare ranges are selected, then use the "Next" button to continue.

IMPORTANT:   In all the methods to find ranges, once the Left and Right ranges are selected and a compare is run, the compare process will always check for additional rows added to the ranges and include them in the compare process. This becomes even more convenient when you save a Compare to run at a later date and the compare data may have expanded with more rows (or contracted withs less rows).

Find Ranges Select Ranges

Left and Right Compare Ranges

Why Left and Right?

Using Left and Right provides the perception of comparing things "side-by-side" which is generally what Compare and Merge is doing. However, in the compare process the Right range has greater significance as outlined by the following:

  • The Automatically Resolve differences feature is implemented by "merging right". This means you are automatically accepting the Right range values.
  • If the Left and Right compare cells have a different format, the Right cell format will be used when showing differences and for added, removed, and duplicates rows in the results for a Match Rows compare. For example, if the Left cell is a number with format "General" and the Right cell is number with format "Accounting" the difference value will be displayed in the Accounting format such as ($450.33)
  • The Right range is used as the source for column headers in the compare results and to retrieve the list of columns when using the "Get Columns" feature in order to select specific columns to be compared.

Left and Right can also be more intuitive with time-based compare ranges. For example, if you are comparing current quarter data with previous quarter data then you would set the Right range to be the current quarter on the basis that it contains more recent data and you would accept current data over older data when merging results -- i.e. "merge right".

Columns and Keys

This view enables you to select columns from the Left and Right compare ranges to include in the compare and results.

What are key columns? Key is a database term used to specify a column or columns in a table (or range) that identifies data in that column to be unique or to identify the row as a "record", for example "customer_id". Compare and Merge uses key columns to first, find a matching row in the Left and Right compare ranges that have the same value in this column, and then compare the remaining values in that row. This is called a Match Rows Compare and is one of the most powerful features of Compare and Merge.

It is not required to select key columns but typically compare ranges originate in a database or service (ex. salesforce.com) where a column or columns have unique identifiers for the data, for example, "customer_id". Selecting these columns as keys will cause the compare process to find matching rows with these keys first and then compare the cells in that row.

With key columns and a Match Rows compare, Compare and Merge can also find if there are rows in the Left Range but not in the Right range (removed rows), rows in the Right range that are not in the Left range (added rows), or if there are duplicate rows in either the Left or Right ranges.

  • Added Rows rows in the Right Range that are not in the Left range (ex. "customers that were added this month")
  • Removed Rows rows in the Left Range that are not in the Right range (ex. "lost customers this month")
  • Duplicates in Left Range multiple rows with the same key value in the Left Range
  • Duplicates in Right Range multiple rows with the same key value in the Right Range

Without key columns, Compare and Merge will fallback to a "Cell-by-Cell" compare type which compare the Left and Right ranges cell-by-cell from top/left to right/bottom.

NOTE: Generally, the compare process expects that your columns are in the same position in both the Left and Right ranges. In other words, the compare process needs the columns to "line up" in both ranges. This is important when using the Match Rows compare type the columns used to match rows must be in the same column position for the compare process to find matching rows.

Match Rows Compare Type with Key Columns

Example: Match Rows with Key Columns

As stated above, a Match Rows compare type is a special compare type that uses "key" columns. For a Match Rows compare, the compare process will use key columns to first, find a matching row in the Left and Right compare ranges that have the same value in this column, and then compare the remaining values in that row.

Below depicts an example of a Match Rows compare using key columns and how the compare process works:



In the above Match Rows compare, the column "Rep ID" is specified as the Key column that the compare process should use to find matching rows in the Left and Right compare ranges. As the compare process moves through the rows in each range it will try to find the same value for Rep ID on both sides. The example depicts that the compare process has found a match on the value "Rep2" in the Rep ID column in both the Left and Right ranges. After finding the match, the compare process continues and compares the remaining cells for Rep2 to check for differences. A new Last Name is found for Rep2 along with a new Sales number and Last Sale date. Rep4 and Rep5 were similarly be matched by the compare process, with no differences found for Rep5.

Using column keys, the compare process also found added (A), removed (R), Duplicate in the Left Range (DL) and Duplicates in the Right Range (DR).

Depending on your compare data, you can specify more than one key column (up to 3). For example, you could specify both Rep ID and Last Name as key columns to match rows which indicates that the value in the 1st key column (Rep ID) combined with the value in the 2nd column (Last Name) should be used by the compare process to match before comparing the remaining cells. TIP: try this in the app using the sample compare to view how this changes the compare results.

Compare Options

Below depicts various compare options that allow you to customize the compare process and the compare results. The app will automatically select popular options but you can use the "Save as favorites" link to save your favorite options to re-use when creating and running additional compares in this this workbook.

The first 2 options are the most important:

Compare Type

  • Match Rows use key column(s) to first match rows in the Left and Right compare ranges and then compare the remaining cells in the row (see above Columns and Keys section
  • Cell-by-Cell compare the Left and Right ranges cell-by-cell from top/left to right/bottom

Merge Type

  • Manually identify all differences and the result sheet and the task pane and, optionally, use the task pane to help resolve and merged the differences.
  • Automatically automatically resolve and merge the differences using the Right Range values (to use the Left Range values, swap the ranges and rerun)
  • None show the differences in the sheet and blanks for non-differences.

TIP:  The best way to observe how the various compare options work is to use the Create Sample Compare button on the home screen and then after the sample compare has run change the options from the Options menu and then re-run the compare.


Sheet Options

Sheet options which allow you to control how and where Compare and Merge will write the compare results to a workbook sheet. Popular options are automatically selected but you can use the "Save as favorites" link to save your favorite options to re-use when creating and running additional compares in this this workbook.

When running or rerunning a compare, if the sheet name you entered already exists and contains compare results, you can choose to create a new sheet or reuse the sheet and replace existing compare results. Creating a new sheet for the compare results is safer since it will preserve existing results from a previous compare run. Choosing to create a new sheet will append a number to the entered sheet name similar to how Excel functions -- sheet1, sheet2, etc.

IMPORTANT:   use caution when choosing to replace previous compare results as previous results will be deleted and replaced with the new results. You CANNOT undo or restore deleted compare results (unless you are able to restore a previous version of the workbook).

Running Compares

Compare Process

Running a compare executes the compare process which does the following:

1) retrieves data the Left and Right compare ranges

2) runs the compare engine on the ranges based on the Compare Type, Match Rows or Cell-by-Cell, and the compare options selected

3) writes the compare results to the sheet and to the "Results" view of the Compare and Merge app in the task pane


How to run a compare:

Run button from the Start view guide steps.
Run command from the Results view to rerun the current compare, perhaps with new options or because the Left or Right compare range data has changed.
Run button from the Compares view to run a previosly saved compare. You can save a Compare on the Results view using the Save button. Saved Compare's are saved to the workbook so you can run them at a later time.

Compare Results

Analyzing the Results

The compare process examines the Left and Right ranges and compares cells by looking at both the type and the value of the data in the cell. For a Match Rows compare, this happens after the matching rows are identified. If the type of data in the two cells is the same then the cells can be compared. The following table shows how differences are displayed in the results.

Compared Data Results
Number type in both cells The difference value between the cells, formatted with the number format of the Right range. Ex.   

The "How to display differences" for numbers option for can be used to also show the Left and Right values in the compare result cell.
Date type in both cells The difference value between the cells, formatted as "# yrs, # days" or "# hours" or the actual number difference (days) depending on the "Date Labels" option. Exs.   

The "How to display differences" for dates option can be used to also show the Left and Right values in the compare result cell.
Note: when merging the Task Pane will always show the formatted difference even if the results table shows the actual number.
Text type in both cells Text differences are indicated by Text Diff

The "How to display differences" for text option can be used to also show the Left and Right values in the compare result cell.
Note: the difference in the text can also be seen in the Task Pane when merging.
Data types are different Cell type differences are indicated by Type Diff
Note: you can still manually merge this difference in the Task Pane and select the preferred Left or Right cell value.

The "How to display differences" for text option can be used to also show the Left and Right values with the type difference displayed in the compare result cell.
Error in one or both cells Error in one or both cells are indicated by Error Diff
Note: you can still manually merge this difference in the Task Pane and select the appropriate Left or Right cell value.

The "How to display differences" for text option can be used to also show the Left and Right values with the error(s) displayed in the compare result cell.
Added Row Added rows, those in the Right range but not in the Left range, are colored in green and indicated in the Diff column with "A".
Removed Row Removed rows, those in the Left range but not in the Right range, are colored in red and indicated in the Diff column with "R".
Duplicate Row in Left Range
(Match Rows Compare Only)
Duplicates in the Left range are colored in light blue and indicated in the Diff column with "DL".
Duplicate Row in Right Range
(Match Rows Compare Only)
Duplicates in the Right range are colored in dark blue and indicated in the Diff column with "DR".

Results

Sheet Results

Left Range

Right Range



Using Task Pane Results

You can use the app Results view in the task pane to help merge/resolve differences in the the compare results. The task pane also contains helpful statistics about the Compare.

Merging / Resolving Differences

Manually resolving differences between two ranges or lists can be a cumbersome process. Compare and Merge makes this easier for you by providing efficient ways to view and navigate the differences and accept the correct values into the results range. When you accept the correct value in the Task Pane it will also update the sheet so that once you finish with the merge process you have the final results in the sheet.

NOTE:   You can bypass merging in the Task Pane and modify the sheet results directly but the Task Pane will not update based on your direct changes in the sheet. In addition, adding or removing rows directly in the sheet will cause the Task Pane to be out of sync with the sheet results and subsequently switching back to the Task Pane will result in errors. Therefore, it is recommended that you merge results using either the Task Pane or directly in the sheet but not both at the same time.

The table below details the controls and options available for merging.

"M"erge button opens a panel which displays the Left and Right values and allows you to select or "accept" which value to include in the merged results range. The values are links so simply click the value to accept it. You can also select the Diff value to cancel the merge for this cell.

Accept button used to accept a row in the results range.
Reject button used to remove a row from the results range.
Display the merge pane as a Grid view. This is ideal for larger screen displays where you can increase the size, or even undock, the Task Pane.
Display the merge pane in a Column view and merge row-by-row. This is ideal for smaller displays where the Task Pane is narrow or when the Task Pane size cannot be adjusted such as Excel Online.
Filters the Task Pane to show only rows that have differences.
Show the Left and Right values for all the cells with differences. Basically this auto-clicks all the M's. This provides a great way to view all the differences and to quickly merge multiple differences.
Set how many rows to display per page. For larger screens, setting this at a higher value allows you to view several differences at once. When the Task Pane is smaller a good option is to set the page size to 1 and use the Column view for merging.

Navigate through the Task Pane -- First, Next, Previous, Last.

Compare and Merge Results Merge Row by Row


My Compares

The My Compares screen contains a list of Compares you have previously saved to the workbook. The Left and Right ranges and all compare options are saved so that you can run the Compare as you last left it. If, however, if the number rows in either or both of the Left and Right compare ranges has changed, the compare process will detect this and use the new amount of rows.

My Compares