Which formula to use for this...

hercule_p2001

New member
Joined
Aug 8, 2011
Messages
8
Reaction score
0
Points
0
Hi

I have a worksheet with a list of daily patients that I see. The details that I am interested in(from the many columns) are: Date, Patient Name, Diagnosis, Follow-up Date, Notes. These are the columns I am interested in and they are not contiguous. As can be guessed, the Date column will be in chronological order. The Follow-up date will have no chronology because some patients may be called after a week, some after 2 weeks, some after a month and some after 3 months.

What I need to do is, set up a filter in such a way that on Sheet 2 of the workbook, if I enter a date say todays' date in cell C2, the data from sheet 1 which match the follow-up dates of Sheet2!$C$2 must be listed in Sheet 2. This will enable me to see the possible appointments I have on any given day without going to Filter Data routine every now and then. So in a nutshell, given a date in the said cell, look up all values of column "Follow-up Date" in Sheet1 and copy the matching records to Sheet2. Is there a way to do this?

Thanks.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try this
 

Attachments

  • XLGuru - 330 - Patient Follow-up.xlsx
    10.4 KB · Views: 29

hercule_p2001

New member
Joined
Aug 8, 2011
Messages
8
Reaction score
0
Points
0
Bob
That worked perfectly. Thanks a lot. Can you please explain to me how the array formula that you have written works? Thanks again. Appreciate the effort.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Okay, I'll have a go. I'll chop the range down to 2:20 instead of 2:200 to help show the details.

The main logic is a simple IF test that checks whether the follow-up dates on Patient List equal the user defined date

IF('Patient List'!$D$2:$D$20=$C$2,ROW('Patient List'!$D$2:$D$20))

this builds an array of matching row numbers where the dates match, or FALSE where they do not. In our exampl, with a defined date of 8th Aug, this creates an array of

{2;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

The formula then takes the next smallest value from that array

SMALL(IF('Patient List'!$D$2:$D$20=$C$2,ROW('Patient List'!$D$2:$D$20)),ROW(A1))

We use the SMALL function as that can get the nth smallest values, MIN only gets the very smallest, and we use the variable ROW(A1) to get the 1st smallest, then the 2nd smallest, ..., because A1 increments to A2 on the second row, and so on. So this returns a value of
{2}, then
{6}, and then a series of
{FALSE}

The ISERROR checks this returned value, so if we have an error we output "", just to keep it looking good. If it is not an error, we repeat the SMALL(IF(... to return that row number. This is saved in column A with a white font to hide it.

Finally, we use a simple INDEX(range, row_num) to get the actual value, row_num being the value in column A, range being the variable column, A:A, B:B, etc.
 
Last edited:

jpr73

New member
Joined
Apr 11, 2011
Messages
15
Reaction score
0
Points
0
Location
France
Hello Bob,
You are the Super Doctor Excel Guru.
Thanks
 

hercule_p2001

New member
Joined
Aug 8, 2011
Messages
8
Reaction score
0
Points
0
Bob

That was the best I could have hoped for. Your explanation was really better than what I tried to read in the Excel Help. I had posted this question in another Excel forum too before I found this site. And NO ONE has replied yet! I am sure to return to this forum for my excel questions. Am just going to post a new one.

Thanks.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
hercule_p2001,

I'm really glad that you got your answer here, but can you do me a favour? Please make sure that you go back to the other forum and post that you got an answer here. The reason is not so much that I want links coming here, but I respect the time of other experts out there, and we don't want to waste their time and effort if an answer has been solved. Here's a little more on why: http://www.excelguru.ca/forums/faq.php?faq=crossposting

Again, very glad you got your answer, and hope to see much more of you here. :)
 
Top