Data Import & Export

Excel: Combine Multiple Form Responses into One Row by ID

Chanuka GeekiyanageChanuka Geekiyanage Nov 12, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the output cell

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.

2
Enter the consolidation formula

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))

3
Adjust the source reference and positions

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.

4
Check the spilled result

Press Enter and review the dynamic array output. Each unique ID should appear once, with the related response values combined across the same row.

5
Use the result for Mail Merge

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.

Function availability: LET, LAMBDA, REDUCE, FILTER, VSTACK, HSTACK, UNIQUE, TAKE, and TOROW require a recent Microsoft 365 or compatible spreadsheet version. If the formula is not recognized, update the spreadsheet application or use a helper-column method to group the IDs.

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. 1. Open the response workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the Microsoft Forms responses.
  2. 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. 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. 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.
Group multiple partial form responses by a shared ID in one worksheet.Use dynamic-array formulas to create a consolidated output without manually copying each record.Prepare cleaner recipient data for Mail Merge and downstream reporting.Open and save Microsoft Excel .xlsx workbooks while preserving familiar spreadsheet workflows.
microsoft office alternative - wps office

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.