I am looking for some help with a formula. On my 'data' sheet, I have data in the format below:
On my 'report' sheet, I have the following table set up:
B C D E F4 HospCode RecordType 0-2 Weeks 2-4 Weeks 4-6 Weeks5 STIV1 Transfers6 Discharges7 8On my 'report' sheet, I'm looking to count the number of the number of 'transfers' and 'discharges' (as specified in column b of the 'data' sheet) occurring within the time frames in columns D, E, F etc.
In summary, what I'm looking for in cell D5 is to count the number of 'transfers' (as specified in column b of 'data' sheet) for hospcode 'STIV1', occurring within 0-2 weeks of the corresponding 'STAsses' record (which is also specified in column b). D6 would be the same, only the number of 'discharge' records occurring within the specified time frame.
I don't know where to start with this, or if it's possible to do. Each person will have at least have an STAsses record but not always a 'Transfer' or 'Discharge' record. The 'UID' is the same for each person, so this could be used to match 'STAsses' entries with 'Transfer' and 'Discharge' entries.
Any pointers would be much appreciated.
Code:
A B C D E F G
UID RecordType HCode AdmittedDate Forename Surname ReviewDate
87962 STAsses STIV1 01/07/2012 Mark Jones 09/12/2012
89658 Transfer GLSI2 01/01/2012 Alison Aitken Female 08/07/2013 4
87962 Discharge STIV1 Mark Jones 20/07/2012 Male 08/07/20135
89654 STAsses STGE1 01/01/2012 Andrew Macbeth Male 08/07/2012 6
89867 Transfer KIND1 01/01/2012 George Deas Male 08/07/2013 7
87962 Transfer STIV1 Mark Jones 14/07/2012 Male 04/03/2013 8
89654 Transfer STGE1 01/01/2012 Andrew Macbeth Male 12/08/2012On my 'report' sheet, I have the following table set up:
B C D E F4 HospCode RecordType 0-2 Weeks 2-4 Weeks 4-6 Weeks5 STIV1 Transfers6 Discharges7 8On my 'report' sheet, I'm looking to count the number of the number of 'transfers' and 'discharges' (as specified in column b of the 'data' sheet) occurring within the time frames in columns D, E, F etc.
In summary, what I'm looking for in cell D5 is to count the number of 'transfers' (as specified in column b of 'data' sheet) for hospcode 'STIV1', occurring within 0-2 weeks of the corresponding 'STAsses' record (which is also specified in column b). D6 would be the same, only the number of 'discharge' records occurring within the specified time frame.
I don't know where to start with this, or if it's possible to do. Each person will have at least have an STAsses record but not always a 'Transfer' or 'Discharge' record. The 'UID' is the same for each person, so this could be used to match 'STAsses' entries with 'Transfer' and 'Discharge' entries.
Any pointers would be much appreciated.
Last edited:
