Excel: Combine Multiple Form Responses into One Row by ID
Question details
The user needs to consolidate several Microsoft Forms response rows into one row for each ID.
- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- Partial form responses are stored across multiple rows, and the user wants to group them by ID before using the results for Mail Merge.
- Observed behavior
- Information belonging to the same ID is split across multiple rows instead of appearing together in one consolidated row.
Place the source responses in one contiguous table or matrix and identify the column containing the record ID. Confirm the ID column position and the columns that should be combined, because the formula uses those positions.
Use a dynamic-array formula to combine responses by ID
Use this approach in Microsoft 365 or a compatible WPS Spreadsheet version when the source contains multiple rows for each ID and the results should spill into a new consolidated table.
The formula creates a unique list of IDs, filters the source rows for each ID, combines the nonblank response fields horizontally, and returns one row per ID. Replace matrix and the column positions with references that match your source table.
Open the worksheet containing the response data, select a blank cell where the consolidated table should begin, and make sure the cells below and to the right are empty for the spilled results.
Enter this formula in the selected cell: =LET(ID,INDEX(matrix,,3),UniqueID,SORT(UNIQUE(ID)),Combine,LAMBDA(acc,id,LET(filtered,FILTER(matrix,ID=id),nameAndID,TAKE(filtered,1,3),subjects,TOROW(DROP(filtered,,3),1,1),VSTACK(acc,HSTACK(nameAndID,subjects)))),DROP(REDUCE("",UniqueID,Combine),1))
Replace matrix with the actual source range or table reference. Change INDEX(matrix,,3) if the ID is not in the third column, and adjust TAKE, DROP, or the column counts when the identifying fields use different positions.
Press Enter and review the dynamic array output. Each unique ID should appear once, with the related response values combined across the same row.
Select the consolidated output range, copy it, and use it as the recipient data source for Mail Merge after confirming that the column headings and values are correct.
Combine Form Responses in WPS Spreadsheet Before Mail Merge
WPS Spreadsheet can organize form-response records with modern spreadsheet formulas, helping you group duplicate IDs, combine related fields, and prepare a clean one-row-per-recipient table for Mail Merge.
- 1. Open the response workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the Microsoft Forms responses.
- 2. Choose a blank output area: On the response worksheet, click an empty cell outside the source table where the combined records can spill.
- 3. Enter the grouping formula: Paste the LET, FILTER, UNIQUE, REDUCE, VSTACK, and HSTACK formula into the formula bar, then replace matrix and the ID column position with your actual source range and layout.
- 4. Verify and save the consolidated table: Review the one-row-per-ID output, copy it if needed, and choose File > Save As to save the workbook in Excel Workbook (*.xlsx) format for Mail Merge.

Frequently Asked Questions
Which column does the formula use as the record ID?
The example uses the third column through INDEX(matrix,,3). Change 3 to the actual position of the ID column within your source matrix.
How do I change the source data range?
Replace every occurrence of matrix in the formula with your source range or table reference, such as A2:H500 or Responses[#Data]. Keep the range consistent throughout the formula.
Will the formula combine responses for duplicate IDs?
Yes. UNIQUE creates one ID for each group, FILTER selects all rows with that ID, and the remaining response fields are combined into the output row.
Why does the formula show a function or spill error?
The spreadsheet version may not support one of the dynamic-array functions, or cells in the spill area may contain data. Clear the output area and confirm that LET, LAMBDA, REDUCE, VSTACK, and related functions are supported.
Can the consolidated output be used for Mail Merge?
Yes. After checking the results, use the output range as the Mail Merge data source. Make sure the first row contains clear field names and that each ID appears only once.




