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

Register

or
0
0
0
s2smodern

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

### Kutools for 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

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

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:

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:

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

 Original Final

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

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:

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:

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:

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

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:

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

 Original Final

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

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

Say something here...
symbols left.