How to Group Employee Certifications in Excel for Mail Merge
Question details
The user needs to group multiple certification descriptions and expiration dates by employee into a single row to prepare the data for a Word mail merge.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing a consolidated employee certification list from a large dataset (approx. 1,200 records) to generate notifications using Mail Merge in Word.
- Observed behavior
- Multiple certification records exist in separate rows for individual employees, which prevents standard Mail Merge from grouping them into a single notification document per employee.
Ensure your spreadsheet software supports dynamic array functions. The UNIQUE, FILTER, and TEXTJOIN formulas are available in Microsoft 365, Excel 2021, and modern versions of WPS Office.
Use Dynamic Array Formulas (UNIQUE, FILTER, and TEXTJOIN)
Combine advanced array formulas to extract unique employee names and seamlessly merge their respective certification details into a single cell.
This method leverages dynamic arrays to automatically pull distinct employee names and consolidate their related records, perfectly structuring the dataset for a Word Mail Merge without requiring complex VBA code.
Create a new summary sheet. In cell A2, enter the formula =UNIQUE(Sheet1!A2:A31) to generate a distinct list of employees from your original dataset.
In cell B2, next to the first unique employee name, enter the formula =TEXTJOIN(", ", TRUE, UNIQUE(FILTER(Sheet1!$B$2:$B$31, Sheet1!$A$2:$A$31=A2, ""))).
Select cell B2, click and drag the fill handle down to apply the TEXTJOIN formula to the rest of the unique employee names.
Save your workbook. Open Microsoft Word, go to the Mailings tab, click 'Select Recipients', and choose your newly created summary sheet as the data source.

Use WPS Spreadsheet to Group and Merge Data Seamlessly
WPS Office offers robust support for advanced array formulas like UNIQUE, FILTER, and TEXTJOIN, making data consolidation for Mail Merge quick and hassle-free. It provides a fast, lightweight alternative to Microsoft Excel with built-in document generation features.
- 1. Open Data in WPS Spreadsheet: Launch WPS Office and open your employee certification spreadsheet.
- 2. Apply Array Formulas: Use =UNIQUE() to list employees in a new sheet, and use =TEXTJOIN() with FILTER() to merge their certifications into a single cell.
- 3. Start Mail Merge in WPS Writer: Open WPS Writer, navigate to the Mailings or References tab, click Mail Merge, and select your saved WPS Spreadsheet file as the data source.

Frequently Asked Questions
Why is my TEXTJOIN formula returning a #NAME? error?
If TEXTJOIN returns a #NAME? error, it means your version of Excel does not support the function. You need Excel 2019/365, Excel 2021, or a recent version of WPS Office to use TEXTJOIN.
Can I separate the grouped certifications with a line break instead of a comma?
Yes, you can use CHAR(10) as the delimiter in your TEXTJOIN formula: =TEXTJOIN(CHAR(10), TRUE, FILTER(...)). Remember to turn on 'Wrap Text' for that cell to see the line breaks.
How do I group both certification name and expiration date together?
You can concatenate the columns inside the FILTER function. For example, use Sheet1!$B$2:$B$31 & " - " & TEXT(Sheet1!$C$2:$C$31, "mm/dd/yyyy") inside the FILTER range argument to combine the name and date before joining them.
Does Mail Merge in Word recognize the line breaks from my Excel cell?
Word mail merge sometimes ignores standard Excel line breaks. If CHAR(10) doesn't format correctly in Word, you may need to perform a directory-style mail merge or use Word's built-in formatting rules to properly display the merged lines.




