Create Shift Handover List
I need to combine all patient names in 'Room 101' into a single comma-separated string for the handover report.
=TEXTJOIN(", ", TRUE, IF(A2:A6="Room 101", B2:B6, "")) How it works: This formula uses TEXTJOIN to combine text strings. - `", "` is the delimiter, separating each patient name with a comma and a space. - `TRUE` tells TEXTJOIN to ignore any empty cells that result from the IF condition, preventing extra delimiters. - `IF(A2:A6="Room 101", B2:B6, "")` is the core filtering mechanism. It checks each cell in the 'Room' range (A2:A6). If a room matches 'Room 101', it returns the corresponding patient name from column B; otherwise, it returns an empty string. TEXTJOIN then only combines the non-empty patient names.
Data Setup
| Room | Patient Name | Bed Number |
|---|---|---|
| Room 101 | Alice Smith | 1 |
| Room 102 | Bob Johnson | 2 |
| Room 101 | Charlie Brown | 3 |
| Room 103 | Diana Prince | 4 |
| Room 101 | Eve Adams | 5 |
Step-by-Step Guide
Ensure your patient data is organized with columns for 'Room' and 'Patient Name'. For this example, 'Room' is in column A (A2:A6) and 'Patient Name' is in column B (B2:B6).
Select an empty cell where you want the combined list of patient names to appear (e.g., C2).
Enter the formula: `=TEXTJOIN(", ", TRUE, IF(A2:A6="Room 101", B2:B6, ""))`
Press Enter. Excel will automatically spill the result into the selected cell. (Note: For older Excel versions, you might need to press Ctrl+Shift+Enter to enter it as an array formula.)