How to merge duplicate cells in excel

Answer

Merging duplicate cells in Excel is possible, but it requires understanding what you want to achieve. Excel does not automatically merge duplicates while keeping all data. The process depends on whether you want to combine text from duplicate cells, highlight duplicates, or keep one cell per duplicate value.

Method 1: Highlight and Remove Duplicates

1. Select the range of cells where duplicates might exist.

2. Go to the Data tab.

3. Click Remove Duplicates in the Data Tools group.

4. Choose the columns where duplicates should be checked.

5. Click OK.

This method does not merge cells, but it deletes repeated entries, keeping only one instance.

Method 2: Merge Duplicate Cells with Concatenation

To combine the contents of duplicate cells in the same row:

1. Suppose column A has duplicate names, and column B has corresponding data.

2. Insert a new column C for combined data.

3. Use the formula: 

   =TEXTJOIN(", ", TRUE, IF(A:A=A2, B:B, ""))

4. Press Ctrl + Shift + Enter for array formula versions in older Excel versions.

5. This will merge all corresponding values in column B where column A is duplicated.

Method 3: Merge Duplicate Cells Visually

1. Sort the data so that duplicate cells are adjacent.

2. Select the range of duplicate cells in a column.

3. Go to the Home tab.

4. Click Merge & Center in the Alignment group.

Important: Excel will keep only the topmost value and discard the other cells’ contents. This method is mostly for visual purposes and not for data consolidation.

Method 4: Use VBA to Merge Duplicates Without Losing Data

1. Press Alt + F11 to open the VBA editor.

2. Click Insert → Module.

3. Paste the code below: 

   Sub MergeDuplicates()

       Dim Rng As Range

       Dim i As Long

       Dim LastRow As Long

       LastRow = Cells(Rows.Count, "A").End(xlUp).Row

       For i = LastRow To 2 Step -1

           If Cells(i, 1).Value = Cells(i - 1, 1).Value Then

               Cells(i - 1, 2).Value = Cells(i - 1, 2).Value & ", " & Cells(i, 2).Value

               Rows(i).Delete

           End If

       Next i

   End Sub

4. Press F5 to run.

5. Column A will have duplicates merged in Column B without losing any content.

Key Notes

• Merging cells with Merge & Center deletes all content except the first cell.

• Concatenation or VBA is safest for preserving all data.

• Always back up your data before merging to prevent accidental loss.

• Sorting duplicates first ensures that all duplicates are adjacent, simplifying the merge.

This method works for both Windows and Mac versions of Excel. On Mac, the keyboard shortcuts differ slightly:

• Mac Merge & Center: Command+Option+M

• VBA editor: Option+F11

Yes · 0 No · 0 Add Comment · 0

Add ·

Report