How to concatenate cells if same value exists in another column in Excel?
As shown in the screenshot below, if you want to concatenate cells in the second column based on the same values in the first column, there are several methods you can use. In this article, we will introduce three ways to accomplish this task.
Concatenate cells if same value with formulas and filter
The following formulas help to concatenate the corresponding cells in one column based on matching values in another column.
1. Select a blank cell besides the second column (here we select cell C2), enter formula =IF(A2<>A1,B2,C1 & "," & B2) into the formula bar, and then press the Enter key.
2. Then select cell C2, and drag the Fill Handle down to cells you need to concatenate.
3. Enter formula =IF(A2<>A3,CONCATENATE(A2,",""",C2,""""),"") into cell D2, and drag Fill Handle down to the rest cells.
4. Select cell D1, and click Data > Filter. See screenshot:
5. Click the drop-down arrow in cell D1, uncheck the (Blanks) box, and then click the OK button.
You can see the cells are concatenated if the first column values are same.
Note: To use the above formulas successfully, the same values in column A must be continuous.
Easily concatenate cells if same value with Kutools for Excel (several clicks)
The method described above requires creating two helper columns and involves multiple steps, which may be inconvenient. If you're looking for a simpler way, consider using the Advanced Combine Rows tool from Kutools for Excel. With just a few clicks, this utility allows you to concatenate cells using a specific delimiter, making the process quick and hassle-free.
1. Click Kutools > Merge & Split > Advanced Combine Rows to enable this feature.
2. In the Advanced Combine Rows dialog box, you just need to:
- Select the range you want to concatenate;
- Set the column with the same values as the Primary Key column.
- Specify a separator to combine the cells.
- Click OK.
Result
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
- To know more about this feature, take a look at this article: Quickly combine same values or duplicate rows in Excel
Concatenate cells if same value with VBA code
You can also use VBA code to concatenate cells in a column if same value exists in another column.
1. Press Alt + F11 keys to open the Microsoft Visual Basic Applications window.
2. In the Microsoft Visual Basic Applications window, click Insert > Module. Then copy and paste below code into the Module window.
VBA code: concatenate cells if same values
Sub ConcatenateCellsIfSameValues()
Dim xCol As New Collection
Dim xSrc As Variant
Dim xRes() As Variant
Dim I As Long
Dim J As Long
Dim xRg As Range
xSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2)
Set xRg = Range("D1")
On Error Resume Next
For I = 2 To UBound(xSrc)
xCol.Add xSrc(I, 1), TypeName(xSrc(I, 1)) & CStr(xSrc(I, 1))
Next I
On Error GoTo 0
ReDim xRes(1 To xCol.Count + 1, 1 To 2)
xRes(1, 1) = "No"
xRes(1, 2) = "Combined Color"
For I = 1 To xCol.Count
xRes(I + 1, 1) = xCol(I)
For J = 2 To UBound(xSrc)
If xSrc(J, 1) = xRes(I + 1, 1) Then
xRes(I + 1, 2) = xRes(I + 1, 2) & ", " & xSrc(J, 2)
End If
Next J
xRes(I + 1, 2) = Mid(xRes(I + 1, 2), 2)
Next I
Set xRg = xRg.Resize(UBound(xRes, 1), UBound(xRes, 2))
xRg.NumberFormat = "@"
xRg = xRes
xRg.EntireColumn.AutoFit
End Sub
Notes:
3. Press the F5 key to run the code, then you will get the concatenated results in specified range.
Demo: Easily concatenate cells if same value with Kutools for 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!