I have seven Excel files containing information about admissions of several hundred patients to various hospital departments, and data appertaining to these (length of stay, type of discharge etc.) The first column always contains the patient ID, the following one contains the dates of admissions. These are usually many dozens per patient, so each patient has dozens to sometimes hundreds of rows (each row containing the patient ID in the first column).

For my research I only need the data from a 2-year period after they completed a certain treatment, however this index date is different for each patient. So I can't just have all rows in the spreadsheet deleted that fall outside the period between two universal dates, rather this period differs for each patient.

I was wondering whether there is a way that, if I set up one spreadsheet with the patient IDs in the first column and the start and end date of the follow-up period in the second and third column, I could link this to my other spreadsheets (or put it all on separate sheets in the same file) and have Excel automatically delete all rows in the other sheets that fall outside this period for the specific patient?

  • 1
    Excel has many different ways this could be accomplished. Since this is not a service forum where we do the work, we really need to know is what you have already attempted, and where you are stuck. With that being said, have you tried filtering the data? How about using a pivot table to arrange the data the way you want to see it? – CharlieRB May 10 '17 at 15:05
  • I thought about filtering by date range, however all I can find online about advanced filters with multiple conditions etc. only talks about one or multiple general date ranges. I can't find any instructions on how to filter each patient in the spreadsheets by his/her individual date range. – Blubberlubber May 10 '17 at 15:49
  • That may be where the pivot table will help you. There are a lot of tutorials online for how to use them. – CharlieRB May 10 '17 at 15:52
  • I am aware of pivot tables, but despite spending about an hour on Google looking for tutorials or general info on how to filter pivot tables by date ranges, I have not found anything on how to use different date ranges for each individual patient. That's the reason I created this thread. – Blubberlubber May 10 '17 at 16:41
  • @Blubberlubber,, for automation you need to program the process,, write me if you can handle VBA, then I suggest the code,, to accomplish it! Also do you have 7 Sheets in one Workbook or 7 Workbooks? – Rajesh S Jun 10 '19 at 8:41

referring to :

data from a 2-year period after they completed a certain treatment.

just set up an extra column to do : if( AND(( >= ), ( <= (+2 years), true, false) .. then filter the column for true.

for the part, you may set up a table of patient name vs the certain treatment date . then use index+match to load the date for the above equation to work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.