How do I reconcile two versions of a shared roster after edits happened in both?

Treat the two edited files as evidence, not as competing files to overwrite. Save an untouched copy of each and label them clearly, such as Roster_A_2025-05-12 and Roster_B_2025-05-12—use your actual dates and names. Create a third working copy for comparison. If possible, also retain the last agreed roster as a reference; it can help distinguish an intentional removal from a record that simply never appeared in one editor's copy.

Before comparing, agree on what makes one person or entry the same record. A stable roster ID is usually more useful than a name, since names can be edited or repeated. If you do not have an ID, choose a careful combination of existing fields and flag uncertain matches for human review rather than merging them automatically. Don't publish the working copy until decisions are recorded.

First compare which identifiers appear in each version; then compare field values for identifiers that appear in both. In Excel Power Query, a full outer join returns rows from both versions, while left- and right-anti joins can isolate records found only on one side. Keep both original queries: the merge creates a result and does not require replacing either input. Make sure the matching columns have the same data type. [S1]

Interpret the result cautiously. An entry found only in A is not automatically an addition: it could be a removal from B, an incomplete copy, or a mistaken match. Use neutral labels such as “only in A” and “only in B” until someone confirms what happened. Likewise, an absent row does not by itself establish why it is absent.

Don't use a blanket rule like “the newest file wins” unless everyone responsible for the roster has agreed that it is appropriate. Compare the specific fields side by side, consult the people who made the edits when possible, and apply the roster owner's established rules. For example, if A changes a person's department and B changes their work location, those may be two compatible edits to retain. If both change the same field to different values, mark it unresolved until an authorized person confirms the intended value.

A compact decision log keeps the reasoning visible:

Record ID | Field | Version A value | Version B value | Decision | Reason | Confirmed by | Date

Example, using placeholders: R-014 | Department | [Value A] | [Value B] | [Value chosen] | [Reason confirmed] | [Approver] | [Date]. For an entry present in only one copy, log whether it is retained, removed, or still under review. The example is a template, not a claim about what your roster contains.

Build the final roster in a separate output copy, using the logged decisions. Check that each identifier appears as intended, unresolved differences are either clearly held back or explicitly flagged, and no unreviewed row was silently dropped. Compare the output against both original versions and the decision log. If someone else owns or maintains the roster, ask them to review ambiguous matches and disputed edits before distribution.

Keep the originals and the log with the working materials according to your team's normal process. The comparison tools can surface differences; they cannot establish which conflicting value is true or authorize a change. A final review should therefore verify decisions, not merely confirm that the merge completed.