I work at a rehabilitation hospital where we design a daily master schedule for all the current inpatients. And then this master schedule is manually converted into a separate excel workbook designed to inform each patient of their schedules for the following days therapy. And I believe our operating system is Windows 7 with Microsoft Office 2007. And I'm hoping that there is a way, even if we have to purchase an excel add-in program, to convert the information from each row of our master schedule into one smaller, individualized table for each patient. Basically, producing multiple smaller tables (one table for each row). I have attached a screenshot of our master schedule (names have been changed) and a screenshot of how we would type up the patients' schedules. (Also, there is one mistake. The date at the bottom of the master schedule should be the same date that's on the patients' schedules)

The master schedule includes all the patients' (we typically have between 40-50 patients) time for therapy and which therapist they will be seeing, which we currently use by filling a time slot (a cell) with the corresponding therapists' initials. Our therapy sessions are translated into units and each unit is equivalent to a 15-minute therapy session. So our column headers are labeled in 15-minute increments (denoting 1 unit of therapy) starting at 8:00am.

When designing the master schedule, each therapist creates his/her own schedule each work day for the following day. Typically, each therapist will see two patients for a 1-hour session in the morning (4 subsequent cells in a row filled with the same initials) and a 45-minute session (3 cells) in the afternoon. Each row corresponds to a room number with most rooms containing two beds and whether the patient's bed is near the door (D) or the window (W), e.g., 100D and 100W. An "adl" means that an Occupational Therapist, therapists' initials being in column C, will be assisting a patient 1-on-1 with bathing and dressing in his/her room for an hour, the starting time being labeled in column D. "RES" stands for a scheduled respiratory treatment, which means the patient must go back to their room and therapy cannot occur at this time (the times for RES remain the same every day). "Lun" and "Din" stand for the times that the patient is scheduled to go to the cafeteria for lunch or dinner. The therapists have lunch between 12-1pm so no therapy is scheduled for this time. Any cells filled with letters other than the therapists' initials translates to a location, for example, "day" means the therapy will be in the day room (and not the gym), or a reminder/note to a therapist, such as "PT" meaning physical therapy, "grp" meaning a group session, "rm" meaning therapy will be in patient's room, etc.

So each day after the master schedule has been completed, myself or another coworker has to manually translate the master schedule into numerous smaller schedules designed for each patient. Essentially, we have to convert each row of the master schedule into a simplified, personalized schedule by translating blocks of cells into assigned times for therapy and then translate each cell's information into the type of therapy, i.e., adl = bathing with therapist, physical therapists' initials = physical therapy, etc. Hopefully this all makes sense to you. As you can probably guess, this is an extremely monotonous task. I know our system is confusing and I believe we make it much harder than it needs to be. Hence, why I am seeking anyone's advice!

Finally to my point, is it possible for an excel function (or an excel add-in program with this function) to convert each row of the master schedule into a smaller table denoting the patient's therapy times? I am assuming that if it is possible, that our master schedule will need some revamping and I am completely fine with anything we may need to do in order to make this work! If this isn't possible, any information or tips on how to make the process easier would be more than welcome! Also, if you have any questions for me, please do not hesitate to ask! Thank you so much for taking the time to read this and I look forward to hearing back from any you!