Function Problems

How to Combine Learner Names and Statuses for Excel Mail Merge

Emma BrownEmma Brown Oct 12, 2026 874 views

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.

How to Combine Learner Names and Statuses for Excel 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.
Before you start

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.

Solution 1Recommended

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.

1
Create a Helper Column

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.

2
Generate Unique Manager Combinations

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.

3
Combine Entries per Manager

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.

4
Enable Wrap Text

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.

Use TEXTJOIN, UNIQUE, and FILTER to Combine Data
Adjusting Included Data: If you need to exclude the course name, simply adjust the helper column formula in step 1 to omit the specific cell reference for the course name.
WPS Office Spreadsheet Solution

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. 1. Open Your Dataset: Launch WPS Spreadsheet and open your existing learner and manager data file.
  2. 2. Apply Dynamic Formulas: Use the TEXTJOIN, UNIQUE, and FILTER functions to combine your data as outlined in the solution.
  3. 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.
Fully compatible with Microsoft Excel .xlsx formats and formulas.Advanced functions like TEXTJOIN, UNIQUE, and FILTER work out of the box.Seamlessly transition from WPS Spreadsheet to WPS Writer for the actual mail merge.Free, lightweight, and user-friendly interface.
microsoft office alternative - wps office

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.