How to Combine Learner Names and Statuses for Excel Mail Merge
Question details
The user needs to consolidate multiple learner names and their corresponding course statuses into a single Excel cell grouped by manager, formatting the data for a Word mail merge.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing an Excel dataset for a Word mail merge where each manager requires a consolidated list of their assigned learners and statuses in one merged field.
- Observed behavior
- The learner data is currently spread across multiple rows and needs to be combined using formulas with line breaks so it displays neatly in a single cell per manager.
Ensure your spreadsheet software supports dynamic array formulas like UNIQUE and FILTER (available in Microsoft 365, Excel 2021, or WPS Office), and verify that your source data has consistent headers.
Use TEXTJOIN, UNIQUE, and FILTER to Combine Data
This solution uses advanced dynamic array formulas to efficiently group, filter, and concatenate learner data by manager with line breaks for clean mail merge formatting.
By combining TEXTJOIN with UNIQUE and FILTER, you can dynamically group multiple rows of data into a single cell. The CHAR(10) function is used to insert a line break between each combined entry, ensuring the final mail merge document looks properly formatted.
In your data table, create a new column to join the learner name and status. Enter the formula =TEXTJOIN(" - ",TRUE,C2:D2) (assuming column C contains the Name and D contains the Status). Press Enter and drag the fill handle down to apply it to all rows.
In a blank area of your worksheet (for example, cell G2), extract a unique list of managers by entering the formula =UNIQUE(A2:B9) (assuming columns A and B contain the manager details). This prevents duplicate mail merge letters.
Next to your unique manager list, use the formula =TEXTJOIN(CHAR(10),TRUE,FILTER(E2:E9,(A2:A9=G2)*(B2:B9=H2))) where column E is your helper column. The CHAR(10) argument automatically inserts a line break between each learner's record.
Select the result cells containing the final TEXTJOIN formula. Go to the Home tab and click 'Wrap Text'. This will force the joined entries to display on separate lines within the same cell.

Efficiently Prepare Mail Merge Data in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN, UNIQUE, and FILTER, allowing you to easily consolidate data for mail merge without needing complex macros.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open your existing learner and manager data file.
- 2. Apply Dynamic Formulas: Use the TEXTJOIN, UNIQUE, and FILTER functions to combine your data as outlined in the solution.
- 3. Run Mail Merge in WPS Writer: Save your prepared .xlsx file, open WPS Writer, go to the References tab, and select Mail Merge to link your newly formatted list.

Frequently Asked Questions
Can this be done without including the course name?
Yes. You can easily modify the helper column formula. Instead of referencing both the learner name and course name (e.g., C2:D2), adjust it to reference only the specific cells you want to keep, or manually select them like =TEXTJOIN(" - ",TRUE,C2,E2).
Why are my combined cells not showing on separate lines?
For the CHAR(10) line break to take effect visually in Excel, you must have the 'Wrap Text' formatting enabled. Select the affected cells, navigate to the Home tab, and click the 'Wrap Text' button.
Does TEXTJOIN work in all versions of Excel?
TEXTJOIN, UNIQUE, and FILTER are available in Excel 2019, Excel 2021, Microsoft 365, and modern versions of WPS Office. If you are using an older version like Excel 2016, you will need to use a VBA macro or Power Query to achieve similar results.
Will the line breaks carry over into a Word Mail Merge?
Yes. When you use the prepared Excel spreadsheet as the recipient list in Word (or WPS Writer), the data contained within the cell, including the CHAR(10) line breaks, will generally carry over and display correctly in your merged document.




