Combine Duplicate Rows In Excel

Apr 30, 2020 Highlight both rows that you want to merge. In the Home menu, select Merge & Center from the ribbon. As you can see, this combines the two rows containing the same data into a single row containing one of those duplicates. This is a smart way to clean up your spreadsheets and reduce duplicates throughout the data set.

Select the range data that you want to combine duplicates. Then click Kutools Content Converter Advanced Combine Rows. See screenshot: 3. In the Advanced Combine Rows dialog box, select the column name as the key columns that you want to combine values based on, and then click Key columns. See screenshot: 4. Merge Duplicates Wizard is an add-in for Microsoft Excel specially designed for combining data from duplicate rows into one. Video: How to work with Merge Duplicates; Before you start; How to use Merge Duplicates. Start Merge Duplicates; Step 1: Select your table; Step 2: Choose key columns with duplicate records; Step 3: Pick columns with the. 5 Download the Combine Multiple Rows Example File. It is better to have all the options in a table listed in a comma-separated list rather than broken out across many rows. Unfortunately, Excel doesn’t have a simple function to make this possible. Here is a quick set of data manipulation steps that will build a comma-separated list in a.

If you’ve got several Excel files that you need to merge (or worksheets in a workbook), you might be having a hard time working out how to do it. There are some useful features in Excel such as 'Consolidate' and 'Remove Duplicates' but these often don't quite hit the mark.

The first thing to work out is what type of merge you want to do. What you need from a merge can vary from situation to situation. Maybe you just need all the rows from each spreadsheet into one, consolidated, workbook. Or maybe your needs are more complex and you need to merge spreadsheets that have different formats, de-duplicating rows as you go. Some of the variations are discussed in the following sections so read on to find what you need.

Concatenate rows from multiple spreadsheets into one

This is probably the simplest type of merge where you’d like to add the rows from all the source spreadsheets to a single output spreadsheet. In this situation, all the worksheets to be merged will have the same columns in the same order.

An example of this type of merge is where you have several spreadsheets, each containing a single worksheet with columns A to F populated with:

  • First Name
  • Last Name
  • Email
  • Age
  • Sex
  • Marital Status

Each spreadsheet has this column structure and you’d like to concatenate the rows from each into just one sheet in one spreadsheet.

If this is what you need, you have are a few options, including:

  • Copy & paste the rows from the source spreadsheets to the end of the rows
  • Use a VBA (Visual Basic for Applications) macro to merge the spreadsheets
  • Convert the spreadsheets to CSV files then concatenate them from the command line

Which option you choose really depends on how many spreadsheets you have to merge. More than a few and copy/paste becomes a real pain, as does converting them all to CSV files, so using VBA code may be your best option.

For step by step instructions on how to do each of these, including example spreadsheets, please have a look at How to merge Excel files with the same columns.

Merge spreadsheets with different columns

Excel Combine Duplicate Data

If the spreadsheets you’d like to merge have differing columns, just concatenating them together isn't much use as the data in the output spreadsheet will not be aligned. What you need to do in this case is map the columns from each spreadsheet onto the correct columns in the desired output spreadsheet.

As an example of this situation, imagine you have 3 spreadsheets, each containing a single worksheet:

  • The first spreadsheet has columns A to D populated with: First Name, Last Name, Email, Age
  • The second spreadsheet has columns A to D populated with: Email, First Name, Last Name, Sex
  • The third spreadsheet has columns A and B populated with: Email, Date Of Birth

Here, you’d like to merge the data from each spreadsheet into just one sheet in one spreadsheet with the same set of columns, lets say we want columns A to F to be:

  • First Name
  • Last Name
  • Email
  • Age
  • Sex
  • Date Of Birth

This implies that the column mapping you need to perform is as follows:

Combine Duplicate Rows In ExcelCombine

To achieve this kind of mapping when merging spreadsheets, you will need to use VBA (Visual Basic For Applications). If you’d like to delve further into this approach, you might like to read How to merge Excel files with different columns.

Copy all sheets from separate workbooks into a single workbook

Sometimes, you have a number of separate spreadsheets and you want to copy all of the worksheets from each into single Excel workbook.

For example, you may have one workbook with 2 sheets, another workbook with 7 sheets in and a third with 3 sheets. The data is all related and you simply want to have all the sheets from the separate workbooks into a single workbook.

How To Combine Duplicate Rows In Excel

You have a couple of options for how to do this:

  • Copy & Paste the worksheets into the master spreadsheet
  • Use a VBA (Visual Basic for Applications) macro to merge the worksheets into the master spreadsheet

The copy and paste approach can be a good choice if there aren’t too many worksheets to copy but you’d get pretty tired of it when there are lots of worksheets. VBA code is probably your best bet when you have lots of worksheets to to copy.

Step by step instructions on how to do both of these options are, including example spreadsheets, are covered in How to merge worksheets from multiple Excel workbooks into one.

De-duplication of rows

Taking these approaches a step further, you may also want to de-duplicate the rows in the final spreadsheet. If duplicates can be identified as all the columns having the same values, there are several approaches you can use but if duplicates are identified based on just one or two columns (e.g. email address or first name, last name and date of birth), a good approach is to merge the spreadsheets using VBA. Your VBA code can merge the duplicate rows as each spreadsheet is processed. This is discussed in the section, De-duplicate using VBA code, below.

Remove Duplicates feature in Excel

Under the Data ribbon in the Excel menu, there’s an option called Remove Duplicates. This identifies duplicates as rows where the column values are the same and will remove all duplicate rows except one.

Select all the data in the spreadsheet then click Remove Duplicates and you're done.

Use Advanced Filters

This method uses the built in Advanced Filters functionality of Excel to hide the duplicate rows. Again, this identifies duplicates as rows where the column values are the same. To use this feature, follow the steps below:

Combine Duplicate Rows In Excel Pdf

  • Select all the data you want to hide duplicates in
  • In the Data ribbon in the Excel menu, select the Advanced button in the Sort & Filter section
  • Select the ‘Unique records only’ checkbox and click OK

De-duplicate using VBA code

Combine Like Data In Excel

This is the most flexible of the approaches as you can define how the de-duplication works and which values from each spreadsheet take precedence in the merged row. The concept is based on that discussed above, in Merge spreadsheets with differing columns.

  • You build a matrix mapping the columns from each spreadsheet to the output columns
  • Identify the column(s) which will be used to identify duplicates, the key column(s)
  • In the VBA code, whilst adding rows, lookup the matching key columns in the output spreadsheet. If there is a match, merge the other columns into the matching row as appropriate

Combine Duplicate Rows In Excel Formula

More information and step by step instructions on this technique, including example code and spreadsheets, can be found in How to combine multiple Excel files into one whilst merging row data.

Martin Judd heads up Joined-up Data, a product and service which saves you the headaches of merging and de-duplicating Excel files. If you want more detail on how to merge and de-duplicate Excel files, see The Ultimate Guide on How to Merge Excel Files.