How to Group Repeated Names into Rows with Excel Formulas
Question details
The user wants to group repeated names in a dataset and combine their associated items into a single row using formulas instead of PivotTables or VBA.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing and restructuring tabular data where multiple rows share the same name but have different associated items spread across multiple rows.
- Observed behavior
- The user needs to consolidate data horizontally into a single row per name with related items split into separate columns automatically.
Ensure your version of Excel supports modern dynamic array functions such as LET, GROUPBY, MAP, and TEXTSPLIT, which are typically only available in the latest Microsoft 365 updates.
Use GROUPBY, MAP, and TEXTSPLIT Functions
This modern formula approach efficiently groups your source data, concatenates the related values, and then splits them into separate columns across a single row.
This method leverages the LET function to store intermediate calculation steps, making the complex formula easier to read. It first groups the data by names and appointments, turns the associated items into a text string, and finally splits that string into a dynamic array.
Click on the empty cell in your worksheet where you want the new grouped table to begin.
Type the formula: =LET(grouped, GROUPBY(HSTACK(Appointment, Name, City), Costume, ARRAYTOTEXT, , 0), costumes, MAP(TAKE(grouped, , -1), LAMBDA(list, TEXTSPLIT(list, ","))), HSTACK(TAKE(grouped, , 3), costumes))
Replace the named ranges (Appointment, Name, City, Costume) with your actual data ranges or column references (e.g., A2:A10, B2:B10).
Press Enter. The dynamic array will automatically spill the grouped results into the adjacent rows and columns.
Use CHOOSECOLS and TEXTBEFORE for Custom Splitting
If you need strict control over column indexing and text delimiters without relying on the MAP function, you can use a combination of CHOOSECOLS, TEXTJOIN, and TEXTAFTER.
Group and Consolidate Data Easily with WPS Spreadsheet
WPS Office provides robust data handling capabilities. You can quickly extract unique names and combine their associated data using a combination of the UNIQUE and TEXTJOIN functions, offering a lightweight alternative to complex array manipulations.
- 1. Extract unique names: Select a blank cell and use the formula =UNIQUE(B2:B20) (assuming your names are in column B) to automatically generate a list of distinct names.
- 2. Combine related data: In the adjacent column, use the formula =TEXTJOIN(", ", TRUE, IF($B$2:$B$20=F2, $D$2:$D$20, "")) to gather all related items for that specific name.
- 3. Apply as an array formula: Press Ctrl+Shift+Enter to evaluate the IF condition properly across the range and drag the fill handle down to apply it to all unique names.
- 4. Split data into columns: If you need the items in separate columns instead of one cell, highlight the results and use the 'Text to Columns' feature under the Data tab, setting the comma as your delimiter.
Frequently Asked Questions
Why am I getting a #NAME? error with the GROUPBY function?
The GROUPBY function is a recent addition to Microsoft 365. If you are using an older version of Excel (such as Excel 2019 or 2021), this function is not recognized. You can use the UNIQUE and FILTER function combination or PivotTables as a workaround.
Can I group repeated names without using complex formulas?
Yes, the standard and most accessible approach is to use a PivotTable or the Power Query tool. Power Query allows you to group by a specific column and extract the related items without needing to write advanced array formulas.
How does the MAP function work in this scenario?
The MAP function applies a custom LAMBDA function to every element in a specific array. In this context, it takes each comma-separated list of grouped items and applies the TEXTSPLIT function to divide them into distinct columns dynamically.
What is the alternative to ARRAYTOTEXT in older versions?
If your spreadsheet software does not support ARRAYTOTEXT, you can use the TEXTJOIN function. By formatting it as =TEXTJOIN(",", TRUE, range), you achieve a similar result by concatenating the array text while ignoring empty cells.
