Claims Diary Merger: Excel Workflow Consolidation

Combined tasks from dual claims systems into a single dynamic calendar

Project Summary

As a claims adjuster managing two separate systems, I spent 15–20 minutes daily cross-referencing diary tasks. This Excel solution merged both calendars automatically using pivot tables and VLOOKUP, creating a unified view of daily workload.

Step 1: Import Raw Data from System A

In the raw claims A sheet, I copy and paste my claims list from claims system A. I only actually use the date column in the code but it is easier to select all of the columns from the claim system when copying and pasting. The extra columns also allow me to glean details about my workload that day as well.

Step 2: Import Raw Data from System B

In the raw claims B sheet, I copy and paste my claims list from claims system B. Similarly to sheet A, I only actually use the date column in the code and the extra columns allow me to glean details about my workload that day.

Step 3: Aggregate Tasks by Date with Pivot Tables

Now that I have a list of claims from each claim system in their own sheet, I created pivot tables for each list to count the number of occurences for each date.

Result: Unified Calendar

Core Formula:
=IFERROR(VLOOKUP(DATE(2025,10,1), PIVOT!$A:$B, 2, FALSE), 0) + IFERROR(VLOOKUP(DATE(2025,10,1), PIVOT!$D:$E, 2, FALSE), 0)
Pulls task counts from both systems and sums them.

Result: November Calendar

The resulting calendar sheet for November.

Result: December Calendar

The resulting calendar sheet for December.