Automatically List Names by Room Number in Excel | WPS Spreadsheet
Question details
The user needs to create a distinct list of room numbers and consolidate all occupant names assigned to each specific room into a single cell.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing room assignments, hotel bookings, or office seating charts where occupant names are currently scattered across multiple rows and need to be grouped by room.
- Observed behavior
- The goal is to dynamically extract unique room numbers and use formulas to combine the corresponding names automatically, rather than doing it manually.
Ensure your source data is formatted as a Table (e.g., named 'Table1') with clearly labeled columns for 'Name' and 'Room Number' to allow dynamic formula expansion.
Use Dynamic Array Formulas (UNIQUE, FILTER, and TEXTJOIN)
Combine modern array functions to automatically extract a unique list of room numbers and append all corresponding names into a single, comma-separated string.
This method utilizes advanced dynamic array functions available in modern spreadsheet software. It requires no VBA or manual filtering, and automatically updates if the source data changes.
Select the cell where you want the list to start (e.g., E2) and enter the formula: =SORT(UNIQUE(Table1[Room Number])). This will spill a sorted, distinct list of all room numbers downward.
In the adjacent cell (e.g., F2), enter the formula: =TEXTJOIN(", ", TRUE, FILTER(Table1[Name], Table1[Room Number]=E2, "")). This filters the names belonging to the room in E2 and joins them with a comma.
Select cell F2, click and drag the fill handle (the small square at the bottom-right corner of the cell) downwards to apply the formula to all the extracted room numbers in column E.
Organize Room Assignments Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like UNIQUE, SORT, FILTER, and TEXTJOIN. You can effortlessly group data, extract unique values, and merge text without writing complex scripts or macros.
- 1. Open your data file: Launch WPS Spreadsheet and open your workbook containing the room assignments.
- 2. Extract unique rooms: Use the =UNIQUE() function to instantly generate a distinct list of room numbers in a new column.
- 3. Consolidate names: Apply the =TEXTJOIN() formula combined with =FILTER() to consolidate multiple occupants into a single cell per room.
- 4. Save seamlessly: Save your newly organized data in the universal .xlsx format to share with anyone.
Frequently Asked Questions
Why is my UNIQUE or FILTER formula returning a #NAME? error?
This error typically occurs if your version of the spreadsheet software does not support dynamic array functions. Ensure you are using a modern version such as Microsoft 365, Excel 2021, or the latest version of WPS Office.
Can I use standard cell ranges instead of a formatted Table?
Yes, you can replace the table references like Table1[Room Number] with absolute standard ranges such as $B$2:$B$100. However, formatting your data as a Table is highly recommended because formulas will automatically include newly added rows.
How do I separate the combined names with a line break instead of a comma?
In the TEXTJOIN formula, replace the comma and space delimiter ", " with CHAR(10), which represents a line break. For example: =TEXTJOIN(CHAR(10), TRUE, FILTER(...)). Remember to enable 'Wrap Text' on the result cells to see the line breaks correctly.
What if there are empty rows in my source data?
The TEXTJOIN function handles this gracefully. By setting its second argument to TRUE, the function automatically ignores empty cells, ensuring you don't get extra commas or awkward spacing in your final combined list.
