How to Remove Duplicate Rows When Serial Numbers Appear in Any Order in WPS Spreadsheets
Question details
The user needs to identify and remove duplicate rows in WPS Spreadsheets that contain the same PC number and LCD serial numbers, even when the serial numbers appear in different columns or in a different order, while preferring complete records over incomplete ones.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Data cleanup or asset reconciliation where each row records a PC number and multiple LCD serial numbers; duplicate detection should ignore the column order of the serial values and keep the most complete row.
- Observed behavior
- A standard row comparison or Remove Duplicates operation treats reordered serial numbers as different values, so duplicate rows are not reliably removed and incomplete duplicates may remain.
Check that the PC number and all LCD serial number cells are in a consistent layout, such as PC number in column A and serial numbers in adjacent columns. Insert two blank helper columns to calculate the normalized key and completeness flag without changing the original data.
Create a normalized serial key with helper formulas and remove duplicates in WPS Spreadsheets
Use this formula-based method when serial numbers may appear in different columns or order and you need a repeatable way to compare the full serial set per PC.
The key idea is to sort the LCD serial numbers within each row before joining them. When the same serial set always produces the same text key, WPS Spreadsheets can compare the key instead of the original column positions. The PC number is placed at the beginning of the key so two rows must belong to the same PC to be treated as duplicates.
In WPS Spreadsheets, click the first available empty column header and type SerialKey, then click the next empty column header and type Complete. This keeps the original PC and serial columns intact.
In the first data row of the SerialKey column, enter a formula that sorts and joins the serial cells. For example, if PC number is in A2 and serials are in B2:D2, use: =A2&"|"&TEXTJOIN("|",TRUE,SORT(TRANSPOSE(B2:D2),,1,TRUE))
In the first data row of the Complete column, enter =IF(COUNTA(B2:D2)=3,"Complete","Incomplete") and copy both formulas down through the data range. Adjust B2:D2 and the count to match your number of serial columns.
Select the entire data range including the helper columns, go to the Data tab, click Sort, sort by Complete in descending order, and then continue with the removal step. This makes WPS keep the complete record when duplicates exist.
With the data range still selected, go to Data > Remove Duplicates, choose only the SerialKey column, and click Remove Duplicates. WPS keeps the first row for each key, which is now the complete row after sorting.
Deduplicate unordered serial rows with a helper key in WPS Office
WPS Spreadsheets can normalize unordered LCD serial numbers into a single key and remove duplicate rows while keeping the most complete record. The workflow uses helper formulas, a completeness sort, and the built-in Remove Duplicates tool.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the file that contains the PC number and LCD serial number columns, and decide which columns hold the serials you need to compare.
- 2. Create a normalized serial key: Add a helper column named SerialKey. In the first data row, enter the formula =A2&"|"&TEXTJOIN("|",TRUE,SORT(TRANSPOSE(B2:D2),,1,TRUE)) and copy it down all rows.
- 3. Mark complete records: Add a helper column named Complete. Enter =IF(COUNTA(B2:D2)=3,"Complete","Incomplete") and copy it down, adjusting the range and count to your serial column layout.
- 4. Sort to keep complete rows first: Select the data range, go to the Data tab, click Sort, sort Complete in descending order, and confirm the sort. Complete records now appear before incomplete records for each match.
- 5. Remove duplicates by the key: Keep the range selected, go to Data > Remove Duplicates, select only SerialKey, and click Remove Duplicates. WPS removes duplicate keys and keeps the first complete row for each PC and serial combination.
Frequently Asked Questions
Why does the normal Remove Duplicates miss unordered serial number rows?
Remove Duplicates compares values in the selected columns by position. If serial S1 and S2 appear in columns B and C in one row but in columns C and B in another, WPS treats them as different. A helper formula that sorts and joins the serial values fixes this by making the same serial set produce the same key.
How do I keep a complete record instead of an incomplete duplicate?
Add a Complete helper column that marks rows with all serial cells filled, sort the data by Complete in descending order, and then run Remove Duplicates using only the SerialKey column. WPS keeps the first row for each key, so the complete row is retained.
What if my WPS Spreadsheets version does not support SORT or TEXTJOIN?
If SORT or TEXTJOIN is unavailable, use helper cells to manually sort the serial numbers within each row and join them with &, for example =A2&"|"&B2&"|"&C2&"|"&D2 after arranging the serial cells in a consistent order. Then use the resulting key with Data > Remove Duplicates.
Can I use this method for more than three LCD serial columns?
Yes. Adjust the range in the formulas to include all serial columns, and update the completeness count from 3 to the total number of expected serial columns. The TEXTJOIN and SORT pattern remains the same.
Will this method work with XLSX and XLS files created in Microsoft Excel?
Yes. WPS Spreadsheets opens Excel XLSX and XLS files and can save back to those formats, so the helper-column deduplication workflow is compatible with files created in Microsoft Excel.
