How to Combine Unique Values in Excel Using TEXTJOIN

Consolidating spreadsheet data often results in redundant, cluttered text strings that require tedious manual cleanup. While traditional methods like standard concatenation or copy-pasting offer a basic starting point, they fail to filter out duplicate entries. Combining the TEXTJOIN and UNIQUE functions grants users the ability to generate clean, distinct lists automatically. To leverage this, it is crucial to note the stipulation that this solution requires Excel 365 or 2021. For example, you can effortlessly merge a repeating list of regional sales representatives into one comma-separated cell. Below, we will examine the formula structure and provide a step-by-step guide to mastering this technique.

Data clean-up and reporting are two of the most common tasks performed in Microsoft Excel. Frequently, you will find yourself with a list of data containing duplicate entries, and your goal is to merge these items into a single, comma-separated list of unique values.

Before the introduction of modern Excel functions, accomplishing this task required complex VBA macros, convoluted array formulas involving INDEX, MATCH, and COUNTIF, or manual extraction using the "Remove Duplicates" tool. Fortunately, Microsoft introduced dynamic array functions that have changed the game. By combining TEXTJOIN with UNIQUE, you can create elegant, fully dynamic formulas that instantly consolidate data without duplicates. This comprehensive guide will explore how to master this formula combination, cover advanced use cases like conditional filtering, and provide troubleshooting tips.

Understanding the Core Functions

To understand how the combined formula works, we must first break down the individual roles of its two primary components: TEXTJOIN and UNIQUE.

1. The TEXTJOIN Function

Introduced in Excel 2016 (and refined in Excel 365), TEXTJOIN concatenates a list or range of text strings using a specified delimiter. Unlike the old CONCATENATE function or the ampersand (&) operator, TEXTJOIN allows you to specify a delimiter once and easily skip empty cells.

Syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: The character(s) you want to place between each text value (e.g., a comma and a space ", ").
• ignore_empty: A boolean value. If TRUE, Excel will skip empty cells in the range. If FALSE, Excel will include empty cells, resulting in consecutive delimiters.
• text1, text2, ...: The text strings, ranges, or arrays of values you want to combine.

2. The UNIQUE Function

Introduced in Excel 365 as part of the dynamic arrays engine, UNIQUE extracts a list of distinct values from a range or array, automatically filtering out duplicates.

Syntax:

=UNIQUE(array, [by_col], [exactly_once])

• array: The range or array from which you want to return unique values.
• by_col: [Optional] A logical value. Use FALSE (or omit) to compare rows; use TRUE to compare columns.
• exactly_once: [Optional] A logical value. Use TRUE to return only values that occur exactly once; use FALSE (or omit) to return all distinct values.

The Basic Combination: TEXTJOIN + UNIQUE

When you nest the UNIQUE function inside TEXTJOIN, Excel first extracts the unique list of items as an array in its memory, and then TEXTJOIN glues those items together into a single cell, separated by your chosen delimiter.

Basic Syntax:

=TEXTJOIN(", ", TRUE, UNIQUE(range))

Step-by-Step Example

Imagine you have a list of fruit orders in column A, and you want to summarize all unique fruits ordered into a single cell.

To combine these unique fruits, you would enter the following formula in your summary cell:

=TEXTJOIN(", ", TRUE, UNIQUE(A2:A7))

How Excel processes this formula:

1. UNIQUE(A2:A7) evaluates to the in-memory array: {"Apple"; "Banana"; "Orange"; "Grapes"}.
2. TEXTJOIN takes this array, ignores any blank spaces (since ignore_empty is set to TRUE), and connects them using a comma and a space.
3. Result: Apple, Banana, Orange, Grapes

Sorting Your Combined List

Often, a consolidated list is much easier to read if it is alphabetized. You can easily achieve this by nesting the SORT function inside your formula. This arranges the unique values before they are concatenated.

Formula Syntax:

=TEXTJOIN(", ", TRUE, SORT(UNIQUE(range)))

Using our previous fruit example, if you run:

=TEXTJOIN(", ", TRUE, SORT(UNIQUE(A2:A7)))

The output changes to: Apple, Banana, Grapes, Orange (alphabetized).

Advanced Scenario: Combining Unique Values Based on Criteria

In real-world business scenarios, you rarely want to combine an entire column. Instead, you typically need to aggregate unique values based on a specific condition. For example, you might want to list all unique products purchased by a specific client, or list all unique team members assigned to a specific project phase.

To do this, we incorporate the FILTER function into our formula toolkit.

Formula Syntax:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(range_to_return, criteria_range = criterion, "")))

Note: The empty string "" at the end of the FILTER function acts as a fallback argument in case no matching values are found, preventing a #CALC! error.

Conditional Example

Consider the following table containing project assignments:

If you want to list the unique employees assigned to Project Alpha, your formula would look like this:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B7, A2:A7 = "Project Alpha", "")))

How Excel processes this dynamic formula:

1. FILTER(B2:B7, A2:A7 = "Project Alpha") filters the list to only return: {"Sarah"; "John"; "Sarah"; "Michael"}.
2. UNIQUE(...) takes that filtered list and removes duplicates, leaving: {"Sarah"; "John"; "Michael"}.
3. TEXTJOIN merges those names using the comma separator.
4. Result: Sarah, John, Michael

Handling Pitfalls and Limitations

While this formula combination is incredibly powerful, there are a few limitations and common errors you should keep in mind:

1. Excel Version Compatibility

The UNIQUE, FILTER, and SORT functions are only available in Excel 365, Excel 2021, and Excel for the Web. If you share your workbook with a user running Excel 2019 or older, they will see a #NAME? error because their version of Excel does not recognize these dynamic array functions.

2. Character Limit in Cells

Excel has a limit on the number of characters a single cell can hold, which is 32,767 characters. Furthermore, TEXTJOIN itself has a internal string length limit of 32,767 characters. If your combined unique list exceeds this limit, the formula will return a #VALUE! error. If you anticipate highly massive lists, you may need to filter down your dataset further or use Power Query.

3. Empty Spaces and Zeros

If your source range contains blank cells, the UNIQUE function may interpret these blanks as 0 (zero) or as an empty string. While TEXTJOIN's ignore_empty argument can successfully skip true blanks, it may fail to ignore zeros returned by underlying arrays. To bypass this, you can wrap your range in a filter that specifically excludes blanks:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(A2:A100, A2:A100 <> "", "")))

Conclusion

Mastering the combination of TEXTJOIN and UNIQUE is a monumental upgrade for any Excel user's workflow. It eliminates the need for legacy array formulas and manual "Remove Duplicates" steps. By chaining these functions with SORT and FILTER, you can build elegant, automated dashboards and summaries that instantly update whenever your raw data changes. Whether you are summarizing customer transactions, tracking project resources, or organizing inventory lists, this modern formula combination is an essential tool in your data-handling arsenal.