A good key is stable and specific
A row identifier tells the tool which record in the original belongs with which record in the update. It should stay the same when ordinary values change and should identify at most one row in each sheet. SKU is often useful in a product table. A person’s name is often a poor choice because names can repeat or be corrected.
Do not choose price, status or stock quantity as the identifier when those are the fields you want to inspect. If the identifier changes, the tool sees a removed record and an added record rather than a modification of the same row.
When one column is not enough
An order number usually repeats when an order contains several items. Pairing on order number alone would leave several possible counterparts. Add the line number to identify each item within the order:
| Order | Line | Amount |
|---|---|---|
| A100 | 1 | 50 |
| A100 | 2 | 30 |
Enter Order, Line as the identifying columns in both versions. If line 2 changes from 30 to 35, it will match the correct order line even if it moves to a different row. The first key field on the original side corresponds to the first on the updated side, and the second to the second.
What happens with duplicate or missing values
If either version contains several rows for the same key, the tool records a duplicate-key flag and does not guess which row pairs with which. A missing value in any part of a composite key produces a missing-key flag. These records need review and are not included as normally matched rows.
Review flags count problems, not all affected rows or cells. A duplicate group involving three original rows and two updated rows can be represented by one flag. Read the flag details to see the actual row numbers involved.
Know the matching rules
Keys use the text representation of their values. Numeric 1 and text “1” can match; text “001” remains different from “1”. Spaces and letter case in identifiers matter. The options that ignore spaces or case affect compared cell values, not key matching. Clean inconsistent identifiers in source copies when necessary.
A header’s surrounding spaces are trimmed during header interpretation. Header names must remain unique after that trimming. The key input uses commas as separators, so this release cannot use a header containing a comma as a key. Rename that header in source copies if you need it.
A short validation before every comparison
- Check whether your proposed identifier repeats in either file.
- Check whether any part is blank.
- Confirm that the identifier keeps its meaning across versions.
- Verify a few matches manually, especially after an export from another system.
- Resolve review flags before using totals for operational decisions.
If duplicates are meaningful in your business, add a field that distinguishes them instead of deleting records just to remove a warning. If no stable key exists, this tool may not be the right way to compare those particular exports.