How to combine data in duplicate rows in Excel

 

Combine Rows Wizard for Microsoft Excel

Combining duplicated rows and keeping unique information for the given record


Select a table

  1. Open the Excel workbook with the table where you need to combine matching rows. Then open Combine Rows Wizard by clicking on its icon. Combine Rows Wizard icon When working with the add-in, on the left you can see the step you are on. See the steps you take on the left
  2. You can select your table by clicking the select range icon You can select your table by clicking the select range icon or by typing its address in the Select your table field manually. Type the address of your table in this field

    Tip: Before you start Combine Rows Wizard, select a cell in your table and the whole table will be selected automatically.

    Note! If you have standard Excel auto filter option switched on, the add-in will match only the visible filtered rows. If you need to work with the entire table, turn the filter off before you run the add-in. If you want to work with some part of the table only, apply auto filter to it and then only the visible rows of your table will be processed and combined.

  3. Click the Next button on the Combine Rows Wizard dialog box to continue. Click the Next button on the dialog box to continue

Select key columns

Key columns are the columns where you have duplicate records. You can select one or more columns for the search.

  1. Tick the checkboxes next to the columns with the duplicated values. Tick the checkboxes next to key columns
  2. If you have a lot of columns in your table, take advantage of the Select All button and all the checkboxes in the table with the list of columns will be checked. Then you can deselect those you don’t need to match. Click the Select All button to check all columns
  3. The 1st row content column next to Columns shows the 1st item of each column in your table. It can be useful if your worksheet doesn't have headers so you can see what kind of information is in the columns. See the 1st item of each column in your table in the 1st row content column
  4. On the Select key columns step you can also choose to ignore extra spaces, indicate whether your table has headers or ignore empty cells.
    You should tick the Ignore extra spaces checkbox if your data may have some extra leading or trailing spaces. This option will prevent the add-in from missing the duplicate values. Tick the Ignore extra spaces checkbox if your data may have extra spaces

    Tip: We always recommend using our free tool Trim Spaces for Microsoft Excel before running the add-ins; it will remove all leading and trailing spaces in your table instead of just ignoring them. You can use this direct link to download and install this free add-in: http://www.ablebits.com/files/get.php?addin=xl-trim-spaces.

  5. It is also possible to indicate if there are header rows in your table. As a rule, the add-in indicates headers automatically. If it doesn't, you can check My table has headers. You can also uncheck this option if you want to include the first row into the merge. Pick this option to indicate that your table has headers
  6. On this step you can choose to skip empty cells. Select the Ignore empty cells option and the add-in will not take them into consideration. Select the Ignore empty cells option and the add-in will not merge them
  7. Click on the Next button on the Combine Rows Wizard dialog box to proceed.
    If you need to get back to step 1, press the Back button on the dialog box. Click this button to get back to the previous step

Select columns to merge

On the Select columns to merge step you choose one or more columns with the values that will be joined. You will see a list of columns in the table that were not selected as key columns.

  1. If you have a really big table with numerous columns, you can benefit from the Select All and Unselect All buttons. Use the Select All and Unselect All buttons with big tables
  2. Tick the checkbox(es) next to the column(s) with the data you need to merge. Check the columns you need to merge
  3. Choose a delimiter that will separate the values that are merged. You can either enter your own delimiter or select it from the predefined options.
    When you select a column in the list, you can see an arrow next to the name of the column in the Delimiters section. See an arrow next to the name of the column in the Delimiters section Click on the arrow and pick the delimiter you need to separate the joined values from the drop-down list. You can choose among semicolon, comma, space or line break. Pick the delimiter you need to separate the joined values It is also possible to enter any delimiter of your choice. To do it, click in the Delimiter field next to the column name and type the separator you need.
  4. On this step you can choose to remove duplicate values or skip empty values.
    • Tick the Delete duplicate values checkbox if the columns with the values you need to merge can contain identical items. Tick this checkbox if the values you need to merge can contain duplicates
    • Select the Skip empty values option to avoid merging empty cells which can further result in extra blanks in your spreadsheet. Select the Skip empty values option not to merge empty cells
  5. Then click the Finish button on the dialog box to see the results. Pick the delimiter you need to separate the values you join
  6. See the results of the merging
 
 
 
 
 
 

Please remember that we work from 9am to 6pm GMT this is 5:00am - 2:00pm EST, 2:00am - 11:00am PST and our public holidays may differ from yours.




Microsoft Office Marketplace logo

License Benefits

  • 30-day money-back guarantee
    If not satisfied, you'll get your money back, no questions asked.
  • 1 license for 3 PCs
    You can install 1 license on 3 PCs: at work, at home and on a laptop.
  • Life-time licenses
    There are no annual or renewal fees.
  • Get the product in minutes
    Instant delivery for credit card payments.
  • All forms of payment
    Pay with any major credit card, wire transfer, Purchase Order, PayPal.
  • Discount for schools & NPO
    Contact us to get your coupon code.
  • Volume discounts
    You can save up to 50%.
  • Free upgrades
    All minor updates are free. Major upgrades are free for 1 year, after 1 year - for half price.
  • Code signed with VeriSign
    The add-ins are signed with the VeriSign Code Signing certificate.
  • Easy corporate deployment
    Please see Installing an add-in for several users
  • Free support
    If you need any help, just contact us.
 
Excel Add-ins Collection - quick way to increase your effectiveness in Excel

Related links





Plug-ins for Microsoft Excel and Outlook

Copyright © 2003 - 2012 Add-in Express Ltd. All rights reserved.

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.