Quickly combine rows based on same value in Excel

Quickly combine same values/data or duplicate rows in Excel

Author: Xiaoyang Last Modified: 2024-11-19

Kutools for Excel

Boosts Excel With 300+
Powerful Features

Suppose you are working with a data set that contains duplicate records in a column, and now you need to combine rows based on the same values in that column and perform some operations (such as merging, calculating) on the cells in the same row of other columns. The Advanced Combine Rows feature of Kutools for Excel can help you solve this problem. With this feature, you can easily achieve:

Combine rows with same values and merge the values of other columns

Combine duplicate rows and sum/count values of other columns

Combine duplicate rows and keep the first or last data of other columns

Click Kutools > Merge & Split > Advanced Combine Rows to enable this feature. See screenshot:

shot-advanced-combine-rows-1

Combine rows with same values and merge the values of other columns

As shown in the screenshot below, this is a sales table containing customers and their corresponding orders in different columns. You can see in the table that the same customer made multiple purchases. Our goal is to merge the purchases of the same customer into one row, separate by commas. Let's see how to handle this task with the Advanced Combine Rows feature.

shot-advanced-combine-rows-2

1. Select the entire table (A1:B13 in this case), go to select Kutools > Merge & Split > Advanced Combine Rows to enable this feature.

2. In the Advanced Combine Rows dialog box, you need to configure as follows.

1) The selected range is displayed in the Combine range box. You can change the range if you need to;

2) In the Columns list section, you can see all columns of the selected range is displayed in the list box.

A: Specify a key column to check for duplicates:
In this case, as I need to combine rows based on duplicate values in the Customer column, here I select Customer in the list, click the arrow in the Operation column, and then select Primary Key from the drop-down menu;

B: Specify a delimiter to separate the merged values:
Here I select the column Product, click the arrow in the Operation column, and then select Comma in the Combine group from the drop-down menu.

3) Click the OK button.

shot-advanced-combine-rows-3

Notes:

1) If the selected range has headers, the My data has headers box will be checked automatically.

2) To keep the cell formatting (such as date, percentage, currency and so on) after merging, check the Use formatted values box.

3) In this case, if there are blank cells in the product column, additional commas and spaces will appear in the comma-separated list. To avoid this, you need to check the Skip empty cells box.

4) If you don’t want to display duplicate values in the merged results, check the Delete duplicate values box.

5) In the lower part of the window, you can see two tabs "Preview" and "Example". The Preview tab shows a real-time preview of the merge result for the settings you specified. And the Example tab displays a screenshot of an example of this feature.

3. Then another Advanced Combine Rows dialog box pops up, showing how many rows will be successfully merged. Click OK to close it.

shot-advanced-combine-rows-8

After merging, you can see the result as follows.

shot-advanced-combine-rows-2

Combine duplicate rows and sum/count values of other columns

As shown in the screenshot below, this is a sales table containing customers and their corresponding orders and sales in different columns. You can see in the table that the same customer made multiple purchases. Our goal is to merge the purchases of the same customer into one row and calculate the total amount for each customer separately.

shot-advanced-combine-rows-4

1. Select the entire table (A1:C13 in this case), go to select Kutools > Merge & Split > Advanced Combine Rows to enable this feature.

2. In the Advanced Combine Rows dialog box, you need to configure as follows.

1) The selected range is displayed in the Combine range box. You can change the range if you need to;

2) In the Columns list section, you can see all columns of the selected range is displayed in the list box.

B: Specify a delimiter for separating the merged results:
Here I select the column Product, click the arrow in the Operation column, and then select a delimiter you want to use to separate the values (here I select Comma).

C: Perform calculation for a certain column:
In this case, I need to sum the sales for the same customer. Here I select Sale in the list, click the arrow in the Operation column, and then select Sum in the Calculate group from the drop-down menu.

3) Click the OK button.

shot-advanced-combine-rows-5

Notes:

1) If the selected range has headers, the My data has headers box will be checked automatically.

2) To keep the cell formatting (such as date, percentage, currency and so on) after merging, check the Use formatted values box.

3) In this case, if there are blank cells in the product column, additional commas and spaces will appear in the merged values. To avoid this, you need to check the Skip empty cells box.

4) For the Sales column, you can also perform other calculations, such as averaging, multiplying, counting distinct, etc.

5) If you don’t want to display duplicate values in the merged results, check the Delete duplicate values box.

6) In the lower part of the window, you can see two tabs "Preview" and "Example". The Preview tab shows a real-time preview of the merge result for the settings you specified. And the Example tab displays a screenshot of an example of this feature.

3. Then another Advanced Combine Rows dialog box pops up, showing how many rows will be successfully merged. Click OK to close it.

shot-advanced-combine-rows-8

After merging, you can see the result as follows.

shot-advanced-combine-rows-4

Combine duplicate rows and keep the first or last data of other columns

As shown in the screenshot below, this is a sales table containing customers and their corresponding orders and purchase dates in different columns. You can see in the table that the same customer made multiple purchases. Our goal is to merge the purchases of the same customer into one row, keeping only the first purchase date in the Date column.

shot-advanced-combine-rows-6

1. Select the entire table (A1:C13 in this case), go to select Kutools > Merge & Split > Advanced Combine Rows to enable this feature.

2. In the Advanced Combine Rows dialog box, you need to configure as follows.

1) The selected range is displayed in the Combine range box. You can change the range if you need to;

2) In the Columns list section, you can see all columns of the selected range is displayed in the list box.

B: Specify a delimiter for separating the merged values:
Here I select the column Product, click the arrow in the Operation column, and then select a delimiter you want to use to separate the values (here, I select Comma).

C: Keep the first/last of a column:
In this case, I want to keep only the first purchase date for each customer. Here I select Date in the list, click the arrow in the Operation column, and then select Keep 1st data of column in the Keep group from the drop-down menu.

3) Click the OK button.

shot-advanced-combine-rows-7

Notes:

1) If the selected range has headers, the My data has headers box will be checked automatically.

2) To keep the cell formatting (such as date, percentage, currency and so on) after merging, check the Use formatted values box. In this example, the date formatting is maintained in the date column if this option is checked after the merge.

3) In this case, if there are blank cells in the product column, additional commas and spaces will appear in the merged values. To avoid this, you need to check the Skip empty cells box.

4) If you don’t want to display duplicate values in the merged results, check the Delete duplicate values box.

3. Then another Advanced Combine Rows dialog box pops up, showing how many rows will be successfully merged. Click OK to close it.

shot-advanced-combine-rows-8

After merging, you can see the result as follows.

shot-advanced-combine-rows-6

Notes:

1. This feature supports Undo (Ctrl + Z);

2. This function does not work for filtering data.

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 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.
Kutools for Excel
The functionality described above is just one of 300 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.