How to Combine Unique Values from Multiple Excel Cells into One Cell
Question details
The user wants to extract distinct values from a multi-cell data range, ignore blank cells and duplicates, and merge the results into a single text string within one cell.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Consolidating scattered data points or lists from a two-dimensional cell range into a clean, comma-separated summary cell.
- Observed behavior
- A single cell needs to display all unique items from a specified range without manual copying and pasting.
Ensure you are using a recent version of your spreadsheet software (like Microsoft 365, Excel 2021, or the latest WPS Office) that supports modern dynamic array functions such as UNIQUE and TOCOL.
Use TEXTJOIN, UNIQUE, and TOCOL to Combine Values
This is the most efficient method to flatten a 2D range into a single list, remove duplicate entries, and join the remaining unique items into one cell.
The TOCOL function converts a two-dimensional array into a single column. The UNIQUE function then filters out any duplicate values. Finally, the TEXTJOIN function merges these unique items together, separated by a specified delimiter.
Click on the empty cell where you want the combined text string to appear.
Type the formula: =TEXTJOIN(", ", TRUE, UNIQUE(TOCOL(A1:C10, 1))) into the formula bar. Replace A1:C10 with your actual data range.
Press Enter. The cell will now display all unique values separated by a comma and a space.
Sort the Combined Values and Separate with Line Breaks
Use this approach if you need the combined unique values to be sorted alphabetically and displayed on separate lines within the same cell for easier reading.
Process and Combine Data Effortlessly with WPS Office
WPS Spreadsheet provides robust support for modern formulas, making it incredibly easy to process complex data arrays, filter unique values, and merge text without hassle.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data range.
- 2. Select a target cell: Click on the cell where you want the final combined string to be output.
- 3. Enter the formula: Type the combined TEXTJOIN and UNIQUE formula matching your data range.
- 4. Press Enter: Hit Enter to instantly calculate and display the unique values in one single cell.
Frequently Asked Questions
What if my version of Excel doesn't have the TOCOL function?
If you are using an older version that lacks TOCOL, you may need to use Power Query to unpivot your data into a single column first, or use a VBA macro (such as a custom User Defined Function) to collect unique values.
How can I change the separator between combined values?
The first argument in the TEXTJOIN function represents the delimiter. You can easily change ", " to any other character string you prefer, such as " - ", " | ", or even CHAR(10) for a line break.
Why does the formula return a #NAME? error?
The #NAME? error typically occurs if your spreadsheet software version does not support newer functions like UNIQUE, TOCOL, or TEXTJOIN, or if you accidentally misspelled one of the function names.
Does this formula ignore blank cells completely?
Yes. The 'TRUE' argument in the TEXTJOIN function tells it to ignore any empty text strings, and the '1' parameter in the TOCOL function instructs it to specifically ignore blanks before passing the data to the UNIQUE function.
