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 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.

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.

Tip: Before applying this tool, please install Kutools for Excel firstly. Go to free download now.

• 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

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.

Easily concatenate cells if same value with Kutools for Excel

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Read More... Free Download...

---

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!

Read More... Free Download...