Function Problems

How to Group and Concatenate Unique Names by Host Family in Excel

Emma Brown Oct 1, 2026 868 views

Question details

The user needs to group data by unique host-family names and automatically concatenate the related individual names into a single cell.

Product: Excel
Device & OS: not provided
Scenario: Organizing and consolidating a list of names based on specific group categories or host families so that the final output updates automatically when source data changes.
Observed behavior: Currently, names and host families are listed in separate rows, and the user seeks a formulaic approach to aggregate the individual names by matching them to distinct host families.

Before you start

Ensure you are using a modern spreadsheet version (such as Office 365, Excel 2021, or the latest WPS Office) that supports dynamic array functions, as older versions do not include the UNIQUE or FILTER functions.

Solution 1Recommended

Use UNIQUE and TEXTJOIN with FILTER for Dynamic Grouping

Combine the UNIQUE, FILTER, and TEXTJOIN functions to automatically extract a list of distinct host families and merge the corresponding names into a comma-separated format.

This solution leverages Excel's dynamic array capabilities to create an automated, automatically updating summary of your data without the need for manual copy-pasting or complex VBA macros.

Extract the Unique Host Families

Select an empty cell where you want your summary to start (e.g., cell G2). Enter the formula =UNIQUE(F2:F29) to generate a dynamic list of unique host family names from column F.

Filter and Concatenate the Names

In the adjacent cell (e.g., cell H2), enter the formula =TEXTJOIN(", ", TRUE, FILTER(E$2:E$29, F$2:F$29=G2, "")). This formula filters the names in column E that match the host family in G2, and joins them with a comma and a space.

Apply Formula to the Entire List

Select cell H2, click and hold the small square at the bottom-right corner of the cell (the fill handle), and drag it down to apply the concatenation formula to all the unique host families listed in column G.

Use UNIQUE and TEXTJOIN with FILTER for Dynamic Grouping

Dynamic Updates: Because this method uses dynamic array functions, the grouped list and concatenated names will automatically update whenever you add, remove, or change data in the original columns.

Solution 2

Use Power Query to Group and Concatenate Text

If you are using an older version of Excel that lacks dynamic array formulas, Power Query provides a robust, built-in alternative for grouping and joining text strings.

Simplify Data Aggregation

Group and Manage Your Spreadsheet Data with WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions, allowing you to instantly group, filter, and concatenate your raw data without complicated workarounds.

1. Open Your Data File: Launch WPS Spreadsheet and open the document containing your names and host families.
2. Extract Categories Dynamically: Type =UNIQUE(F2:F29) in a blank cell and press Enter to instantly list all distinct host families.
3. Concatenate the Names: In the cell next to it, input =TEXTJOIN(", ", TRUE, FILTER(E$2:E$29, F$2:F$29=G2, "")) and drag down to apply the grouping.

Full compatibility with Microsoft Excel formulas (.xlsx)Native support for advanced functions like UNIQUE, FILTER, and TEXTJOINLightweight, fast execution for processing large datasetsFree and intuitive interface for seamless data management

microsoft office alternative - wps office

Frequently Asked Questions

Why is the FILTER function returning a #CALC! error?

The #CALC! error typically happens if the FILTER function finds no records matching your criteria. You can prevent this by adding a third argument to your FILTER function to handle empty results, for example: FILTER(E2:E29, F2:F29=G2, "No Match").

Can I sort the concatenated names alphabetically within the cell?

Yes, you can easily sort the extracted names before they are concatenated by wrapping the FILTER function inside a SORT function. The updated formula would be: =TEXTJOIN(", ", TRUE, SORT(FILTER(E$2:E$29, F$2:F$29=G2, ""))).

Are UNIQUE and FILTER functions available in older versions of Excel?

No, UNIQUE and FILTER are dynamic array functions that were introduced in Excel 2021 and Microsoft 365. If you are using Excel 2019 or older, you will need to rely on alternative methods like Power Query or complex INDEX/MATCH array formulas to achieve a similar result.

Excel & Data Problems