How to merge rows in Excel without losing data (2024)

How to merge rows in Excel without losing data

The task: you have a database where each row contains certain details such as product name, product key, customer name and so on. What we want is to combine all the rows related to a particular order like shown below:

There are two ways to achieve the desired result:

• Merge Excel rows using a formula
• Combine multiple rows with Merge Cells add-in

How to combine duplicate rows into one (keeping unique values only)

The task: you have some Excel database with a few thousand entries. The values in one column are essentially the same while data in other columns are different. Your goal is to combine data from duplicate rows based on a certain column, making a comma separated list. Additionally, you may want to merge unique values only, omitting duplicates and skipping empty cells.

The screenshot below shows what we are trying to achieve.

The prospect of finding and merging duplicate rows manually is definitely something you'd want to avoid. Meet the Merge Duplicates add-in that turns this time-consuming and cumbersome chore into a quick 4-steps process.

1. Select the duplicate rows you want to merge and run the Merge Duplicates wizard by clicking its button on the ribbon.
   
2. Make sure your table is selected correctly and click Next. It is wise to keep the Create a backup copy option checked, especially if you are using the add-in for the first time.
   
3. Select the key column to check for duplicates. In this example, we select the Customer column because we want to combine rows based on customer name.

   If you want to skip empty cells, be sure to select this option and click Next.
   

4. Choose the columns to merge. In this step, you select the columns whose data you want to combine data and specify the delimiter: semicolon, comma, space, line break, etc.

   Two additional options in the upper part of the window let you:

   See Also

   GROUP VALUE Definition | Law Insider
   • Delete duplicate values while combining the rows
   • Skip empty cells

   When done, click the Finish button.
   

In a moment, all the data from duplicate rows are merged into one row:

How to repeatedly merge blocks of rows into one row

The task: you have an Excel file with information about the recent orders and each order takes 3 lines: product name, customer name and date of purchase. You would like to merge every three rows into one, i.e. repeatedly merge the blocks of three rows.

The following image show what we are looking for:

If there are only few entries to be combined, you can select each 3 rows and merge each block individually using the Merge Cells add-in. But if your worksheet contains hundreds or thousands of records, you will need a faster way:

1. Add a helper column to your worksheet, column C in our example. Let's name it BlockID, or whatever name you like.
2. Insert the following formula in C2 and then copy it down the column by dragging the fill handle:

   =INT((ROW(C2)-2)/3)

   Where:

   • C2 is the topmost cell in which you enter the formula
   • 2 is the row where the data starts
   • 3 is the number of rows to be combined in each block

   This formula adds a unique number to each block of rows, as shown in the screenshot:
   
   How this formula works: The ROW function extracts the row number of the formula cell, from which you subtract the number of the row where your data start, so that the formula starts counting from zero. For example, our data start in the 2^nd row, so we subtract 2. If your data start, say, in row 5, then you will have ROW(C5)-5. After that, you divide the above equation by the number of rows to be merged and use the INT function to round the result down to the nearest integer.

3. Well, you've done the main part of the work. Now you just need to merge the rows based on the BlockID For this, we will be using the already familiar Merge Duplicates wizard that we utilized for combining duplicate rows:
   • In step 2, choose BlockID as the key column.
   • In step 3, select all the columns you want to merge and pick line break as the delimiter.

   
   In a moment, you will have the desired result:
   

4. Delete the Block ID column since you don't need it any longer and you are done! A funny thing is that we've needed 4 steps again, like in the two previous examples :)

How to merge matching rows from 2 Excel tables without copying / pasting

Task: you have two tables with a common column(s) and you need to merge matching rows from those two tables. The tables may be located in the same sheet, in two different spreadsheets or in two different workbooks.

For example, we have sales reports for January and February in two different worksheets and want to combine them into one. Mind you, each table may have a different number of rows and different order of products, therefore simple copy/pasting won't work.

In this case, the Merge Two Tables add-in will work a treat:

1. Select any cell in your main table and click the Merge Two Tables button on the Ablebits Data tab, in the Merge group:
   
   This will run the add-in with your main table preselected, so in the first step of the wizard you simply click Next.
2. Select the second table, i.e. the lookup table containing the matching rows.
   
3. Choose one or more column columns that exist in both tables. The key columns should contain only unique values, like Product ID in our example.
   
4. Optionally, select the columns to update in the main table. In our case, there are no such columns, so we just click Next.
5. Choose the columns to add to the main table, Feb sales in our case.
   
6. In the final step, you can select additional options depending on how exactly you want to merge data, and click the Finish button. The screenshot below shows the default settings, that work just fine for us:
   

Allow the add-in a few seconds for processing and review the result:

Available downloads

Ultimate Suite 14-day fully-functional version (.exe file)