Assume a three column dataset with Patient ID, Smoking Status and Review Date
| PatientID | SmokingStatus | ReviewDate |
| P1 | 10-03-2018 | |
| P1 | 9 | 09-03-2018 |
| P1 | 1 | 08-03-2018 |
| P1 | 4 | 07-03-2018 |
| P2 | 9 | 10-03-2018 |
| P2 | 9 | 09-03-2018 |
| P2 | 9 | 08-03-2018 |
| P2 | 9 | 07-03-2018 |
| P3 | 2 | 10-03-2018 |
| P3 | 09-03-2018 | |
| P3 | 9 | 08-03-2018 |
| P4 | 9 | 10-03-2018 |
| P4 | 1 | 09-03-2018 |
| P4 | 4 | 08-03-2018 |
The objective is the create another 3 column dataset with the following conditions:
- If the patient’s latest smoking status is other than Blank or 9, then consider that as the smoking status of the patient; and
- If the patient’s latest smoking status is blank or 9, then consider the previous smoking status that is not blank or 9; and
- If the patient’s smoking status is blank or 9 on all dates, then consider the smoking status as 9
The expected result is:
| PatientID | Last date when the smoking status was other than 9 or Blank | Smoking status on that date |
| P1 | 08-Mar-18 | 1 |
| P2 | 10-Mar-18 | 9 |
| P3 | 10-Mar-18 | 2 |
| P4 | 09-Mar-18 | 1 |
I have solved this question using 3 methods – PowerPivot, Advanced Filters and formulas. You may download my solution workbook from here.
Determine the most recent status after satisfying certain conditions
{ 0 Comments }