Watch our 2 minute video to learn how you can quickly merge duplicate rows into one using Combine Rows Wizard add-in.
Combine Duplicate Rows Wizard for Excel merges data from multiple duplicate rows into one quickly and perfectly. It takes just 3 simple steps to combine all values from identical rows based on a selected key column or several columns. The wizard can join all entries or pull unique data only skipping duplicates and empty cells.
When you look at some tables you get an Excel, you know there should be a simple way to make the data fit for further work. Combining duplicate rows that have unique records is one of such challenges.
My task is not only to simply delete the duplicates. I need to keep the unique records and merge them all to one row. You don't have to play "Find 10 differences to complete the task". Combine Rows Wizard can do this in three simple steps. Like here we have a table with our customers' names and orders. There are different items that belong to the same person on different rows. In the end, I want to have a row per name with the full list of items this person wanted. That's when "Combine Rows Wizard" comes into play.
When I start the add-in by clicking on its icon, it picks the range with all my data. I can change it, of course, if I need to. I'm good with processing the entire table but I prefer to keep the original. So I take the Backup copy option and follow to the next step.
Here I pick the column with the main information which is the Name in my table. This means the add-in will look for the same names to bring the rest of information for this person to 1 row.
I can choose what information to keep on the next step. Here I select columns with the records I want to merge. So the add-in will look through these columns and take all values from different rows that belong to the same name. You can enter your own delimiter for these values or choose among the popular ones in the list. If you have other columns you want to select, you can save some time by using this checkbox. Fields have the same delimiter picked by default. You can keep on with the unique values by taking the Delete duplicates option. Or you can keep all records by unchecking it.
That's it! Click Finish to see all the records merged into 1 row with the name they belong to.
You can give Combine Rows Wizard a try by downloading its evaluation version. It is fully functional.
If you have any questions please write to support@ablebits.com.
Thank you for your attention!
4 responses to "Video how to: consolidate duplicate rows keeping unique data only"
How do I perform this same function in excel 2011. I cannot seem to find the combine rows feature.
Hello Kyle,
I'm afraid our add-in doesn't support Excel 2011 for Mac. If you have Microsoft Excel, you can download a fully-functional trial version of Combine Rows Wizard using this direct link:
https://www.ablebits.com/files/get.php?addin=xl-combine-rows-wizard
I have in column A
1 Cast
2 Crew
3 Hiring
4 Cast
5 Hiring
6 Crew
I need these unique CAST CREW AND HIRING in another column without delete in column A entries.
Ex: I need (after formula)
1 Cast
2 Crew
3 Hiring
Is it possible by formula?
Hello Muveen,
You can get unique values in another column using an array formula, here is our post that describes the steps:
https://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/#get-distinct-values
You can also use Duplicate Remover add-in: just choose to find unique values and copy them to another location. Here is a link to install a fully functional trial version:
https://www.ablebits.com/files/get.php?addin=xl-duplicate-remover-excel
I hope this helps!