Reconcile Your Employee Roster Before Filing 1095-Cs

Record the employer and calendar year at the top of the workbook. Choose a stable internal person identifier and an employer identifier as the comparison key. Preserve the source employee IDs in a crosswalk, especially after a payroll conversion or rehire.

Keep three counts: raw source rows, unique people, and unique person-employer relationships. They answer different questions. A person can have multiple transaction rows, and a person who worked for different employers can legitimately appear in more than one employer packet. The official Form 1095-C recipient instructions explain why a person can receive forms from multiple employers.

Do not delete repeated names until you know what each row represents. The correct action may be consolidation, a crosswalk update, or an employer-assignment review.

Keep original source tabs unchanged during review. Put normalized keys and decisions in separate working columns or tabs so a reviewer can reproduce the comparison.

Use categories that point to a next action: HR-only, payroll-only, benefits-only, duplicate source key, missing employer mapping, conflicting dates, or incomplete history. An unmatched record is an investigation item, not an automatic exclusion.

HR-only could mean an employee had no payment in the exported period. Payroll-only could be a former employee omitted by a current-roster filter. Benefits-only could be a record requiring a different scope review. Ask the relevant owner to establish the actual facts instead of applying one blanket rule.

Review duplicate keys at the correct level. Two rows for the same person and employer can be separate periods that must be combined. Two identical export rows can be accidental duplication. Preserve a reason for every removal and retain the source reference.

Fictional Cedar Slate Company has these distinct internal person keys after source-row cleanup. All figures are fictional.

The union contains 126 people: 112 + 8 + 3 + 2 + 1. HR contains 122, payroll 123, and benefits 116. Those totals reconcile because HR is 112 + 8 + 2, payroll is 112 + 8 + 3, and benefits is 112 + 3 + 1.

The 14 people outside the three-way match require explanations. The reviewer does not simply file 126 forms or discard everyone outside the group of 112. The workbook exposes the questions that must be resolved.

Once the identities match, compare effective periods. Flag a person whose employment history spans the year but whose offer source begins in July, or whose enrollment history stops at a system-conversion date. A successful name match can still conceal missing months.

Use a separate period-completeness column for each required source. Keep source-history gaps distinct from a valid period without employment or coverage. A reviewer should be able to tell whether the packet contains an explicit fact or merely lacks a record.

Give special attention to rehires and transfers because their current records may hide earlier periods. Review the combined timeline before the preparer compresses it into annual form fields.

For each exception, record the source owner, evidence reference, selected action, and actual reviewer. Produce a final bridge from the source union to the reviewed preparation population, with counts for documented consolidations, exclusions, additions, and unresolved items.

Have the preparer return a count of the person-employer records it loaded. Explain any difference before reviewing forms. If an update changes the population, regenerate the bridge so the employer's approval does not rely on an old count.

The downloadable workbook layout gives you tab names and column headers to copy into your own controlled spreadsheet. Start with a small representative subset to confirm the key mapping, then run the complete population and preserve the results with the batch.