Function Problems

Create a Comma-Separated Scene List for Each Role in Excel

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 868 views

Question details

Create a comma-separated list of scene numbers for each role from a scene-and-role data set.

Create a Comma-Separated Scene List for Each Role in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Scene numbers and their associated role numbers are stored in separate columns, and each role needs a single cell listing its scenes.
Observed behavior
Each role should display all matching scene numbers in one comma-separated cell.
Before you start

Arrange the data so each row contains one scene number in column A and its associated role number in column B. List the roles to search for in column D, starting at D2, and check that your Excel version supports FILTER and TEXTJOIN.

Solution 1Recommended

Combine FILTER and TEXTJOIN to list scenes by role

Use this formula in Excel for Microsoft 365 or Excel 2021 and later to create a scene list for each role.

1
Enter the role list

In column D, enter the role numbers you want to look up, starting in D2.

2
Enter the formula

Select E2 and enter =TEXTJOIN(", ",TRUE,FILTER($A$2:$A$100,$B$2:$B$100=D2,"")) to combine the scenes matching the role in D2.

3
Copy the formula down

Drag the fill handle from E2 down alongside the remaining roles in column D. Adjust the fixed ranges if your source data extends beyond row 100.

Combine FILTER and TEXTJOIN to list scenes by role
Repeated scenes: The formula includes every matching row, so a scene number appears more than once if the same scene-and-role pair is repeated.
Work with scene lists in WPS

Create role-based scene lists in WPS Spreadsheet

In a version of WPS Spreadsheet that supports FILTER and TEXTJOIN, use the same formula to combine matching scene numbers for each role.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Check the data layout: Place scene numbers in A2:A100 and corresponding role numbers in B2:B100. Enter the roles to search for in D2 and below.
  3. 3. Enter the formula: Select E2 and enter =TEXTJOIN(", ",TRUE,FILTER($A$2:$A$100,$B$2:$B$100=D2,"")) if your WPS version supports both functions.
  4. 4. Fill and save: Drag the fill handle down column E for the other roles, then use File > Save As to save the workbook.
Combine matching scene numbers in one comma-separated cellOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

What happens if a role has no matching scenes?

The FILTER formula returns an empty string for no matches, so the result cell is blank.

Can I use this formula in an older version of Excel?

The formula requires FILTER, which is available in Excel for Microsoft 365 and Excel 2021 or later. For older versions, use a helper column or VBA instead.

How do I include more source rows?

Edit both ranges in the formula to cover the same rows. For example, change $A$2:$A$100 and $B$2:$B$100 to $A$2:$A$500 and $B$2:$B$500.

Why is a scene number listed more than once?

FILTER returns every matching row. If a scene-and-role pair appears multiple times in columns A and B, the scene number is included for each occurrence.