Combine Duplicate Rows and Join Corresponding Values in Excel

When working with large Excel worksheets, duplicate records are very common. For example, the same customer may appear in multiple rows with different products, the same order ID may contain several item names, or the same employee may have multiple project records.
Instead of keeping these duplicate rows separately, you may want to combine them into one row and join the corresponding values into a single cell. This makes your data cleaner, easier to read, and more suitable for reporting or analysis.
This tutorial explains several practical ways to combine duplicate rows and concatenate corresponding values in Excel.

Combine Duplicate Rows and Join Corresponding Values

• Method1: With formulas (Excel 365, Excel 2021 and later versions)
• Method 2: With VBA code
• Method 3: With Kutools for Excel

Tips for Combining Duplicate Rows Correctly

Conclusion

Combine Duplicate Rows and Join Corresponding Values

For example, if the same department appears multiple times with different employee names, you may want to keep one department row and join all corresponding employees together. Manually copying and merging these values can be time-consuming and easy to get wrong, especially when the dataset is large. Fortunately, Excel offers several ways to handle this task. In this section, we will introduce three practical methods to combine duplicate rows and join corresponding values: using formulas, VBA code, and Kutools for Excel for a quicker visual solution.

Method1: With formulas (Excel 365, Excel 2021 and later versions)

If you are using Excel 365, Excel 2021, or a later version, you can combine duplicate rows by using dynamic array formulas such as UNIQUE, FILTER, and TEXTJOIN. This method is formula-based, so the result can update automatically when the source data changes.

Step 1: Extract Unique Values

First, enter the following formula in a blank cell, then, press Enter key, it will extract the unique department names from the original list. See screenshot:

=UNIQUE(A2:A15)

Step 2: Join Corresponding Values

Next, enter this formula:

=TEXTJOIN(", ",TRUE,FILTER($B$2:$B$15,$A$2:$A$15=D2))

Then drag the formula down to apply it to the other unique departments. See screenshot:

=TEXTJOIN(CHAR(10),TRUE,FILTER($B$2:$B$15,$A$2:$A$15=D2))

Method 2: With VBA code

If you are using an older version of Excel or want to generate static results, VBA is a useful solution. With VBA, you can combine duplicate rows and join corresponding values automatically.

This method is especially suitable when you need to process data repeatedly or when your Excel version does not support modern dynamic array formulas.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.

Sub CombineRows()
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
    xvalue = arr(i, 1)
    If Dic.Exists(xvalue) Then
        Dic(arr(i, 1)) = Dic(arr(i, 1)) & " " & arr(i, 2)
    Else
        Dic(arr(i, 1)) = arr(i, 2)
    End If
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

3. Then press F5 key, and a prompt box pops out to remind you to select a range that you need to combine. See screenshot:

4. And then click OK, the values in column B have been combined into one record and separated by spaces. See screenshot:

Dic(arr(i, 1)) = Dic(arr(i, 1)) & " " & arr(i, 2)

Dic(arr(i, 1)) = Dic(arr(i, 1)) & "; " & arr(i, 2)

Dic(arr(i, 1)) = Dic(arr(i, 1)) & vbLf & arr(i, 2)

Method 3: With Kutools for Excel

If you prefer a visual and easier way to combine duplicate rows, Kutools for Excel provides a convenient feature that can merge rows based on duplicate values and combine the corresponding contents without writing formulas or VBA code.

This method is suitable for users who want to quickly process duplicate records through a dialog box.

1. Select the data range that contains duplicate rows.
2. Click Kutools > Merge & Split > Advanced Combine Rows.
3. In the Advanced Combine Rows dialog box:

• Select the column that contains duplicate values, such as Department, set this column as the key column.
• Select the column that contains the values you want to join, such as Employee. And then, choose one combine option from the Combine group, such as Comma, Colon, Semicolon, Space, Nothing, or New line.
• Click OK to apply the settings and combine duplicate rows.

Kutools will combine rows with the same department and join the related employee names into one cell. See screenshot:

Tips for Combining Duplicate Rows Correctly

Conclusion

Combining duplicate rows and joining corresponding values is a common task when cleaning and summarizing Excel data.

• If you are using Excel 365 or Excel 2021, formulas such as UNIQUE, FILTER, and TEXTJOIN provide a flexible and dynamic solution.
• If you are using an older Excel version or want static results, VBA can help automate the process efficiently.
• For users who prefer a simple visual operation without formulas or code, Kutools for Excel offers an easier way to merge duplicate rows and combine related values.

In short, the best method depends on your Excel version, data size, and whether you want the result to update automatically. For modern Excel users, formulas are a great first choice. For repeated or large-scale data processing, VBA and Kutools can save more time and reduce manual work.