How to Remove Duplicate Names in Excel TEXTJOIN Formula
Question details
The user needs to combine multiple names (such as modifier, creator, and owner) into a single cell without repeating the same name if a person fulfills multiple roles.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining data from multiple columns or complex lookups into a single comma-separated or semicolon-separated text string.
- Observed behavior
- Standard TEXTJOIN includes all returned values, resulting in duplicate names in the final text string. The goal is to output only unique names.
Ensure you are using a version of Excel or WPS Spreadsheet that supports dynamic array functions like UNIQUE and VSTACK (e.g., Microsoft 365, Excel 2021, or the latest WPS Office).
Combine TEXTJOIN, UNIQUE, and VSTACK Functions
This is the most efficient method to stack multiple values, filter out duplicates, and join them into a single text string.
The VSTACK function allows you to stack multiple arrays or lookups vertically. By wrapping this in the UNIQUE function, you instantly filter out any duplicate names. Finally, TEXTJOIN combines the unique array into a single cell with your chosen delimiter.
Click on the cell where you want the combined, deduplicated names to appear.
Type =TEXTJOIN("; ", TRUE, to start the formula. This specifies a semicolon and space as the delimiter, and TRUE tells the formula to ignore empty cells.
Type UNIQUE( immediately after the comma to ensure only distinct values are returned.
Use VSTACK( to combine your different data sources or lookups. For example, VSTACK(A2, B2, C2) or your specific XLOOKUP formulas.
Close all parentheses and press Enter. A basic example looks like: =TEXTJOIN("; ", TRUE, UNIQUE(VSTACK(A2, B2, C2))).
Use Dynamic Array Functions in WPS Office
WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN, UNIQUE, and VSTACK, allowing you to clean and combine data effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Select the output cell: Click the cell where you want the combined names to be displayed.
- 3. Enter the formula: Type the =TEXTJOIN(", ", TRUE, UNIQUE(VSTACK(...))) formula using your specific cell references.
- 4. Apply the formula: Press Enter to instantly view the deduplicated, combined text string.
Frequently Asked Questions
Why is the UNIQUE function returning a #NAME? error?
The #NAME? error usually occurs if you are using an older version of Excel (like Excel 2016 or 2019) that does not support dynamic array functions. You will need to upgrade to Microsoft 365, Excel 2021, or use a modern alternative like WPS Office.
Can I use a comma instead of a semicolon in TEXTJOIN?
Yes, the first argument in the TEXTJOIN function is the delimiter. You can change "; " to ", " or any other character you prefer to separate the names.
How do I ignore blank cells in my TEXTJOIN formula?
Set the second argument of the TEXTJOIN function to TRUE. This tells the formula to ignore any empty strings returned by your lookups or cell references, preventing extra delimiters in your final text.
What if I don't have the VSTACK function available?
If VSTACK is not available, you can use a contiguous range like UNIQUE(A2:C2) if your data is side-by-side. For non-contiguous data in older versions, you may need to use Power Query to unpivot and remove duplicates before joining the text.
