How to Compare Multiple Columns and Add Unique Values in WPS Office Spreadsheets
Question details
The user needs to compare values across multiple worksheet columns, identify the values that are unique to the non-target columns, append those unique values below the first column, and then highlight the appended records for easy review.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains data in several columns, and the user wants to consolidate unique values from the other columns into the first column without checking each row manually.
- Observed behavior
- The first column currently contains only its original values. Overlapping values in the other columns are ignored, and appended unique values need a visible highlight to distinguish them from the original list.
Open the workbook in WPS Office Spreadsheets and identify the first column that will receive the unique values. If you use a dynamic array formula, make sure the area below the target output cell is free of existing data.
Create a Combined Unique List with a Dynamic Array Formula
Use this method when you want a separate, automatically updated list that contains the original first-column values plus unique values from the other columns.
This method keeps the original columns intact and places the combined result in a helper column. It works best when you do not need to physically modify the first column.
Click an empty cell where you want the combined list to start, for example F2.
In the formula bar, type =UNIQUE(VSTACK(A2:A100,TOCOL(B2:D100,1))) and press Enter. Replace A2:A100 with the first-column range and B2:D100 with the other columns to compare.
Select the formula spill area, click Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =AND(F2<>"",COUNTIF($A$2:$A$100,F2)=0). Set a fill color and click OK.
Append Unique Values Directly Below the First Column with a Macro
Use this method when you must physically expand the original first column and highlight the appended cells in place.
Use WPS Office Spreadsheets to Compare Columns and Add Unique Values
With WPS Office Spreadsheets, you can use dynamic array formulas and conditional formatting to compare columns, extract unique values, add them below the target column, and highlight the new records without switching to a paid Office suite.
- 1. Open your workbook: Download and install WPS Office from the official website, then open your workbook in WPS Office Spreadsheets.
- 2. Create the unique list: Select an empty output cell and enter a dynamic array formula such as =UNIQUE(VSTACK(A2:A100,TOCOL(B2:D100,1))).
- 3. Highlight new records: Apply Home > Conditional Formatting to the output range with a rule that highlights values not found in the original first-column range.
- 4. Save your file: Review the appended records and save the file as an Excel-compatible XLSX workbook.
Frequently Asked Questions
Does the formula method physically add the unique values below column A?
No, the formula method creates a separate combined list. If you need the original first column to be physically expanded, use the macro method instead.
How do I highlight only the newly added unique values?
Apply conditional formatting to the output range using a formula like =AND(F2<>"",COUNTIF($A$2:$A$100,F2)=0). This highlights values that do not already exist in the original first-column range.
What if my comparison columns contain blank cells?
Use TOCOL(B2:D100,1) inside the formula. The second argument 1 instructs the function to ignore empty cells before unique values are combined.
Why am I seeing a #NAME? error after entering the formula?
A #NAME? error usually means your version of WPS Office Spreadsheets does not support VSTACK, TOCOL, or UNIQUE. Update WPS Office or use the macro method.
