How to Group Rows and Concatenate Values with Dynamic Arrays in Excel
Question details
The user needs to group records by specific identifiers (such as PCODE and BUNDLEID) and concatenate their related quantities using modern dynamic array formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and consolidating raw datasets by grouping related rows together and merging their corresponding values into a single consolidated string.
- Observed behavior
- Producing a dynamically updating array output where duplicate records are grouped by specific keys, and their respective attributes are concatenated into comma-separated text.
Ensure you are using a modern version of Excel or WPS Office that fully supports advanced dynamic array functions such as LET, CHOOSECOLS, REDUCE, and VSTACK. Converting your source data into a formatted Table (Ctrl+T) is also highly recommended so formulas update automatically as your dataset grows.
Group and Concatenate Using LET, REDUCE, and TEXTJOIN
Combine modern array functions to dynamically group your data by unique identifiers and concatenate the related quantities without the need for complex VBA macros.
This approach utilizes the LET function to declare your source data range once, simplifying formula maintenance. REDUCE and VSTACK are then applied to loop through the unique grouping combinations (like PCODE and BUNDLEID), stacking the concatenated strings vertically.
Because these are dynamic arrays, the resulting data will automatically spill into adjacent cells, adjusting seamlessly when new rows are added to the designated source range.
Begin your formula with the LET function to establish the source range as a variable. Type `=LET(rng, A2:E14, ...)` in your destination cell, replacing A2:E14 with your actual data boundaries.
Within the LET formula, use the CHOOSECOLS and UNIQUE functions to isolate the specific columns that contain your grouping keys, ensuring each combination is processed only once.
Implement the REDUCE function to iterate over your unique keys. Inside the REDUCE calculation, use the FILTER function to extract the relevant quantities for each identifier, and wrap it in TEXTJOIN to combine those values with a delimiter, such as a comma.
Use VSTACK to vertically stack the unique identifiers and their corresponding concatenated strings. Press Enter to apply the formula, allowing the grouped results to spill dynamically into the empty space below.
Use WPS Spreadsheet for Advanced Dynamic Array Formulas
WPS Spreadsheet fully supports advanced array functions like LET, UNIQUE, FILTER, and TEXTJOIN, allowing you to seamlessly group rows and concatenate values dynamically just as you would in Microsoft Excel.
- 1. Open Your Source Dataset: Launch WPS Spreadsheet and open the workbook containing the raw data rows you need to group and concatenate.
- 2. Enter the Dynamic Formula: Click on a clear destination cell and input your dynamic array formula combining LET, REDUCE, FILTER, and TEXTJOIN to configure your grouping parameters.
- 3. Calculate and Spill Results: Press the Enter key. WPS Spreadsheet will calculate the variables and automatically spill your newly grouped and concatenated array into the surrounding cells.
Frequently Asked Questions
Why is my dynamic array formula returning a #SPILL! error?
A #SPILL! error occurs when the dynamic array formula does not have enough empty adjacent space to output all its results. Ensure that the cells directly below and to the right of your formula are completely clear of any data, formatting, or hidden characters.
Can I use TEXTJOIN to group rows without the REDUCE function?
While TEXTJOIN handles the concatenation of strings, grouping rows dynamically based on multiple criteria requires functions like UNIQUE and FILTER. REDUCE is necessary in this context because it iterates over the unique arrays to automatically combine and stack the final aggregated results.
Will these dynamic formulas work in older versions of Excel?
No, functions such as LET, CHOOSECOLS, VSTACK, and REDUCE are exclusive to modern spreadsheet applications supporting dynamic arrays (like Microsoft 365 or recent versions of WPS Office). If using an older version such as Excel 2016 or 2019, you will need to utilize Power Query or VBA macros to achieve automated grouping and concatenation.
