Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to combine rows with same ID/name in Excel?

Author Kelly Last modified
A screenshot showing a table with data to combine rows by the same ID in Excel

Combining rows with the same ID or name in Excel is a common task for organizing and summarizing data. Whether you're consolidating sales records, summarizing survey results, or organizing datasets, the process can be streamlined using Excel's built-in tools. Here's a detailed guide to help you efficiently combine rows with the same ID or name.


Combine rows with same ID/name by VBA

This method will introduce a VBA to quickly combine all rows with the same ID or name in the specified selection. Please do as follows:

1. Press "Alt + F11" keys to open the Microsoft Visual Basic for Applications window.

2. Click "Insert" > "Module", and then paste the VBA code into the Module window.

VBA: Combine Rows with the Same ID/name in Excel

Sub Consolidate_Rows()
Dim xRg As Range
Dim xRows As Long
Dim I As Long, J As Long, K As Long
On Error Resume Next
Set xRg = Application.InputBox("Select Range:", "Kutools For Excel", Selection.Address, , , , , 8)
Set xRg = Range(Intersect(xRg, ActiveSheet.UsedRange).Address)
If xRg Is Nothing Then Exit Sub
xRows = xRg.Rows.Count
For I = xRows To 2 Step -1
For J = 1 To I - 1
If xRg(I, 1).Value = xRg(J, 1).Value And J <> I Then
For K = 2 To xRg.Columns.Count
If xRg(J, K).Value <> "" Then
If xRg(I, K).Value = "" Then
xRg(I, K) = xRg(J, K).Value
Else
xRg(I, K) = xRg(I, K).Value & "," & xRg(J, K).Value
End If
End If
Next
xRg(J, 1).EntireRow.Delete
I = I - 1
J = J - 1
End If
Next
Next
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

3. Press the F5 key or click the Run button to run this VBA.

4. In the popping out Kutools for Excel dialog box, please select the rows you will combine, and click the OK button. See screenshot:
A screenshot showing the Kutools for Excel dialog box with row selection to combine rows by ID

Now you will see the rows in the specified range are combined with commas by the same ID. See screenshot:
A screenshot showing rows combined in Excel by the same ID with commas separating the values

Note: This VBA can only combine rows by the values in the first column. For example you want to combine rows by the same name, you must move the name column to the first column of the specified range.

Combine rows with same ID/name by Kutools for Excel

Don’t worry if you’re not familiar with VBA coding—Kutools for Excel offers an "Advanced Combine Rows" feature that simplifies the process. With no complicated operations needed, just a few clicks are enough to quickly merge rows with the same ID or name. This powerful tool also supports column merging, summing, counting, averaging, and more, making it the perfect solution for all your diverse data processing needs.

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

1. Select the data range you will combine, and click "Kutools" > "Merge & Split" > "Advanced Combine Rows". See screenshot:
A screenshot showing the Kutools for Excel ribbon with the Advanced Combine Rows option selected

2. In the "Advanced Combine Rows" dialog box, please specify the following operations:

  • Select the ID or name column you will combine other data based on, then select "Primary Key" from the "Operation" drop down;
  • Next, select other column name, and click the drop down list from the "Operation" field, and choose one delimiter from the "Combine" section. Repeat this step for  other columns you want to combine ;
  • Click OK button.
    Tip: If you need to keep the cell formatting when merging, please check "Use formatted values" option.

A screenshot showing the Advanced Combine Rows dialog box

Now you will see the selected rows are combined based on the order ID. See screenshot:
A screenshot showing rows combined in Excel with the results displayed after using Kutools

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

By following these methods, you can easily combine rows with the same ID or name in Excel, saving time and ensuring accuracy. Choose the approach that best fits your data and objectives! If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.


Related articles:

How to concatenate keeping number/date formatting in Excel?

How to concatenate cells from another sheet/different sheets in 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 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
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in