Transpose Variable-Length Excel Groups into Rows with a Formula
Question details
Create one output row for each unique index and place all values associated with that index across adjacent columns, even when groups contain different numbers of values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The index values are in A2:A34 and the related data values are in B2:B34. The user wants to reorganize the list into variable-length horizontal groups.
- Observed behavior
- Each unique index should appear once in the first output column, followed by its matching values across the same row.
Make sure the destination area has enough empty columns and rows for the spilled result. This formula requires a version of Excel that supports dynamic array functions such as LET, FILTER, UNIQUE, REDUCE, VSTACK, HSTACK, and TOROW.
Use a Dynamic Array Formula to Group Values by Index
Use this method when you want a formula-based result that updates automatically as the source values change.
The formula finds every unique index, filters its matching values, converts each group into a horizontal row, and combines the rows into one spilled array. Groups with fewer values are filled with blank cells so the output remains aligned.
Click an empty cell where the transformed table should begin, such as D2. Ensure the cells below and to the right are empty so Excel can spill the results.
In the selected cell, enter: =LET(ind,A2:A34,conc,B2:B34,HSTACK(UNIQUE(ind),IFNA(DROP(REDUCE("",UNIQUE(ind),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,ind=v))))),1),"")))
Press Enter. Excel returns one row per unique value from column A and places the related values from column B across the row.
Replace A2:A34 and B2:B34 in the formula with your actual index and data ranges. Keep both ranges the same height so each index is matched to the correct value.
Group Variable-Length Lists into Rows in WPS Spreadsheets
WPS Spreadsheets provides a familiar worksheet environment for organizing indexed data and using modern formula workflows. Paste your source list into two columns, enter a compatible dynamic array formula in an empty output area, and let the result expand into grouped rows.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the index and value columns.
- 2. Verify the source columns: Confirm that the index values are in one column, such as A2:A34, and the associated values are in the adjacent range, such as B2:B34.
- 3. Select an empty output cell: Click a blank cell such as D2 where the grouped table should start, leaving room for the formula result to expand.
- 4. Enter the dynamic array formula: Paste the LET formula into the selected cell and press Enter to return each unique index with its associated values arranged across one row.
Frequently Asked Questions
Why does the formula return a #SPILL! error?
A #SPILL! error means one or more cells in the formula's output area are not empty. Clear the cells to the right and below the formula cell, then press Enter again.
Can I use the formula with more than 34 rows of data?
Yes. Change both source references to cover your data, such as A2:A1000 and B2:B1000. The two ranges must begin and end on corresponding rows.
How can I keep the unique indexes in their original order?
UNIQUE returns values in the order they first appear in the index range. Keep the index range in its original sequence to preserve that order in the output.
What happens when an index has only one associated value?
The index is returned with its single matching value in the next column. Remaining columns on that row are left blank to match the width of longer groups.
Is Power Query better than a formula for this task?
Power Query is useful when the transformation is large, repeated regularly, or needs a refreshable import process. Use the formula when you want the result to update directly in the worksheet as source cells change.
