How to Concatenate Unique Values Without Duplicates in Excel
Question details
The user needs a formula to combine distinct values from a specified cell range into one single cell, filtering out duplicate entries and completely ignoring empty cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating data categories, creating summary reports, or cleaning up unorganized lists into a neat, single-cell text string.
- Observed behavior
- Distinct items from a given data range are successfully extracted and merged into a single text string separated by a designated delimiter.
Ensure your spreadsheet software version supports dynamic array functions like UNIQUE (available in Microsoft 365, Excel 2021, and modern versions of WPS Office), as older versions will return an error.
Use the TEXTJOIN and UNIQUE Functions
This is the most efficient and recommended approach using modern dynamic array formulas to instantly extract and combine distinct values.
By nesting the UNIQUE function inside the TEXTJOIN function, you can automatically filter out duplicate values from an array before combining them. The TEXTJOIN function's built-in feature to ignore empty cells ensures a clean, perfectly formatted text string.
Click on the specific cell where you want the final concatenated text string to be displayed.
Type the formula: =TEXTJOIN(",", TRUE, UNIQUE(A1:A100)). Replace A1:A100 with the actual data range you want to evaluate.
If you prefer a different separator instead of a comma, change the first argument in quotation marks. For example, use "; " or " | ".
Press the Enter key. The cell will now display all unique, non-blank values from your selected range, separated by your chosen delimiter.
Use Power Query for Older Excel Versions
A reliable alternative method for grouping and concatenating data if you are using an older version of Excel that lacks the UNIQUE function.
Easily Concatenate Unique Values with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN and UNIQUE out of the box. You can seamlessly process complex data, extract unique values, and combine them effortlessly without resorting to complex scripts.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open your data file: Launch WPS Spreadsheet and open your existing workbook containing the data you want to consolidate.
- 3. Apply the modern formula: Select an empty cell and type =TEXTJOIN(",", TRUE, UNIQUE(YourDataRange)).
- 4. Get instant results: Press Enter to instantly view your comma-separated unique values without any duplicates.
Frequently Asked Questions
Why does my formula return a #NAME? error when using UNIQUE?
The #NAME? error typically appears if your current spreadsheet software version does not support dynamic array formulas like UNIQUE. Upgrading to a modern suite like Microsoft 365, Excel 2021, or the latest version of WPS Office will resolve this issue.
Can I separate the unique values with a line break instead of a comma?
Yes. Instead of a comma, use the CHAR(10) function as your delimiter. The formula will look like: =TEXTJOIN(CHAR(10), TRUE, UNIQUE(A1:A100)). Make sure to also enable 'Wrap Text' on the result cell so the line breaks display properly.
Is the UNIQUE function case-sensitive?
By default, the UNIQUE function is not case-sensitive. It will treat 'Apple' and 'apple' as the exact same text and only return the first instance it finds. To process case-sensitive duplicates, you would need a more advanced array formula using EXACT, or utilize Power Query.
How can I sort the concatenated unique values alphabetically?
You can add the SORT function into your formula nesting. Simply wrap the UNIQUE function inside SORT like this: =TEXTJOIN(",", TRUE, SORT(UNIQUE(A1:A100))). This ensures your final combined text string is alphabetized.
