Data Import & Export

Compare Two Excel Workbooks and Export Changed Employee Records

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 869 views

Question details

Compare a master employee database with a daily update file and export records with changed fields for automation.

Compare Two Excel Workbooks and Export Changed Employee Records
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.
Before you start

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.

Solution 1Recommended

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.

1
Import both workbooks

In Excel, select Data > Get Data > From File > From Workbook. Import the master and daily update tables into Power Query as separate queries.

2
Merge on employee ID

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.

3
Expand the update fields

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.

4
Flag each changed field

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.

5
Filter and load the differences

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.

6
Export the result

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 matching employee records with Power Query
Check the comparison rules: Normalize data types and review spaces, capitalization, and blank values before comparing fields. Refresh the query after replacing or updating the source data.
Compare employee updates

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. 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. 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. 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. 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. 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.
Flag employee records using their IDs and key fieldsFilter changed records before exporting a CSVOpen and save Microsoft Excel XLSX workbooksWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.