Concatenate Unique Values with a Delimiter in Excel

Manually consolidating duplicated list data into a single, clean cell is a tedious Excel frustration. While traditional methods like CONCATENATE or manual copy-pasting offer basic workarounds, they lack efficiency. Fortunately, leveraging modern formula combinations grants you the power to automate deduplication instantly. Note the stipulation: this streamlined approach requires Excel 365 or 2021 to support dynamic arrays. Whether you are merging duplicate regional sales representatives or consolidating email lists, this technique saves hours. Below, we will break down the exact TEXTJOIN and UNIQUE formula structure to optimize your reporting.

When working with large datasets in Excel, you often need to summarize information by combining text from multiple cells into a single cell. While standard concatenation functions like CONCAT or TEXTJOIN make this easy, they often include duplicate entries if your source data contains repetitive values. Generating a clean, comma-separated list of unique values is a highly sought-after solution for reports, dashboards, and data cleaning.

In this comprehensive guide, we will explore the best formulas and techniques to concatenate unique values with a delimiter in Excel. Whether you are using the modern, dynamic array-enabled Excel 365 or an older legacy version of Excel, you will find a step-by-step solution tailored to your setup.

Method 1: The Modern Excel Way (Excel 365 & 2021)

If you are using modern Excel (Microsoft 365 or Excel 2021), this task is incredibly simple. You can combine two powerful dynamic array functions: TEXTJOIN and UNIQUE.

The Basic Formula

To extract unique values from a range and merge them using a specific delimiter, use the following syntax:

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

How It Works

• UNIQUE(range): This function evaluates the specified range and returns an array containing only the distinct (non-duplicate) values.
• TEXTJOIN(delimiter, ignore_empty, text1, ...): This function takes the array of unique values, joins them together using the defined delimiter (e.g., a comma and a space ", "), and skips any blank cells if the second argument is set to TRUE.

Step-by-Step Example

Imagine you have a list of fruit orders in column A, and you want to create a single-cell summary of the unique fruits ordered.

To get the unique list shown in Column B, enter the following formula in your target cell:

=TEXTJOIN(", ", TRUE, UNIQUE(A1:A6))

Excel will instantly output: Apple, Banana, Orange. If you add or change items in column A, the concatenated list will update automatically.

Sorting the Output

To make your concatenated string even more professional, you can sort the unique list alphabetically before joining them. Simply wrap the UNIQUE function inside the SORT function:

=TEXTJOIN(", ", TRUE, SORT(UNIQUE(A1:A6)))

Method 2: Conditional Unique Concatenation (With Criteria)

In real-world scenarios, you often need to concatenate unique values based on specific conditions. For example, you might want to list the unique products purchased by a specific customer.

To achieve this, we can introduce the FILTER function into our formula stack:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(value_range, criteria_range = criteria)))

Scenario Example

Suppose you have a sales table detailing the Sales Representative and the Product sold:

If you want to list all unique products sold by John, use this formula:

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

How it evaluates:

1. FILTER(B2:B7, A2:A7 = "John") filters the product column, returning: {"Laptop"; "Mouse"; "Laptop"; "Keyboard"}.
2. UNIQUE(...) strips out duplicates, leaving: {"Laptop"; "Mouse"; "Keyboard"}.
3. TEXTJOIN(...) merges them, resulting in: Laptop, Mouse, Keyboard.

Method 3: The Legacy Way (Excel 2019 and Older)

If you are using Excel 2019, 2016, or older, you do not have access to the UNIQUE or FILTER functions. Excel 2019 does have TEXTJOIN, but Excel 2016 and older lack even that. Below are the best alternative approaches for older environments.

Option A: Using a User-Defined Function (VBA Macro)

The most elegant and reusable workaround for legacy Excel is creating a Custom Function (User Defined Function or UDF) using VBA. This macro will work in all versions of Excel that support VBA.

How to Install the VBA Code:

1. Press ALT + F11 to open the Visual Basic for Applications (VBA) Editor.
2. Click Insert > Module from the top menu.
3. Copy and paste the following VBA code into the empty module window:

Function CONCATUNIQUE(RefCell As Range, Optional Delimiter As String = ", ") As String
    Dim Cell As Range
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    
    ' Loop through each cell in the designated range
    For Each Cell In RefCell
        If Cell.Value <> "" Then
            ' Check if value is already in dictionary to ensure uniqueness
            If Not Dict.Exists(Cell.Value) Then
                Dict.Add Cell.Value, True
            End If
        End If
    Next Cell
    
    ' Combine the unique keys using the delimiter
    CONCATUNIQUE = Join(Dict.keys, Delimiter)
End Function

5. Close the VBA Editor and return to your workbook.
6. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm).

How to Use the Custom Function:

Now, you can use this function just like a regular Excel formula. To replicate our first example, write:

=CONCATUNIQUE(A1:A6, ", ")

This VBA script utilizes a "Dictionary" object, which inherently prevents duplicate keys from being added, offering a lightning-fast way to construct your unique, delimited list.

Option B: Using Power Query (No Code Solution)

If you cannot use VBA due to corporate security policies, you can use Power Query, which is built into Excel 2016 and newer (and available as an add-in for Excel 2010/2013).

1. Select your range of data and go to the Data tab, then click From Table/Range.
2. In the Power Query Editor, right-click the header of the column containing duplicates and select Remove Duplicates.
3. Go to the Transform tab and click Group By.
4. In the dialog, set "Operation" to Sum (we will manually change this in the formula bar shortly) and select any column. Click OK.
5. In the formula bar, locate the generated M-code. It will look similar to this:
   = Table.Group(#"PriorStep", {"GroupColumn"}, {{"Count", each List.Sum([TargetColumn]), type text}})
6. Change List.Sum([TargetColumn]) to:
   Text.Combine([TargetColumn], ", ")
7. Click Close & Load to return the clean, unique concatenated table to Excel.

Best Practices & Troubleshooting

• Handling Empty Cells: Ensure the second argument of TEXTJOIN is always set to TRUE. This prevents your output string from having trailing or double delimiters (e.g., "Apple, , Banana").
• Case Sensitivity: Note that Excel's UNIQUE function is case-insensitive (treating "apple" and "Apple" as duplicates). If you require case-sensitive uniqueness, you may need a specialized VBA script or Power Query configuration.
• Character Limit: Remember that Excel cells have a limit of 32,767 characters. If you are concatenating thousands of long strings, you may hit this limit and receive a #VALUE! error.

Conclusion

Simplifying your datasets no longer requires tedious manual sorting and copy-pasting. By pairing TEXTJOIN with UNIQUE and FILTER, you can build dynamic, clean, and automated summaries instantly. For legacy environments, utilizing a lightweight VBA function or deploying Power Query ensures that you can achieve the same clean output across any Excel version.