Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Quickly combine same values / same data or duplicate rows in Excel

Supposing you are working with a worksheet which contains multiple duplicate records, and now you need to combine / merge the rows based on the same value and do some calculations, such as sum, average, count of the duplicate rows. With this Advanced Combine Rows of Kutools for Excel, you can quickly combine same values / same data or duplicate rows into appropriate cells.

Combine rows with same values / same data

Combine duplicate rows and sum/average the values

Combine duplicate rows and keep the first or last data of column

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Click Kutools >> Content >> Advanced Combine Rows. See screenshots:

shot advanced combine row 1 -2

Combine rows with same values / same data

Supposing you have a range as shown in the following screenshot, and you want to merge the column Quantity data based on duplicate values in the column Product and column Name.

Original   Final
-2 shot-advanced-combine-7


1. Select the range data that you want to combine duplicates, then apply this Advanced Combine Rows utility by clicking Kutools > Content > Advanced Combine Rows.

2. To select key columns to check for duplicates. Please specify the column Product and column Name as key column to check for duplicate values. Please select the column want to specify as key column and then clicking Primary Key button to set it as key column to check for duplicate values. See screenshot:

shot-advanced-combine-4-4

3. To select column to merge data. Please select the column Quantity and click the Combine button to specify the operation you want to merge the data in this column. For example, we will want to merge the data in column Quantity with separator Comma. See screenshot:

shot-advanced-combine-5 -2 shot-advanced-combine-6

4. Click OK button to apply the operation. See screenshots:

Original   Final
-2 shot-advanced-combine-7

Combine duplicate rows and sum/average the values

Supposing you have a range as shown in the following screenshot, and you want to sum the column Quantity data based on duplicate values in the column Product.

Original   Final
-2 shot-advanced-combine-12

1. Select the data range and apply this Advanced Combine Rows utility by clicking Kutools > Content > Advanced Combine Rows.

2. To select key columns to check for duplicates. Please specify the column Product as key column to check for duplicate values. Please select the column want to specify as key column and then clicking Primary Key button to set it as key column to check for duplicate values. See screenshot:

shot-advanced-combine-9

3. Then select the column to calculate: Please select column Quantity, and click Calculate, and then choose one function from the drop down list as you need, here I select Sum. See screenshot:

shot-advanced-combine-10 -2 shot-advanced-combine-11

4. If you needn’t to set the operations for the rest columns, just keep the default state, then click Ok, and the duplicate values will be merged and their values will be added up. See screenshots:

shot-advanced-combine-3 -2 shot-advanced-combine-12

Note: In Combine, you can sum, average, count and product the values, also can select the maximum and minimum from the duplicate cells.


Combine duplicate rows and keep the first or last data of column

Supposing you have a range as shown in the following screenshot, and you want to get the last data of column Quantity data based on duplicate values in the column Product.

Original   Final
-2 shot-advanced-combine-14

1. Select data and clicking Kutools > Content > Advance Combine Rows.

2. To select key column to check for duplicate: Please specify the column Product as the primary key column. Please select the column want to specify as key column and then clicking Primary Key button to set it as key column to check for duplicate values.

3. To select first or last data: To select column Quantity, then click Keep 1st or Keep Last to keep the first or last data after you combine the duplicate values. see screenshot:

shot-advanced-combine-13

4. Then click Ok, the duplicates will be combined and their first or last value will be kept as well.

Original   Final
-2 shot-advanced-combine-14

Notes:

1. If you check My data has headers option, your headers (the first row) of the range will be ignored when you apply this utility.

2. Use formatted values: it will keep the formatting of the values after combining the duplicate cells.

3. This utility is not available for filtering data.

4. This utility supports Undo.


Demo: Combine multiple rows based on same values and do some calculations

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.