How to Combine Duplicate Rows and Merge Values with Delimiters in Excel

Manually consolidating duplicate rows in Excel is a notoriously tedious and error-prone process. When managing complex institutional reports-such as those tracking standard funding sources-datasets frequently repeat key identifiers, leading to fragmented views. Implementing a dynamic formula to merge these duplicates with a delimiter grants analysts the ability to synthesize clean, nested lists instantly. The primary stipulation is that this approach requires modern Excel engines supporting TEXTJOIN and UNIQUE. For example, this allows for effortlessly combining multiple grant award codes under a single donor entity. Below, we outline the exact formula configuration to streamline your data.

In data management and analysis, encountering duplicate records is an everyday occurrence. Sometimes, simply deleting these duplicates isn't the right solution. Instead, you may need to consolidate your data by combining duplicate rows and merging their associated values into a single cell, separated by a delimiter (like a comma, semicolon, or line break).

For instance, if you have a list of customers and the individual products they purchased, you might want to transform multiple rows for the same customer into a single row listing all their purchases separated by commas. In the past, this required complex VBA macros. Today, modern Excel functions make this task incredibly simple, though we will also cover classic methods for older versions of Excel.

The Goal: Before and After

To visualize what we are trying to achieve, consider the following raw dataset:

Our goal is to merge the duplicates in the "Employee" column and combine their "Project" values, resulting in this clean table:

Method 1: The Modern Excel Way (TEXTJOIN & FILTER)

If you are using Excel 365 or Excel 2021, you have access to dynamic arrays. This is by far the easiest, most robust way to combine duplicate rows without touch-ups or VBA.

Step 1: Extract Unique Values

First, we need to create a unique list of the items from our duplicate column. We will use the UNIQUE function for this.

Assuming your original data is in cells A2:B6, place this formula in cell D2 to generate a unique list of employees:

=UNIQUE(A2:A6)

This will automatically spill down and display Alice, Bob, and Charlie in column D.

Step 2: Combine the Rows with a Delimiter

Now, in cell E2 (next to the first unique name), enter the following formula:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$6, $A$2:$A$6 = D2))

Drag this formula down for the rest of your unique list. Let's break down how this works:

• FILTER($B$2:$B$6, $A$2:$A$6 = D2): This looks at the Employee column (A2:A6) and extracts only the Projects (B2:B6) where the employee matches the value in D2 (e.g., "Alice"). For Alice, this returns an array: {"Project Alpha", "Project Gamma"}.
• TEXTJOIN(", ", TRUE, ...): This takes the array of filtered projects and merges them. The first argument (", ") is the delimiter. The second argument (TRUE) tells Excel to ignore empty cells.

Bonus: Removing Duplicate Values Inside the Combined Cell

What if Alice worked on "Project Alpha" twice, and you only want to list it once in her combined cell? You can nest the UNIQUE function inside your FILTER function:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER($B$2:$B$6, $A$2:$A$6 = D2)))

Method 2: For Older Excel Versions (Power Query)

If you are using Excel 2016 or 2019, you won't have access to the FILTER or UNIQUE functions. However, you can achieve the exact same result using Power Query (Get & Transform Data), which is built directly into these versions.

1. Select your source data table (A1:B6).
2. Go to the Data tab on the Ribbon and click From Table/Range. This opens the Power Query Editor.
3. Select the column with duplicates (e.g., "Employee").
4. Go to the Transform tab and click on Group By.
5. In the "Group By" window:
   • Ensure "Employee" is selected in the Group By dropdown.
   • Set the New column name to Combined.
   • Set the Operation to All Rows (this is key, do not select Sum or Average). Click OK.
6. You will now see a table column. Go to the formula bar. You will see a formula that looks like this:
   = Table.Group(#"Changed Type", {"Employee"}, {{"Combined", each _, type table [...]}})
7. Modify that formula in the formula bar to extract and combine the text. Change it to:
   = Table.Group(#"Changed Type", {"Employee"}, {{"Combined", each Text.Combine([Project], ", "), type text}})
   Note: Replace "[Project]" with the exact name of your second column.
8. Press Enter. Your table will instantly consolidate!
9. Go to the Home tab and click Close & Load to send the clean data back to a new Excel worksheet.

Method 3: Combining Rows Using a VBA Macro

If you need a solution that works across all versions of Excel and updates at the click of a button, a VBA User-Defined Function (UDF) is an excellent option.

Follow these steps to set up the macro:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the following code into the module window:

Function MergeDuplicates(LookupValue As String, LookupRange As Range, ValueRange As Range, Delimiter As String) As String
    Dim i As Long
    Dim Result As String
    
    For i = 1 To LookupRange.Rows.Count
        If LookupRange.Cells(i, 1).Value = LookupValue Then
            If Result = "" Then
                Result = ValueRange.Cells(i, 1).Value
            Else
                Result = Result & Delimiter & ValueRange.Cells(i, 1).Value
            End If
        End If
    Next i
    
    MergeDuplicates = Result
End Function

Once pasted, close the VBA editor. You can now use this custom function just like a regular Excel formula! In your worksheet, extract your unique list of names using the standard "Remove Duplicates" tool, and then enter the following formula in the adjacent column:

=MergeDuplicates(D2, $A$2:$A$6, $B$2:$B$6, ", ")

This function searches $A$2:$A$6 for the value in D2, finds all matches, extracts their corresponding values from $B$2:$B$6, and joins them using your specified delimiter (", ").

Which Method Should You Choose?

The best approach depends entirely on your version of Excel and your personal preference:

• Choose Method 1 (TEXTJOIN & FILTER) if you are using modern Microsoft 365. It is dynamic, meaning if you change your source data, the combined cells update instantly without needing a manual refresh.
• Choose Method 2 (Power Query) if you are working with extremely large datasets (tens of thousands of rows) or using Excel 2016/2019. Power Query handles heavy lifting and data transformations much faster than complex array formulas.
• Choose Method 3 (VBA) if you need to share the file with users on legacy versions of Excel (like Excel 2010) and want a seamless, formula-like experience without using Power Query.

By mastering these techniques, you can easily clean up messy, repetitive transactional databases and convert them into tidy, reader-friendly summary reports.