Hi,
Hoping someone could help: I have a list of patient No's and the dates of their stay. Patients could have multiple stays - if they are still here then the Deduction Date is blank.
I then have another dataset of drugs prescribed; using the Start Date of the drugs I'd like to identify which Stay the prescription was under
Data looks as follows - in the Drugs Prescribed table, the 1st row for patient 123456 needs to have "Stay No" as 3 because the Start Date is between the Registration Date and Deduction Date of the Stays table, the Lookup would need to ignore the first entry for patient 123456:
Stays
Drugs Prescribed
Many thanks for any help - would this be some kind of INDEX/MATCH formula? And could it deal with instances of the End Date being blank?
Hoping someone could help: I have a list of patient No's and the dates of their stay. Patients could have multiple stays - if they are still here then the Deduction Date is blank.
I then have another dataset of drugs prescribed; using the Start Date of the drugs I'd like to identify which Stay the prescription was under
Data looks as follows - in the Drugs Prescribed table, the 1st row for patient 123456 needs to have "Stay No" as 3 because the Start Date is between the Registration Date and Deduction Date of the Stays table, the Lookup would need to ignore the first entry for patient 123456:
Stays
Patient ID | Registration Date | Deduction Date | Stay No |
123456 | 10-Mar-21 | 25-Mar-21 | 1 |
987654 | 11-Mar-21 | 2 | |
123456 | 26-Jun-21 | 15-Aug-21 | 3 |
Drugs Prescribed
Patient ID | Drug | Start Date | Stay No |
123456 | Paracetamol | 28-Jun-21 | 3 |
987654 | Ibuprofen | 15-Mar-21 | 2 |
Many thanks for any help - would this be some kind of INDEX/MATCH formula? And could it deal with instances of the End Date being blank?