Log in
x
or
x
x
Register
x

or
0
0
0
s2sdefault

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


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

-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 opertaion you want to merge the data in this column. For example, we will want to merge the data in column Quantity with seperator 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 Quntity, 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 Quntity, 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 200 Advanced Functions for Excel 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

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

Designed for Excel(Office) 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.