Compare Two Excel Workbooks and Export Changed Employee Records
Question details
Compare a master employee database with a daily update file and export records with changed fields for automation.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A daily employee update workbook must be checked against a master workbook using employee IDs.
- Observed behavior
- The desired output is a separate set of records showing changes to fields such as status, manager, or department.
Confirm that each workbook has a unique employee ID column and consistent column names and data types. Decide whether the export should include only changed existing employees or also new and removed employees.
Compare matching employee records with Power Query
Use this method to flag changed fields and load matching employee records to a separate worksheet.
This comparison uses an inner join, so it includes only employee IDs present in both workbooks. New and removed employees require a separate comparison.
In Excel, select Data > Get Data > From File > From Workbook. Import the master and daily update tables into Power Query as separate queries.
In Power Query Editor, select the master query, then choose Home > Merge Queries. Select the daily update query, select the employee ID column in each table, and choose Inner join.
Select the expand button on the merged column. Choose the update's Status, Manager, and Department fields, and keep the column-name prefix so the update fields can be distinguished from the master fields.
Choose Add Column > Custom Column and add a flag such as [Status] <> [Update.Status], replacing Update with the actual merged-column prefix. Repeat for Manager and Department using their corresponding expanded column names.
Filter the flag columns to keep rows where at least one flag is true. For an OR condition, add a custom column combining the three flags with the Power Query operator or, then filter that column to true. Select Home > Close & Load To and load the result to a new worksheet.
Select the results worksheet and use File > Save As to save a copy in the format required by the automation script, such as CSV UTF-8. CSV saves only the active worksheet.

Compare and export employee records in WPS Spreadsheets
Use a formula in WPS Spreadsheets to flag daily records as new, changed, or unchanged, then filter and export the records needed for automation.
- 1. Arrange the data: Open both files in WPS Spreadsheets. Copy the master data to a sheet named Master and the daily data to another sheet. In both sheets, place employee ID, status, manager, and department in columns A through D.
- 2. Add a comparison formula: On the daily sheet, enter this formula in E2: =IF(SUMPRODUCT(--(Master!$A$2:$A$1000=A2))=0,"New",IF(SUMPRODUCT(--(Master!$A$2:$A$1000=A2),--(Master!$B$2:$B$1000=B2),--(Master!$C$2:$C$1000=C2),--(Master!$D$2:$D$1000=D2))>0,"Unchanged","Changed"))
- 3. Fill and check the results: Drag the fill handle from E2 down the daily records. Adjust the master ranges in the formula if the data extends beyond row 1000, and confirm employee IDs are unique.
- 4. Filter the records: Select the daily table and choose Data > Filter. Use the filter in column E to show Changed records; include New if your automation also needs new employees.
- 5. Export the filtered data: Copy the filtered rows to a new workbook, paste as values, and use File > Save As to save the workbook as CSV or another format accepted by your script.

Frequently Asked Questions
How do I identify which employee fields changed?
In Power Query, expand the update fields after merging and use Add Column > Custom Column to compare each master field with its update counterpart. A true flag indicates that the two values differ.
Will an inner join find new or removed employees?
No. An inner join keeps only IDs found in both files. Use a full outer join and check which side has a missing ID to identify new and removed employees.
How can I update the comparison when a new daily file arrives?
Update the source file or query source path, then select Data > Refresh All in Excel. Check that the new file uses the expected table structure and column names.
Why might matching values appear to have changed?
Different data types, extra spaces, capitalization, or blank values can affect a comparison. In Power Query Editor, check column data types and use Transform options such as Format > Trim where appropriate.




