How to compare two Excel tables when there is no unique ID
Check whether the same product-and-region requests appear in two files. Compare the columns together to catch differences a product-only check would miss.
If your lists have no request number, compare the details that describe each request. Connect both Item and Region with Compare values in JOIN SHEET. A request appears as shared only when the item and its region match, helping you catch differences that comparing item names alone would miss.
A single-column comparison can hide a difference
The fictional Requests list contains Apples–Seoul, Pears–Busan and Mandarins–Jeju. Confirmations contains Pears–Seoul, Apples–Seoul and Mandarins–Jeju. Comparing only the fruit names suggests that every item exists in both lists. It misses the different destination for Pears.
Compare the fruit and destination together to distinguish the two Pears requests while still matching requests that appear in both lists.
Compare the complete combination
- Load Requests and Confirmations with the item and region in separate columns.
- Connect Item to Confirmed item and choose Compare values.
- Connect Region to Confirmed region and choose Compare values again. Do not add a separate join key for this example.
- Inspect the selected values and Comparison result in Result preview.
Expected result: four combinations
| Item and region | Comparison result |
|---|---|
| Apples–Seoul | In both sheets |
| Mandarins–Jeju | In both sheets |
| Pears–Busan | First sheet only |
| Pears–Seoul | Second sheet only |
Pears–Busan and Pears–Seoul remain separate. This does not prove that the Busan request was changed to Seoul. One request may have been cancelled and a different one created.
Which columns should I include?
Include Size or Color if those values distinguish requests. Do not include an export timestamp merely because it is present: a timestamp that changes on every export can make all records look different.
Check repeated requests too. If Apples–Seoul appears twice in Requests and once in Confirmations, the extra occurrence can remain unmatched. Decide whether those are two real requests before treating one as a duplicate.
If you have a stable, unique request number, join by that ID before comparing changed fields. You can then check whether the same request changed, rather than only finding combinations that differ.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
Comparing two dataframes and getting the differences — Stack Overflow
How to compare two lists with multiple columns and get results from all columns? — Reddit r/excel
The fruit-and-region lists are fictional examples of the multi-column comparison problem.
