Manually consolidating fragmented row data, such as multiple funding streams assigned to a single project ID, is incredibly tedious and error-prone. While standard funding sources like venture capital or traditional loans are typically tracked individually, managing diverse grant portfolios requires unified oversight. Because grants offer non-dilutive capital that accelerates growth, aggregating this specific financial data is crucial. However, a key stipulation is that legacy Excel versions lack the dynamic array capabilities required for modern concatenation. For instance, when tracking complex SBIR grants, manual workarounds fail. Below, we outline how to utilize the TEXTJOIN and FILTER functions to automate your reporting.

Excel Formula To Concatenate Row Values Matching Specific ID

When working with flat data structures in Excel, you will frequently encounter scenarios where a single unique identifier (such as an Order ID, User ID, or Project Code) is associated with multiple rows of data. For example, an order containing several products might list each product on a separate row, repeating the Order ID.

Consolidating these repeating rows into a single summary sheet-where each unique ID has all its corresponding values concatenated into a single, comma-separated cell-is a common business reporting requirement. While older versions of Excel made this task notoriously difficult, modern versions of Excel (Excel 365, Excel 2021, and newer) provide incredibly simple dynamic formulas to handle this instantly.

In this guide, we will explore the best formulas and techniques to concatenate row values matching a specific ID, ranging from modern dynamic array formulas to backward-compatible methods for older Excel versions and Power Query.

Understanding the Scenario

Assume we have the following raw data table representing customer orders (Columns A and B):

Our goal is to create a summary table that lists each unique Order ID and groups all associated products into a single cell, separated by commas:

Method 1: The Modern Standard (TEXTJOIN & FILTER)

If you are using Excel 365 or Excel 2021, this is the easiest, most efficient, and highly recommended formula. It combines the text-merging power of TEXTJOIN with the array-filtering capability of the FILTER function.

The Formula:

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

How It Works:

1. FILTER(B$2:B$7, A$2:A$7=D2): This nested function scans the range A$2:A$7 looking for values that match the ID in D2. It returns an array of matching values from the products column B$2:B$7. For ID 1001, this returns {"Wireless Mouse"; "USB-C Cable"; "HDMI Switch"}.
2. TEXTJOIN(", ", TRUE, ...): This function takes the array generated by the FILTER function and joins the values together.
   • The first argument (", ") is the; separates each value.
   • The second argument (TRUE) instructs Excel to ignore any empty cells within the matched range.

Method 2: Handling Duplicates and Sorting

Sometimes, raw data contains duplicate rows. If a customer bought two USB-C cables under the same Order ID, you might not want "USB-C Cable" to appear twice in your concatenated list. Additionally, you may want to present the items in alphabetical order.

By nesting additional dynamic array functions, we can seamlessly deduplicate and sort our output.

Formula to Remove Duplicates:

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

The UNIQUE function filters out duplicate products from the array before passing them to TEXTJOIN.

Formula to Remove Duplicates and Sort Alphabetically:

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

The SORT function arranges the unique items alphabetically (A to Z) before they are concatenated.

Method 3: The Traditional Way (TEXTJOIN & IF Array Formula)

If you are using Excel 2019, you have access to the TEXTJOIN function, but you do not have access to the FILTER function. To achieve the same outcome, you must use a traditional array formula by pairing TEXTJOIN with an IF statement.

The Formula:

=TEXTJOIN(", ", TRUE, IF(A$2:A$7=D2, B$2:B$7, ""))

Important Step for Excel 2019 Users:

Because this is an legacy array formula, you cannot simply press Enter. After typing the formula into your cell, you must press Ctrl + Shift + Enter. When done correctly, Excel will automatically wrap your formula in curly braces like this: {=TEXTJOIN(", ", TRUE, IF(A$2:A$7=D2, B$2:B$7, ""))}.

How It Works:

The IF statement evaluates every single row in the specified range. If a row in column A matches the ID in D2, it outputs the product from column B. If it does not match, it outputs an empty string (""). The array passed to TEXTJOIN looks like this: {"Wireless Mouse"; ""; "USB-C Cable"; ""; "HDMI Switch"; ""}. Because we set the second argument of TEXTJOIN to TRUE, Excel ignores all the empty strings and concatenates only the matching products.

Method 4: The Legacy Solution using VBA (For Excel 2016 and Older)

If you are using Excel 2016 or earlier, the TEXTJOIN function is not natively available. To accomplish this task without manually updating your sheet, you can create a Custom User Defined Function (UDF) using VBA.

Step-by-Step VBA Setup:

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module from the top menu.
3. Paste the following code into the empty module window:

Function ConcatIf(LookupRange As Range, MatchValue As Variant, TargetRange As Range, Optional;
String = ", ");
String
    Dim i;
Long
    Dim Result;
String
    
    For i = 1 To LookupRange.Cells.Count
        If LookupRange.Cells(i).Value = MatchValue Then
            If TargetRange.Cells(i).Value <> "" Then
                Result = Result & TargetRange.Cells(i).Value & Delimiter
            End If
        End If
    Next i
    
    ' Trim trailing delimiter
    If Len(Result) > 0 Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    
    ConcatIf = Result
End Function
    

Using the Custom Function:

You can now use your custom function just like any native Excel formula. In cell E2, enter:

=ConcatIf(A$2:A$7, D2, B$2:B$7, ", ")

Method 5: The No-Formula Power Query Approach

If you are working with exceptionally large datasets (tens of thousands of rows), writing complex formulas for each row can significantly slow down your workbook's performance. Power Query is Excel's built-in data transformation tool and handles group-and-concatenate tasks efficiently with zero slowdown.

Steps to Concatenate using Power Query:

1. Select your raw data table.
2. Go to the Data tab on the Ribbon and click From Table/Range. This opens the Power Query Editor.
3. Select the Order ID column.
4. Go to the Transform tab and click on Group By.
5. In the "Group By" window:
   • Set the Operation to All Rows.
   • Name your new column (e.g., "TempTable"). Click OK.
6. Go to the Add Column tab and click Custom Column.
7. Name your column "Products" and enter the following M-code formula:
   Text.Combine([TempTable][Product Purchased], ", ")
8. Delete the temporary "TempTable" column as it is no longer needed.
9. Go to the Home tab and click Close & Load to output your summary table to a new Excel worksheet.

Summary: Which Method Should You Use?

Choose your solution based on your dataset size and the Excel version you or your end-users run: