Excel Combine Rows Wizard

By Kristin McKinley, Associate Director of Research Administration, Lawrence University

Our Dean of Admissions and Financial Aid recently asked me for information about how to combine multiple rows of data for a given student into a single row within an Excel worksheet. I shared with him two ways that I typically handle this depending on the complexity and size of the worksheet: (1) manually copying and pasting or (2) using formulas, such as concatenating (=CONC). While I have only done this manually a few times, I found it to be extremely time-consuming and more likely to produce errors. This prompted me to look for a new solution to the issue.

I stumbled upon an Excel add-in called Combine Rows Wizard. This three-step, easy-to-use add-in will allow you to merge duplicate rows of data into a single row by choosing a unique identifier(s):

  1. Select the range of cells or entire worksheet.
  2. Choose the key column(s) with your duplicated data.
  3. Specify delimiters.  

This add-in will also allow you to pull unique data by ignoring duplicates and skipping empty cells.

I contacted many Excel users across campus to see if the Combine Rows Wizard was an add-in they might find useful in improving their efficiency. Many were not aware of such an add-in and were using the exact methods I was – manually or via formulas. This feature will free up time to work on higher-level analysis and institutional projects. Research Administration, Admissions, Financial Aid, Development, and Financial Services recently subscribed to this wizard to improve overall efficiency.

Example:

Here is an example of what the data looked like in an Excel file I received from a colleague:

combine-rows1.JPG
 
The file contained two rows of data for a single student (each highlighted in a different color under the ID, or Column A above). For the purposes of demonstration, I will highlight one student:
combine-rows2.JPG
Combine Rows Wizard Steps:
  1. Ablebits is required before adding other wizards.

  2. Download the free trial available at the website. Once installed, the Combine Rows Wizard symbol will appear under Ablebits Data on the ribbon bar. Note: A free 15-day trial is available for the Combine Rows Wizard Add-In. The license is for a lifetime and costs $29.95. Volume discounts are also available.

  3. Open the Excel file and select the entire worksheet or range of cells.

  4. Select Combine Rows icon on the ribbon:
    combine-rows3.JPG
  5. Select your table. You will also have the option to check a box to create a backup copy of the worksheet, which I did. The backup copy is the same name as the worksheet, but has a (2) after it.
    combine-rows4.JPG
  6. Select key column(s). You will have the option to check “my table has headers” and/or “skip empty cells.”
    combine-rows5.JPG
  7. Select columns to merge. You will have the option to “delete duplicate values” and/or “skip empty cells." You will also have the option to select one of four delimiters (comma, space, semicolon, or line break) using a drop-down box. This example shows different types of delimiters being used in various columns. 

    combine-rows6.JPG
  8. Select Finish. Once it is done processing, the following message will appear: 
    combine-rows7.JPG
  9. Select OK. You will now have a single row of data per student now.
    combine-rows8.JPG

Note: You will need to use the Text to Columns wizard under Data in the ribbon and select the delimiter used in step 7 for any given column. However, in the overall scheme of things, this is much more efficient that what I was previously doing.  

These wizards come in very handy and really save a lot of time!

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 15
 
Susan posted on 9/11/2014 3:43 PM
this is amazing! thank you so much for sharing!
Keith posted on 9/11/2014 4:03 PM
I love the screen shots in the examples. This was very intuitive.

Question - Does the software work across multiple spreadsheets/tables within the same document?

We often have data that needs to be combined from multiple pages and this would be a really useful tool to achieve that.
JR posted on 9/11/2014 4:09 PM
I will have to work on using this technique, very helpful, well laid out examples with the screen images really makes this easy to follow and see how the results might look, thank you for this time saver!
Erin posted on 9/11/2014 4:53 PM
I too, think the screen shots were marvelous. Does this also work if you have different numbers of responses? For example, you have 3 responses for student 1, 2 for student 2 and 1 for student 3?
Ashley posted on 9/11/2014 5:01 PM
The screen shots were very helpful. I'm looking forward to trying this out and training others in our department on how to use it, too. Answers to some of the other questions in previous comments would be very much appreciated!
Ishuan posted on 9/11/2014 5:03 PM
This add-in does not seem to allow multiple spreadsheets or tables within the same document. Active cells with values linked to other tables on the same spreadsheet works just fine. Great find!
Ishuan posted on 9/11/2014 5:07 PM
The number of responses does not matter: so if you have 4 responses for a student, it will combine them into one single entry.
Julia posted on 9/11/2014 5:09 PM
This was an easy-to-follow example of how to use this add-in feature in Excel. I will definitely pass this on to my team. Thank you for sharing!
Shawn posted on 9/12/2014 9:09 AM
This is such a useful tip, removes the need to pull duplicative data into other sources to dedup. The step by step instructions are quite useful as well. Thank you!
Lisa posted on 9/12/2014 9:39 AM
Thank you for giving us your insight into a common problem for Excel users. If you're not the programmer, and receive data like this, I can see how this would be a quick and easy solution. Thanks again!
Lu posted on 9/12/2014 10:16 AM
I had written a macro to do this and subsequently the macro was lost. I tried re-writing and have a hiccup that I can't fix. This is so timely and works almost as good as the macro I had. THANK YOU.
Lee posted on 9/12/2014 10:20 AM
Thanks Kristin! I use Excel quite extensively, so it's very helpful to learn about new features and add-ins.
Carolyn posted on 9/12/2014 11:19 AM
This is a great tip. I have the Ablebits license for the merge tables wizard, which operates very similarly. That tool has been very useful and reliable, so I'll consider adding this one!
Kristin posted on 9/12/2014 1:48 PM
I am so glad this tip was of help! In response to Keith's comment, when I used the combine rows wizard all of my data were in a single worksheet. The combine rows wizard did not work across multiple worksheets within the same document; you would have to copy and paste to get everything you wanted into a single worksheet before using the wizard. In response to Erin's comment, the combine rows wizard works no matter how many response you have per student.
Lisa posted on 2/18/2015 10:43 AM
Wow! I'm excited to try this! This will be a great time-saver.