Function Problems

How to Group Employee Certifications in Excel for Mail Merge

Phi Hung VoPhi Hung Vo Nov 4, 2026 868 views

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.

How to Group Employee Certifications in Excel for 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.
Before you start

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.

Solution 1Recommended

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.

1
Extract unique employee names

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.

2
Combine matching certification details

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, ""))).

3
Apply the formula to all employees

Select cell B2, click and drag the fill handle down to apply the TEXTJOIN formula to the rest of the unique employee names.

4
Save and prepare for Mail Merge

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 Dynamic Array Formulas (UNIQUE, FILTER, and TEXTJOIN)
Adjusting the Formula Ranges: Make sure to adjust the ranges (A2:A31 and B2:B31) to match the actual row count of your dataset. For a list of 1,200 records, change it to A2:A1200 and B2:B1200.
Efficient Data Processing with WPS Office

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. 1. Open Data in WPS Spreadsheet: Launch WPS Office and open your employee certification spreadsheet.
  2. 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. 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.
Fully supports advanced formulas like UNIQUE, FILTER, and TEXTJOIN for grouping certifications.Seamlessly compatible with Microsoft Excel (.xlsx) and Word (.docx) formats.Built-in Mail Merge tools in WPS Writer directly connect to data in WPS Spreadsheet.Free, lightweight, and highly optimized for processing large datasets with thousands of rows.
microsoft office alternative - wps office

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.