Skip to main content

Quickly combine same values/data or duplicate rows in Excel

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:


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.

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.

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.

After merging, you can see the result as follows.


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.

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

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.

After merging, you can see the result as follows.


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.

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

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

After merging, you can see the result as follows.


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.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (10)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
It just spins, nothing happend. Would be a great feature if i could get it to work
This comment was minimized by the moderator on the site
What a great concept...but it doesn't work. I got it to work 2 or 3 times and now it doens't do anything anymore!
This comment was minimized by the moderator on the site
I find this to be the best and the worst tool... sometimes simple combines hang forever.
This comment was minimized by the moderator on the site
I love the tool! Had a 8,000 row spreadsheet - took a few hours, but it worked great. Then I got really bold and tried my much larger spreadsheet - it's been almost 24 hours now and it's still going.
This comment was minimized by the moderator on the site
Still problem for me... Not really efficient !!!
This comment was minimized by the moderator on the site
3 Columns, 1800 rows... still processing.
This comment was minimized by the moderator on the site
8000 rows, 10 columns, still processing after 1.5 hours
This comment was minimized by the moderator on the site
Same thing here, with 50000 rows, has been processing for over 10 hours now,any solutions?
This comment was minimized by the moderator on the site
Hey Matt, I have a sheet with 89000 rows just two columns. I've been here since the cows came home, left again the next day and came home again - the advanced combine rows is still running, and I have an Intel Core i9-9900 processor in my box to boot. I learned my lesson the hard way, and believe the Kutools utility is specifically for small data sets of no more than 1,000 rows. Anything larger than that and you're asking for trouble.
This comment was minimized by the moderator on the site
I have a sheet of 26000 rows just two columns. The advanced combine rows has been running for over 30 min. ??? I tried closing and running it again with no better results. Any thoughts?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations