Filtering/deleting rows based on numerous individual index dates

For a research project I am trying to find out how often several hundred patients have been admitted to various hospital departments, and additional info like the type of discharges etc. For each department I have a spreadsheet covering all sorts of data from the late 90s to today (resulting in tens of thousands of rows in each spreadsheet, for a total of seven spreadsheets). However, for each patient I am only interested in a 2-year period after they have completed a certain treatment. The problem is that this period is different for each patient.

I have cleaned up most of the mess, so the first column contains the unique patient ID (in each row, usually covering dozens to several hundred rows), the second column contains the individual admission dates. An additional spreadsheet contains the patient IDs in the first column, the index date in the second, and the end date of the follow-up period in the third column.

Now I need to remove (or filter out) all rows in the spreadsheets that contain admission dates that fall outside the 2-year period. I know how to filter by date range with advanced filters across the whole spreadsheet, but after quite some time on Google and looking at tutorials I am still at a loss as to how to do this for different individual index dates for each patient. I have found out that a pivot table is supposed to help, but I don't know how to link such a table to my spreadsheet (or sheet) containing the data on individual follow-up periods, so excel checks the index date for each patient ID to filter out the rows containing dates that fall outside this period.

If anyone could point me towards the right option in Excel, or any resource/tutorial to achieve this, that would be greatly appreciated

Originally Posted by JBeaucaire

When in doubt, add a helper column. For you, I'd add a formula that is doing the "within two years" test for one row.

For instance, =IF(B2-A2<=720, "Within", "Outside")

You get the idea.

Now you have a column that is flagging each row for you in advance. Do your filtering and copying based on those values.

Thanks for the reply. The way I understand it I would have to create another column in the actual spreadsheets with the data and insert the index dates - considering the number of rows for each patient varies (from a few dozen to several hundreds), it seems like I would have to copy the index dates over manually.

Wouldn't creating that helper column take just as long as just deleting the unwanted rows by hand?

I made up some data (the real one is on a secure server with remote access, and I'd be drawn and quartered if I tried to remove any of it ) and attached it to this post. It's just an example, the actual datasets are naturally much bigger.

I could do what you described, i.e. make a new column next to the patient IDs, insert the index dates, then use the formula you posted, but because the number of rows per patient differ I would have to copy a number of cells with index dates, then add more, or take some out etc., and I think in that case I may as well remove the rows manually (highlight and delete them collectively from the first row to the index date, and then from the end of the follow-up period to the last row for the patient, not one by one, of course).

So I was wondering whether there is a way to make Excel recognise the patient ID in the first column in Sheet2 (Admission data), go to Sheet1 (Index dates), check the follow-up period for that patient, and delete any rows for that patient in Sheet2 that fall outside that period. Alternatively I could of course copy the index data from Sheet1 somewhere into Sheet2, if that makes it easier.

At least that's what I had in mind, maybe there's a better way to do it (or maybe it's not even possible at all)

Sorry for the late reply, I just got time to try it out. First I thought it wasn't working properly (tons of dates within the follow-up were flagged as FALSE), then realised some of the dates in the Index sheet were formatted the wrong way. Fixed it and all is good now.

BRILLIANT! Thanks a LOT for that, you saved me many hours of work