Skip to main content

How to concatenate cells if same value exists in another column in Excel?

Author: Siluvia Last Modified: 2024-12-10

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.

Screenshot showing a table with concatenated values based on common keys


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.

Screenshot of a formula applied to concatenate values in Excel

2. Then select cell C2, and drag the Fill Handle down to cells you need to concatenate.

Screenshot of dragging the fill handle in Excel to apply concatenation formula

3. Enter formula =IF(A2<>A3,CONCATENATE(A2,",""",C2,""""),"") into cell D2, and drag Fill Handle down to the rest cells.

Screenshot of a concatenation formula result in Excel

4. Select cell D1, and click Data > Filter. See screenshot:

Screenshot showing the filter option in Excel ribbon

5. Click the drop-down arrow in cell D1, uncheck the (Blanks) box, and then click the OK button.

Screenshot of Excel filter dropdown showing the options for filtering blank cells

You can see the cells are concatenated if the first column values are same.

Screenshot of final concatenated results in Excel after filtering

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Enhanced with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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.
  • Screenshot showing Kutools Advanced Combine Rows settings for concatenating cells

Result

Screenshot of concatenated results using Kutools

Note:

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:

1. D1 in the line Set xRg = Range("D1") means that the result will be placed starting with cell D1.
2. "No" and "Combined Color" in the line xRes(1, 1) = "No" and xRes(1, 2) = "Combined Color" are the headers of selected columns. 

3. Press the F5 key to run the code, then you will get the concatenated results in specified range.

Screenshot showing concatenated results using VBA


Demo: Easily concatenate cells if same value with Kutools for Excel

Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

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


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!