How to join texts in a column without duplicates in Excel?
When working with lists or large sets of data in Excel, you may often need to combine the text contents of an entire column into a single cell—while ensuring that any duplicate values appear only once. Additionally, you might encounter situations where texts in one column need to be concatenated based on a specific value in another column, but still with duplicates removed. Mastering these methods can be very useful for cleaning, summarizing, and reporting data, enabling you to keep your worksheets tidy and easy to interpret. This article explores several practical ways to achieve these goals, and provides detailed, step-by-step guidance for various common scenarios.
Join texts in a column without duplicates with formula
Join texts in a column without duplicates based on another cell value with VBA code
Easily join texts in a column based on another cell value with an amazing tool
More tutorial for combining cells…
Join texts in a column without duplicates with formula
Suppose you have a column of text values in your worksheet and want to merge them into one cell while removing any repeated entries, as demonstrated in the screenshot below. This approach is particularly useful for generating summary fields or creating comma-separated unique lists for reports or dashboards. This method does not require using macros or third-party tools, and is suitable for most Excel versions that support dynamic array functions and TEXTJOIN.
Select a blank cell where you want the combined text to appear. Copy and paste the formula below into your selected cell, then press Ctrl + Shift + Enter if you are using older versions of Excel. In newer versions that support dynamic arrays (Excel365 or Excel2019 and later), simply press Enter:
=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A12, A2:A12,0)=MATCH(ROW(A2:A12), ROW(A2:A12)), A2:A12, ""))
Explanation of parameters and usage notes:
- A2:A12: This defines the range of cells whose values you wish to join. Adjust this range as needed to fit your data.
- ", ": This is the separator used between combined texts (in this example, a comma and space). For other separators such as a semicolon or line break, simply change this argument.
- TRUE: This setting tells TEXTJOIN to ignore empty cells.
After completing these steps, you will get a single cell that contains all unique text values from your specified column, joined together according to the separator you set.
Join texts in a column without duplicates based on another cell value with VBA code
There are cases where you want to concatenate text in one column based on the unique values of another column—for instance, listing all projects assigned to a team member, or topics covered in each session, without any text repeated. The built-in Excel functions can be limiting or cumbersome for these tasks, especially as data grows. VBA provides a flexible way to automate this process, even in scenarios where you need to merge large numbers of rows by group, while ignoring duplicates. The following VBA solution allows you to quickly achieve this and export the results to a new worksheet, keeping your source data intact for safety and review.
1. In the worksheet that contains the texts you intend to combine, press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the Visual Basic window, click Insert > Module to create a new module, and then paste the following code into the module's code window:
VBA code: Join texts in a column without duplicates based on another cell value
Sub JoinTextsWithoutDuplicates()
'Updated by Extendoffice 20190924
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 xValue
Dim xStr, xStrValue As String
Dim xB As Boolean
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
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
xStrValue = xArr(I, 2)
xB = True
For Each xStr In Split(xArr(xDic.Item(xArr(I, 1)), 2), ",")
If xStr = xStrValue Then
xB = False
Exit For
End If
Next
If xB Then
xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
End If
End If
Next
Sheets.Add.Cells(1).Resize(xDic.Count, 2).Value = xArr
End Sub
3. To run the code, simply press the F5 key. In the dialog that pops up (titled Kutools for Excel), select the range containing both the grouping column and the texts to be joined. The range should be two columns wide (for example, the first column with a category, and the second column with the associated text values). Click OK to confirm your selection and continue.
Once the code completes, you will find that a new worksheet has been added to your workbook. Here, the unique values from your key column are listed, and next to each, the corresponding texts from your original column are joined—each value appearing only once. This is particularly efficient for summarizing grouped data, such as gathering customer comments per product, listing projects per manager, and more.
Easily join texts in a column based on another cell value with an amazing tool
Before applying Kutools for Excel, please download and install it firstly.
This method lets you interactively configure your summary and merging operations as needed, making it ideal for users who frequently work with variable data sets or need to perform the operation multiple times with different parameters.
Note: Since this utility modifies your selected range directly, it is highly recommended to back up your original worksheet data before performing the combine operation. This helps prevent accidental data loss or overwriting of important information.
1. Select the cell range that includes both the values you want to join by and the texts to be merged. Then, go to the Kutools tab, click Merge & Split, and select Advanced Combine Rows from the dropdown menu.
2. In the Advanced Combine Rows dialog box that appears:
- Choose the column that determines how your texts are grouped, and click Primary Key. This is usually the identifier or category column.
- Select the column containing the texts you wish to merge, then click Combine > Comma (or choose your preferred separator).
- If there are additional columns, such as numeric values or other attributes, you may specify operations for them (for instance, sum totals or calculate averages) by selecting the corresponding function, such as Calculate > Sum.
- Click OK to execute the operation, and the data will be combined accordingly. See the following screenshot for an example:
After the process, you'll observe that all text values in your specified column have been joined together according to the grouping of your chosen key column. This approach is especially advantageous for users who require a quick, repeatable solution without memorizing formulas.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Related articles
Combine duplicate rows and sum the values in Excel
In Excel, you may always meet this problem, when you have a range of data which contains some duplicate entries, and now you want to combine the duplicate data and sum the corresponding values in another column, how could you solve this problem? Try the methods in this article.
Combine Multiple Cells Into A Cell With Space, Commas or other separators In Excel
When you combine the cells from multiple columns or rows into a single cell, the combined data may be separated by nothing. But if you want to separate them with specified marks, such as space, commas, semicolon or others, how can you do? This tutorial will introduce some methods for you.
Concatenate cells keeping number/date formatting in Excel
You may have noticed that when we apply the CONCATENATE function in Excel, the date format (2014-01-03) and number format such as currency format ($734.7), percentage format (48.9%), decimal places (24.5000), etc. are removed and show as general numbers in the concatenation results. In this article we come up with several workarounds to concatenate data with keeping date or number formatting in Excel.
Reverse the concatenate function in Excel
As we all known, the Concatenate function in Excel can help us to combine multiple cells into one cell, but, sometimes, you want to do the opposite of Concatenate function, it means not combine cells, but split cell value into multiple separated cells. Methods in this article will help solve the problem.
Concatenate cells if same value exists in another column in Excel
This article provides methods to help you easily concatenate cells if same value existing in another column in Excel.
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