How to Combine Multiple Excel Sheets and Remove Duplicate Records in WPS Office
Question details
The user needs to consolidate records from five worksheets into one sheet, remove duplicate rows, and identify incomplete records without manually checking tens of thousands of rows.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has five worksheets with matching Part Number, Description, Machine, and Drawing Number columns and needs a single master sheet for data cleanup.
- Observed behavior
- Manual review is impractical for tens of thousands of rows, and rows with missing fields or extra spaces may appear to be duplicates but are not exact matches.
Confirm all five worksheets use the same header names and column order for Part Number, Description, Machine, and Drawing Number before copying data. If any sheet has a different column order, rearrange it first so Remove Duplicates compares the correct fields.
Combine All Records into One Sheet and Remove Exact Duplicate Rows in WPS Spreadsheets
Use this method to create a single working list and delete only the rows where every selected column matches exactly.
WPS Spreadsheets removes a row only when all selected columns are identical. Rows that look the same may remain because extra spaces, missing values, or different capitalization are treated as different data.
Open WPS Spreadsheets, click the plus icon at the bottom to create a new worksheet, copy the header row once from the first sheet, and then paste the data rows from all five worksheets below it.
Click the first header cell, press Ctrl+Shift+End to select all populated cells in the new sheet, and make sure the header row and all data rows are included.
Go to the Data tab, click Remove Duplicates, and keep all columns checked so WPS considers a row duplicate only when Part Number, Description, Machine, and Drawing Number all match.
Click OK. WPS reports how many duplicate rows were removed; the remaining rows may need further cleanup because only exact matches are deleted.
Highlight Missing and Near-Duplicate Records for Review
Use this after duplicate removal to quickly isolate incomplete rows or entries that differ only by small text issues.
Combine Excel Sheets and Remove Duplicate Records in WPS Office
Open your existing Excel workbook in WPS Office Spreadsheets, consolidate all five worksheet tables into one master sheet, run Data > Remove Duplicates to delete exact duplicate rows, and then use filters and conditional formatting to identify incomplete records. This keeps large datasets manageable without manual review and preserves your .xlsx file format.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains the five worksheets.
- 2. Consolidate the data into one sheet: Create a new worksheet with the plus icon, paste the header row once, and copy the data rows from each of the five sheets below it.
- 3. Remove exact duplicate rows: Select the entire range, click Data > Remove Duplicates, keep all columns checked, and click OK.
- 4. Isolate incomplete and near-duplicate records: Use Data > AutoFilter to show blank cells in Part Number, Description, Machine, or Drawing Number, and apply Home > Conditional Formatting > New Rule > Blanks to highlight missing fields.
Frequently Asked Questions
Does Remove Duplicates work across multiple sheets at once?
No. In WPS Spreadsheets, combine the records into one sheet with a single header row first, then run Data > Remove Duplicates on that range.
Why does Remove Duplicates leave rows that look the same?
WPS removes a row only when all selected columns match exactly. Extra spaces, different capitalization, or blank fields can make rows look identical but not match exactly.
Can I remove duplicates based only on the Part Number column?
Yes. In Data > Remove Duplicates, deselect the Description, Machine, and Drawing Number columns so WPS checks only Part Number for duplicates. Keep all columns checked if you need a full-row match.
How do I find missing values after removing duplicates?
Select the range, go to Home > Conditional Formatting > New Rule > Format only cells that contain > Blanks, or turn on Data > AutoFilter and filter for blank cells in the Part Number, Description, Machine, or Drawing Number columns.
Will WPS Spreadsheets preserve the original Excel formatting?
WPS Spreadsheets preserves common cell formatting and formulas in .xlsx files, but after pasting the rows into the consolidated sheet, confirm the header row and column alignment before running Remove Duplicates.
