Create a Comma-Separated Scene List for Each Role in Excel
Question details
Create a comma-separated list of scene numbers for each role from a scene-and-role data set.

- 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.
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.
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.
In column D, enter the role numbers you want to look up, starting in D2.
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.
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.

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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 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. 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. Fill and save: Drag the fill handle down column E for the other roles, then use File > Save As to save the workbook.

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.




