Compare changes when the main ID repeats
Match records using a main ID and line ID that stay unchanged, then compare other values. A repeated main ID alone cannot identify the right pair.
Use both the main ID and the detail ID when a main ID has several records and both IDs stay unchanged between versions. This example assumes ID+Line identifies the same record on each side. Keep a value that can change, such as Share, out of the JOIN keys and compare it separately.
Identify each record with ID and Line
G1 repeats, but G1+A and G1+B are unique on each side. The After table reverses their order; A still needs to match A.
Before
| ID | Line | Share |
|---|---|---|
| G1 | A | 0.6 |
| G1 | B | 0.4 |
After
| ID | Line | Share |
|---|---|---|
| G1 | B | 0.3 |
| G1 | A | 0.7 |
Join on ID and Line, then compare Share
- Load Before first and After second. Connect their ID columns and choose All rows from both sheets so added and missing records stay visible.
- Add Line as a second JOIN criterion. Both ID and Line must match.
- Connect Share and switch that criterion to Compare values with VS.
- Read Changed columns and the before-and-after values in Result preview. Using Share as a third JOIN key would stop changed records from matching.
What to check in the result
Both rows are Changed: A goes from 0.6 to 0.7 and B from 0.4 to 0.3. If you get ambiguous keys, check that ID+Line is unique within each source.
| Comparison result | Changed columns | Changes | ID | Line | Share | After_ID | After_Line | After_Share |
|---|---|---|---|---|---|---|---|---|
| Changed | Share | Share: 0.6 → 0.7 | G1 | A | 0.6 | G1 | A | 0.7 |
| Changed | Share | Share: 0.4 → 0.3 | G1 | B | 0.4 | G1 | B | 0.3 |
What if ID and Line still repeat?
If the same ID+Line pair appears more than once, add another field that identifies the record or resolve the duplicates in the source. Do not substitute row numbers assigned by position: after sorting, those numbers may pair different records.
If Line itself can change, ID+Line cannot match that record across versions. Prepare an independent, stable record identifier before comparing; the repeated main ID alone does not reveal which old and new detail records belong together.
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
Power Query - Compare two tables with same columns (Reddit r/excel)
This question asks about changes to percentages, text and the secondary ID (ID2) itself. A reply asks how records with different secondary IDs should be paired, and the thread does not establish that matching rule. This fictional example assumes a stable ID+Line pair and compares Share; it does not resolve changing ID2 values and is not a product testimonial.
