Use a Dynamic Ending Row in an Excel TEXTJOIN Formula for Grouped Values
Question details
Combine all values belonging to the same group with TEXTJOIN while avoiding a manually fixed ending row.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The group identifier is in column C and the values to combine are in column D. The user wants the combined result to appear only on the first row of each group.
- Observed behavior
- A dynamic array formula can filter matching records for the current group and join them with a chosen delimiter.
Make sure the group IDs in column C and the values in column D begin on row 2 and use matching data ranges. This formula requires a spreadsheet version that supports the FILTER and TEXTJOIN functions.
Use TEXTJOIN and FILTER to combine every value in a group
Use this formula when group IDs are in column C and the values you want to combine are in column D.
The IF check suppresses repeated results. FILTER returns all column D values whose group ID matches the current row, and TEXTJOIN combines those values with a semicolon and space.
Click cell E2, or the first cell in your output column next to the source data.
Type or paste =IF(C2=C1,"",TEXTJOIN("; ",TRUE,FILTER($D$2:$D$1000000,$C$2:$C$1000000=C2,""))) and press Enter.
Drag the fill handle from E2 down the output column, or double-click the fill handle if the adjacent data column is continuous.
Check column E. Each group’s combined values appear on its first row, while later rows with the same ID remain blank.
Use an Excel Table to manage expanding source data
Use this approach when you regularly append records and want formulas to use structured column references instead of large fixed ranges.
Combine grouped spreadsheet values dynamically in WPS Spreadsheet
WPS Spreadsheet lets you build a TEXTJOIN and FILTER formula to collect matching values by group without manually changing the final row each time your data grows. Enter the formula in the first output cell and fill it down to return one combined result for each group.
- 1. Open the worksheet in WPS Spreadsheet: Open your .xlsx workbook in WPS Office and select the worksheet containing the group IDs and values.
- 2. Choose the output cell: Click E2, or select the first empty cell in the column where you want the joined result to appear.
- 3. Insert the formula: Enter =IF(C2=C1,"",TEXTJOIN("; ",TRUE,FILTER($D$2:$D$1000000,$C$2:$C$1000000=C2,""))) and press Enter.
- 4. Copy the result down the column: Drag the fill handle from the selected formula cell down the required rows to calculate each group result.
Frequently Asked Questions
Why does the TEXTJOIN result appear only on the first row of each group?
The leading IF(C2=C1,"",...) condition compares the current group ID with the prior row. When they match, it returns a blank; when they differ, it calculates the joined result.
Can I change the semicolon between joined values?
Yes. Replace "; " in TEXTJOIN with another delimiter, such as ", ", " | ", or CHAR(10) for line breaks. If you use CHAR(10), enable Wrap Text from Home > Wrap Text to display each value on a new line.
What happens if a group has blank values in column D?
The TRUE argument in TEXTJOIN tells the formula to ignore empty values. The group can still be evaluated, but blank value cells are not added to the joined text.
How can I avoid using the $D$2:$D$1000000 range?
Convert the source range to an Excel Table using Insert > Table, then use structured references such as Table1[Value] and Table1[Group]. Table columns expand automatically as records are added.
Why does FILTER return a function error in my spreadsheet?
FILTER is available only in spreadsheet versions that support dynamic array functions. Update your spreadsheet application, or use an alternative approach based on INDEX, MATCH, helper columns, or Power Query if FILTER is unavailable.
