Thank you so much @ErikLund_Jensen. I used your solution and got exactly the results that I wanted. Except that where the department was radiology/lab etc. I got the values for the new department to be missing. So I deleted this line from the code if first.ContactDTS then dept_new = ''; sorted my dataset based on patient id, contact date and department old, used your code and got exactly this result. Where the department is radiology, or lab, in the same patient on the same date, then use the department_new equal to the department_old based on the previous row of same patient and same date. Thanks so much for the help! I really appreciate it.
... View more
