Trying to check a list of names (with repeats) against another cell value

[marksman1941]

Hey all,

I'm using Excel 2013. I'm trying to organize a medical database with several hundred clients, and am having trouble with a formula. I need to check and see if a patient has been seen within the past 60 days. Every time a patient is seen, a new entry is made that shows their name, date of visit, etc. I created a function to lookup today's date, turn it into a value, then subtract 60 from that value to give me the date value that is the cutoff. What I'm having trouble with is figuring out how to check if a patient has been seen in the last 60 days. I've uploaded a screenshot w/redacted names to help get my point across.

Essentially, I need a formula that will go through all instances of a name (Column A) and compare each instance to Column I, which states whether that visit was within the past 60 days. If any of the visits return a "Yes", then I want an output (whether that's highlighting the name or displaying it in an adjacent cell, I don't really care) that will tell me that. Likewise, if none of the visits for that name return a "Yes", then I want an output that will notify me they haven't been seen in 60 days.

[nigelbloomy]

Does this work for you if you put it in I2?

Please Login or Register  to view this content.

[marksman1941]

Yes! That worked wonderfully! I didn't expect a response so quickly on here. Maybe you can help me streamline my process here a bit further. Since I have so many patients to go through, I'd like to make another column that checks for the "Yes" or "No" in column I, then returns a list with each name. For example, if John Doe visited 20 times, and some visits were within the past 60 days, the function would check that and return a single instance of John Doe, then go to the next person. This would let me give my providers a list of who should be an open or closed case without having to manually create the list. Thanks again for your help!

Edit: I figured out a clunky way to do it with a Pivot Table, but it seems like there ought to be a formulaic method as well.

[FDibbins]

Welcome to the forum

Can I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[nigelbloomy]

You could do a formula method like this:

Please Login or Register  to view this content.

I applied it on the attached workbook. It gets the job done but it is very computer intensive and you have to drag it down far enough so that you would never miss a name.

I like the pivot table option much better. I also did that on the attached workbook. It isn't clunky at all and it keeps up to date without using a ton of computer power.

[marksman1941]

Hey all,

Sorry it took so long to reply. I got lucky enough to go on vacation over the last 5 days Using the formula that nigelbloomy provided, I figured out a fairly streamlined way of doing it. I download the patient list into Excel, insert a column next to the name column, and paste in the formula (and alter it to refer to the date column). Once that does its magic, I make a pivot table and sort by names that return "No", copy the names into a master sheet, then sort by "Yes" and copy the names over. It takes about 4 minutes from start to finish. I was able to provide my boss with a full practice caseload (8,500+ entries ) as well as individual provider caseloads in under an hour. She expected this to take me two weeks, but underestimated my ability to garner help from wonderful internet strangers lol. Thanks again!