Data and spreadsheets
Compare two lists without losing unmatched records
Choose a matching key and check whether it is unique in each list. Assign every source row a reference before comparing, so unmatched and ambiguous entries remain traceable. Keep duplicate-key groups separate for review rather than forcing a match, and reconcile counts for each original list.
A text to start with
I have two lists. List A is [what it contains and where it came from] and List B is [what it contains and where it came from]. The match key should be [field or fields]. Here are anonymous sample rows from each: [rows]. Compare them and label each record as matched, only in A, only in B, conflicting, possible match or duplicate key. Retain each input row reference and assign one review status per row. Keep duplicate-key groups unresolved and preserve unmatched rows. Do not treat similar names as confirmed matches.
Example request. Change the details to fit your situation.List comparison log
List A and B names, versions and row counts: [ ] Match key and matching rule: [ ] Duplicate-key groups in A: [ ] Duplicate-key groups in B: [ ] Source list and row reference: [ ] Key value: [ ] Candidate counterpart row, if any: [ ] Differing field values: [ ] One status: [matched / unmatched / conflicting / possible match / duplicate key] Review note and owner: [ ] Repeat for every input row. A: rows counted once across all statuses: [ ] A: original total and difference: [ ] B: rows counted once across all statuses: [ ] B: original total and difference: [ ] Unresolved groups kept for review: [ ]
Duplicates and fuzzy matches
Check whether the key is unique in each list, because a repeated key can match one record to several and inflate the results. Watch for differences in spacing, capitalization, leading zeros and date formats that stop exact matches. A similar name or address is a candidate for review, not proof of the same person or item, so it needs its own label. When a record matches on the key but differs in another field, report both values and let a person decide. Confirm that the status groups add back up to the total in each list.
Ask another agent to check the result
Check that every input row has a reference and exactly one status. Identify duplicate keys that would create multiple matches and keep those groups unresolved. Flag similar-name matches presented as confirmed. Recount source rows separately for A and B, showing whether the status totals reconcile without double-counting matched pairs.
Decide what happens to each group
Spot check a few records from each status against the source lists. Decide who resolves conflicting entries and which list wins for each field. Send unmatched records to whoever owns the source instead of deleting them. Record the matching rule, the date and the list versions used, so the comparison can be repeated later with the same method.