How to Remove Duplicate Values Within Each Excel Row in WPS Office Spreadsheets
Question details
The user needs to remove repeated entity IDs that appear across multiple columns within each row while keeping only the first occurrence in that row.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Cleaning a table where the same entity ID is repeated across columns in the same row and only the first occurrence should remain.
- Observed behavior
- The standard Remove Duplicates command removes duplicate rows, not individual duplicate values inside each row.
Make sure the data is in a table or named range with a header for each column, and identify which column uniquely identifies each row so duplicates are removed only within that row.
Use Power Query to unpivot, remove duplicates, and pivot row values back into columns
Use this refreshable, formula-free method when you want to keep only the first occurrence of each repeated entity ID in every row.
Power Query lets you turn columns into rows, remove duplicate combinations of the row identifier and the repeated entity ID, and then pivot the cleaned values back into the original column layout. This keeps the first occurrence without deleting entire rows.
In WPS Office Spreadsheets, select the table or range including all columns, go to the Data tab, and choose From Range/Table or Get Data > From Table/Range to open Power Query Editor.
In Power Query Editor, select the column that identifies each row, right-click one of the remaining value columns, and choose Unpivot Other Columns.
Select the row identifier column and the unpivoted value column, then go to Home > Remove Rows > Remove Duplicates. This keeps the first occurrence of each repeated value for the same row identifier.
Select the Attribute column, go to Transform > Pivot Column, choose the value column as the values column, select Don’t Aggregate, and click OK.
Click Close & Load on the Home tab to return the cleaned table to a new or existing worksheet.
Use a VBA macro to clear later duplicate cells within each row
Use this one-click method if you prefer a macro over Power Query and do not need a refreshable query.
Remove Duplicate Values Within Each Row in WPS Office Spreadsheets
Use Power Query in WPS Office Spreadsheets to unpivot rows, remove repeated entity IDs while keeping the first occurrence, and pivot the data back into columns. This gives you a refreshable cleanup that does not delete entire rows.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the Excel workbook that contains duplicate entity IDs, and select the source range.
- 2. Load the range into Power Query: Go to Data > From Range/Table or Get Data > From Table/Range, confirm the headers, and open Power Query Editor.
- 3. Unpivot the value columns: Select the row identifier column, right-click one value column, and choose Unpivot Other Columns.
- 4. Remove duplicates per row: Select the row identifier column and the value column, then click Home > Remove Rows > Remove Duplicates.
- 5. Pivot and load the cleaned result: Select the Attribute column, choose Transform > Pivot Column, select Don’t Aggregate, and click Close & Load.
Frequently Asked Questions
Why does the standard Remove Duplicates command not remove duplicates within a row?
Remove Duplicates compares entire rows or selected column combinations and deletes duplicate records. It does not remove duplicate values that appear in separate cells inside the same row, so individual row cleanup requires Power Query or a row-by-row VBA macro.
Does WPS Office include Power Query for this task?
Recent WPS Office versions include Power Query under the Data tab. If your version does not show Power Query, you can use the VBA method to clear duplicate cells within each row.
Will Power Query keep the first occurrence in each row?
Yes. After you unpivot the values, Remove Duplicates keeps the first occurrence because it processes rows in source order. Pivot the data back to restore the original column layout.
Can I use VBA in WPS Office Spreadsheets to clean duplicates?
Yes. Open the VBA editor from the Developer tab or with Alt+F11, insert a module, and run a macro that tracks values already encountered in each row and clears later duplicate cells.
Does conditional formatting remove duplicate values within a row?
No. Conditional Formatting > Highlight Cells Rules > Duplicate Values only highlights duplicates. Use Power Query or VBA to actually remove or clear the repeated values.
