How to concatenate unique values in Excel?
When working with spreadsheets, it is common to encounter situations where you need to merge (concatenate) only the unique values from a column, or compile lists that summarize unique entries with their corresponding records. Handling duplicates and presenting summarized information not only tidies your data but also makes reports clearer and more insightful. In Excel, there are several practical ways to achieve these goals, ranging from using built-in functions to leveraging advanced add-ins or custom code. This tutorial provides a detailed exploration of multiple methods to concatenate unique values and to list unique entries alongside their related data. The solutions covered cater to different Excel versions and user preferences, helping you select the best approach for your scenario.
Concatenate only unique values from a column
- By using TEXTJOIN and UNIQUE functions
- By using Kutools AI Aide
- By using User Defined Function
- By using advanced Excel formula (alternative solution)
List unique values and concatenate corresponding values
- By using TEXTJOIN and UNIQUE functions
- By using Kutoolsfor Excel
- By using VBA code
- By using Excel PivotTable with formulas (alternative solution)
Concatenate only unique values from a column
During Excel data analysis, one frequent task is to merge only the distinct entries in a column into a single cell. This can be particularly useful for creating summary reports, avoiding duplicate values in a list, or preparing data for further processing. The choice of method depends on your Excel version, dataset size, and your familiarity with formulas or code. The following methods address different needs, highlight considerations for each, and offer practical tips to ensure correct execution.
Method 1: Using TEXTJOIN and UNIQUE functions
For users of Excel365 and Excel2021, the introduction of the TEXTJOIN and UNIQUE functions makes combining unique values from a column both simple and flexible.
This solution is best when your data column is continuous and you want to quickly consolidate all unique items into a single cell with a chosen delimiter. It eliminates duplicates automatically, is easily auditable, and allows you to change the range or delimiter as needed. However, note that this approach is only available in the latest Excel versions; older versions will not support the UNIQUE function.
In the cell where you want to display the result, enter the following formula (assuming your data is in cells A2:A18):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNIQUE(A2:A18) filters out duplicate entries and returns only the unique values from the range A2:A18.
- TEXTJOIN(", ", TRUE, ...) combines (concatenates) these unique values into one cell, separated by a comma and space. The TRUE argument ensures that any empty cells are ignored in the concatenation.
Helpful tips and troubleshooting:
- Ensure your Excel version supports the UNIQUE and TEXTJOIN functions. If you see a #NAME? error, you may be using an older version.
- The delimiter used in TEXTJOIN can be changed to anything you prefer, such as "; " or "|".
- If you add or remove data in the original range, the formula updates automatically.
- To avoid unintentional extra spaces or delimiters, double-check your delimiter argument in the formula.
Method 2: Using Kutools AI Aide
When you need a quicker, fully automated way to concatenate unique values—without writing formulas—Kutools for Excel’s "AI Aide" tool offers a practical solution that saves time for users of all skill levels. This method is especially helpful if you aren’t familiar with Excel’s advanced formulas or if your data changes frequently, requiring repeated tasks.
After Kutools for Excel is installed, access this feature by clicking "Kutools" > "AI Aide" to open the "Kutools AI Aide" pane.
- Select the cells that contain the values you want to combine into a single cell, ensuring your selection matches your intended data.
- In the chat box, describe your requirement. For example, you can type:
Concatenate unique values with comma from selected range and put the combine result into cell C2 - Press the Enter key or click the "Send" button. The AI analyzes your request and, after processing, press “Execute” for Kutools to perform the operation. The result will be returned as described.
Notes and tips:
- Check that you are running the most recent Kutools version to access all AI features.
- Be specific in your text command for best results; mention delimiter and target cell.
- Kutools AI is particularly efficient for large ranges or for workflows that need to be repeated on various datasets.
Method 3: Using User Defined Function
For users who require advanced flexibility, need custom delimiters, or want a reusable tool for multiple workbooks, coding a User Defined Function (UDF) in VBA is an effective way to automatically concatenate unique values. This VBA solution is compatible with all Excel versions and is not restricted by the availability of new functions.
- You should enable macros in your workbook.
- ave the file as "macro-enabled" (.xlsm) in case you continue to use this VBA code in the future.
- Regular backups of your workbook before running new code are recommended.
1. Hold down ALT + F11 to open the Microsoft Visual Basic for Applications window.
2. In the VBA window, click Insert > Module, then copy and paste the following code:
VBA code: Concatenate unique values into a single cell:
Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
Dim xCell As Range
Dim xDic As Object
Set xDic = CreateObject("Scripting.Dictionary")
For Each xCell In xRg
xDic(xCell.Value) = Empty
Next
ConcatUniq = Join$(xDic.Keys, xChar)
Set xDic = Nothing
End Function
3. Return to your worksheet, and in a blank cell (for example C2), type the following formula:
=ConcatUniq(A2:A18,",")Press Enter to confirm. The cell will display all unique values from the specified range, separated by commas.
- If your range is different, adjust A2:A18 accordingly.
- If a different delimiter is needed, replace "," in the formula with your preferred symbol (e.g., ";" or |).
- If you encounter a #NAME? error, check that macros are enabled and the UDF name matches exactly.
Tip: To reuse this function in other workbooks, copy the VBA code into their modules as well.
Method 4: Using advanced Excel formula (alternative solution)
In environments where the UNIQUE function is not available (for example, in Excel 2016 or Excel 2019), you can still concatenate unique values using a more complex combination of the classic IF, COUNTIF, and TEXTJOIN functions array formulas. This approach works but is best suited for smaller datasets due to its calculation overhead.
1. In a target cell (e.g., C2), enter the following array formula (after input, press Ctrl+Shift+Enter instead of just Enter):
=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))2. If you see curly braces {} appear around your formula, it has been entered correctly as an array formula. The formula will return concatenated unique values from range A2:A18, separated by commas.
Note: This method requires you to adjust the ranges to match your data. For very large ranges, calculation time may increase. If you're not comfortable with array formulas, consider trying the VBA or add-in solutions described above.
List unique values and concatenate corresponding values
Frequently in data reporting, you may want to not only extract the unique values from one column, but also aggregate or join together their corresponding entries from another column. For example, consolidating all products sold by each salesperson, or compiling all entries linked to the same ID. Choosing the right method depends on the complexity of your data and whether automation, ease of use, or compatibility is your priority.
Method 1: Using TEXTJOIN and UNIQUE functions
When you are using Excel 365 or Excel 2021, you can combine the UNIQUE and FILTER functions with TEXTJOIN for a robust, fully formula-driven approach. This method works well for summarizing data where one value may relate to multiple records and you want a delimiter-separated list of those related records.
1. In an empty column, enter the following formula to list all unique values from Column A:
=UNIQUE(A2:A17)
2. Now, to concatenate the corresponding values from Column B for each unique entry, in the next column beside your unique value (e.g., E2 if your unique values start at D2), enter this formula and drag down as needed:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNIQUE(A2:A17) creates an array of unique items from Column A.
- FILTER(B2:B17, A2:A17 = D2) generates an array containing all the corresponding values from Column B for each unique value in D2.
- TEXTJOIN(", ", TRUE, ...) joins these corresponding values, separated by commas.
- If you need a different delimiter, change ", " in TEXTJOIN accordingly.
- To avoid errors, ensure the ranges in your formulas are of equal length and that FILTER does not return errors for missing matches.
- This approach updates results automatically as your data changes, making it suitable for dynamic summary tables.
Method 2: Using Kutools for Excel
Kutools for Excel features an "Advanced Combine Rows" tool purpose-built for grouping data by unique values and merging corresponding values with your delimiter of choice. This is suitable for users who want a graphical solution and aren't comfortable writing formulas or code. It is especially valuable when dealing with large datasets or when frequent regrouping is required, such as in periodic reports or ongoing data maintenance.
Before making changes, it's good practice to back up your data by copying the original data to another location. Then follow these steps:
- Select the range of data you wish to organize.
- Navigate to "Kutools" > "Merge & Split" > "Advanced Combine Rows", as illustrated below:
- In the dialog box that opens:
- Select the column with duplicates to be merged, setting it as "Primary Key" in the "Operation" column.
- Choose the column you wish to aggregate (the values to concatenate); specify your preferred delimiter in the drop-down list under "Operation".
- Click OK to execute.
Result:
Kutools will reorganize your data, extracting unique entries and concatenating all associated values, based on your setup.
- If you make a mistake, use the Undo feature in Excel (Ctrl+Z) to revert.
- The process works for datasets with potentially hundreds or thousands of records, and supports a variety of delimiters.
Method 3: Using VBA code
Using a VBA script provides full control over how you extract and summarize data. This approach is compatible with all versions of Excel, and is especially well-suited for custom workflows, automation, or when functions like UNIQUE or FILTER are unavailable. If your data structure varies frequently, this VBA solution can be easily adapted.
To use the code below, simply follow these steps:
1. Press ALT + F11 to open the VBA editor.
2. Go to Insert > Module, then paste the following code into the opened module window:
VBA code: List unique values and concatenate corresponding data
Sub test()
'updateby Extendoffice
Dim xRg As Range
Dim xArr As Variant
Dim xCell As Range
Dim xTxt As String
Dim I As Long
Dim xDic As Object
Dim xOutputRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "Does not support multiple selections", , "Kutools for Excel"
Exit Sub
End If
If xRg.Columns.Count <> 2 Then
MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
Exit Sub
End If
Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
If xOutputRg Is Nothing Then Exit Sub
xArr = xRg
Set xDic = CreateObject("Scripting.Dictionary")
xDic.CompareMode = 1
For I = 1 To UBound(xArr)
If Not xDic.Exists(xArr(I, 1)) Then
xDic.Item(xArr(I, 1)) = xDic.Count + 1
xArr(xDic.Count, 1) = xArr(I, 1)
xArr(xDic.Count, 2) = xArr(I, 2)
Else
xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
End If
Next
xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub
3. Press F5 to run the script. A pop-up will ask you to select the data range. Make sure you select exactly two columns: the first for unique values, and the second for corresponding values.
4. Click OK, and select the first cell where the result table should begin.
5. After clicking OK, the code will generate a table containing only unique values and their associated concatenated data.
- If you get an error about the number of columns, check that your selection includes only two columns.
- If your delimiter needs to change from a comma to another symbol, adjust the code in the line
xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2)as needed. - Always back up your file prior to running new VBA scripts.
In summary, Excel offers a variety of approaches for concatenating unique values and consolidating related data. Formula methods are fast and dynamic in modern Excel, while VBA and Kutools solutions provide broader compatibility and greater control. Always choose the method suited to your data size, Excel version, and preferred workflow. Remember to test on sample data or back up your work, especially when trying script-based or batch operations. For further Excel guidance and advanced tips, explore our full tutorial collection.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in