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.
The following formulas help to concatenate the contents of the corresponding cells in a column based on the same value 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.
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.
- 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.
- To apply this feature, please download and install Kutools for Excel first.
- To know more about this feature, take a look at this article: Quickly combine same values or duplicate rows in Excel
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
3. Press the F5 key to run the code, then you will get the concatenated results in specified range.
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!