Manually consolidating duplicate rows in Excel is a notoriously tedious bottleneck. When tracking diverse funding sources like federal grants and private donations, standard VLOOKUPs often fail to aggregate matching records. This advanced formula method grants immediate clarity, seamlessly merging multiple row values into a single, clean cell. However, as a crucial stipulation, this approach requires modern Excel engines supporting dynamic arrays. By leveraging robust functions like TEXTJOIN, UNIQUE, and FILTER, you can transform fragmented operational data into a unified, audit-ready report. Below, we outline the exact formula syntax and step-by-step implementation to streamline your datasets.

Excel Formula to Combine Rows Based on Match Criteria

Data professionals, analysts, and everyday Excel users frequently encounter a common challenge: a raw data export contains multiple rows of information for a single entity. For example, you might have an invoice table where a single order ID is spread across several rows, with each row containing a different product purchased. Instead of viewing these scattered records, you may want to aggregate, summarize, or consolidate them into a single, clean master row.

Depending on your version of Excel and whether you are dealing with text data or numeric values, there are several powerful formulas and techniques to merge rows based on matching criteria. In this comprehensive guide, we will explore the best methods-ranging from modern dynamic array formulas like TEXTJOIN and FILTER to legacy array formulas and automated alternatives like Power Query.

Understanding the Problem: A Real-World Scenario

Before diving into the formulas, let us establish a visual baseline. Imagine you have the following transactional dataset containing customer orders:

Our goal is to roll this table up so that each unique Order ID appears only once, with all matching items consolidated into a single, comma-separated list in a single cell, like this:

• ORD101: Laptop, Mouse, Monitor
• ORD102: Keyboard, Mouse Pad

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

If you are using Microsoft 365, Excel 2021, or Excel for the Web, you have access to two incredibly powerful functions: TEXTJOIN and FILTER. Combining these two functions is the most efficient, non-programming way to merge text rows based on matching criteria.

The Formula Syntax

=TEXTJOIN(", ", TRUE, FILTER(items_range, criteria_range = criteria_value))

Step-by-Step Implementation

1. Extract Unique Keys: First, create a list of your unique identifiers. In a new column (let's say Column E), use the UNIQUE formula to pull unique Order IDs:
   =UNIQUE(A2:A6)
   This will automatically spill down and generate a unique list of IDs (ORD101 and ORD102).
2. Write the Concatenation Formula: In the adjacent cell (F2), enter the following formula:
   =TEXTJOIN(", ", TRUE, FILTER($C$2:$C$6, $A$2:$A$6 = E2))
3. Drag the Formula Down: Press Enter and drag the formula down to apply it to all unique rows.

How This Formula Works

• FILTER($C$2:$C$6, $A$2:$A$6 = E2): This inner function looks at the range of items ($C$2:$C$6) and filters out only the values where the Order ID in Column A matches the unique ID in cell E2. For ORD101, it returns the array {"Laptop"; "Mouse"; "Monitor"}.
• TEXTJOIN(", ", TRUE, ...): The outer function takes the filtered array, joins the text items together, separates them with a comma and a space (", "), and ignores any empty cells (thanks to the TRUE parameter).

Handling Duplicates Within Your Combined List

Sometimes, raw data contains redundant duplicates. For instance, if Alice bought two Laptops under the same Order ID, your combined list might look like "Laptop, Mouse, Laptop". To eliminate duplicates within your merged cell, nest the UNIQUE function inside your formula:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER($C$2:$C$6, $A$2:$A$6 = E2)))

By wrapping the filtered array inside UNIQUE, Excel filters out duplicate values before joining them into the final text string.

Method 2: Combining Rows in Older Excel Versions (Excel 2016 and 2019)

If you are using Excel 2016 or 2019, you have the TEXTJOIN function, but you do not have access to the dynamic FILTER function. In this scenario, you must utilize an Array Formula using IF instead.

The Legacy Array Formula

Select the target cell and enter the following formula:

=TEXTJOIN(", ", TRUE, IF($A$2:$A$6 = E2, $C$2:$C$6, ""))

Crucial Step: Because this is an array formula in older Excel versions, do not just press Enter. You must press Ctrl + Shift + Enter simultaneously. When done correctly, Excel will automatically wrap the formula in curly braces like this: {=TEXTJOIN(", ", TRUE, IF($A$2:$A$6 = E2, $C$2:$C$6, ""))}.

How It Works

The IF function evaluates the entire array of Order IDs. If a row matches E2, it returns the corresponding item name; if it does not match, it returns an empty string (""). Since the TEXTJOIN function is set to ignore empty values (via its second argument TRUE), it cleanly ignores all the non-matching empty strings, leaving behind only the combined, matching records.

Method 3: Combining and Aggregating Numeric Rows (SUMIFS and AVERAGEIFS)

If your goal is to combine rows based on match criteria to perform mathematical operations (such as summing sales or averaging hours worked), you do not need TEXTJOIN. Instead, use Excel's standard conditional math functions.

For example, if Column C contains a numeric list of transaction amounts, you can use the following formulas in your consolidated summary table:

• Sum of matching values: =SUMIFS($C$2:$C$6, $A$2:$A$6, E2)
• Average of matching values: =AVERAGEIFS($C$2:$C$6, $A$2:$A$6, E2)
• Count of matching instances: =COUNTIFS($A$2:$A$6, E2)

The Ultimate Alternative: Using Power Query

Formulas are incredibly dynamic and update in real-time, but they can slow down significantly when used across thousands of rows. If you are dealing with massive datasets (over 10,000 rows), Excel's built-in Power Query tool is a far more robust solution for combining rows.

How to Combine Rows with Power Query:

1. Select your raw data table.
2. Go to the Data tab on the Ribbon and click From Sheet or From Table/Range. This opens the Power Query Editor.
3. In the Power Query window, select your ID column (e.g., Order ID).
4. Go to the Home tab and click on Group By.
5. In the pop-up window, configure the grouping:
   • Group by: Order ID
   • New column name: Combined Items
   • Operation: Select Sum (or any placeholder option; we will modify this in the formula bar).
   • Column: Item Purchased
6. Once you click OK, locate the formula bar at the top of the editor. It will look similar to this:
   = Table.Group(#"Changed Type", {"Order ID"}, {{"Combined Items", each List.Sum([Item Purchased]), type nullable text}})
7. Change List.Sum([Item Purchased]) to Text.Combine([Item Purchased], ", ").
8. Press Enter. Power Query will instantly merge all text values into clean, comma-separated lists for each unique ID.
9. Go to Home > Close & Load to return the consolidated table back to your Excel workbook.

Summary: Choosing the Right Tool

Depending on your technical constraints and needs, select the correct strategy below:

• For Microsoft 365 / Excel 2021 and text data, use TEXTJOIN with FILTER.
• For Excel 2016 / 2019 and text data, use the Ctrl + Shift + Enter array version of TEXTJOIN and IF.
• To remove duplicate entries from your merged results, nest UNIQUE inside your formula.
• For numbers and metrics, skip text joining and use SUMIFS or AVERAGEIFS.
• For large datasets where workbook performance is critical, rely on Power Query's Group By feature.