Compare and Merge for Excel Help |
Compare Excel data within minutes. Find and resolve differences. Save hours of manual data review.
Compare and Merge is an Excel Add-In and the fastest way to compare data, find differences, and resolve discrepancies. 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.
Free 30-day Trial available from Microsoft AppSource.
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.
Compare and Merge Home |
Feedback / Support: ExcelCompareAndMerge@strivetech.com |
Get started by following the steps presented on the start screen. The process is simple:
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.
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:
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).
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:
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".
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.
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.
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.
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:
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 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.
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
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. |
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
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
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
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". |
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.
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. |
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.