This isn't something that your Excel experience will help you much in setting it up.
Define a "Rooms" table where you have one record for each room. Use Manage | Database | Relationships to create this relationship:
Rooms::RoomNumber = Occupants::RoomNumber
"Occupants" is what I am calling your current table here.
Create a list layout based on Rooms so you have one row for each record and you can sort these by RoomNumber to get the correct order.
This calculation field, defined in Rooms, will provide you comma separated list of room occupants:
Subsitute ( List ( Occupants::Name ) ; ¶ ; ", " )
List will produce a list of the Names separated by returns. The substitute function then replaces the return characters with a comma and space to eliminate the returns.