Using a logical test(if statements) on two columns to find row specific information

Hi everyone,

So I'm new here. But I was wondering if someone could help me with a quick question for Excel 2010.

In my spreadsheet, I have hundreds of patients listed as first name in column A and last name in column B. Each patient has their own 6 letter/numerical ID in column C, first date of visit recorded in column E and date of the tests they are subjugated to in column F. What I want to do is have excel either search column A and B for new values( or search column C), then based on the occurences of these values, calculate how many days the patient has been in treatment(basically subtracting the first visit date from each test date to give the number of days in between the two).


Each patient has multiple entries for the test data information and we add a lot of data constantly. Previously we had just been subtracting the column E from column F but when we add a lot of information this becomes a hassle to go back and fix. I've tried things similar to this to no avail:

Please Login or Register  to view this content.

The problem I'm having with my code is doing an inquiry to find a patients code and then using the row that the information is found in to calculate the amount of treatment days(E from F) for that same row. I have been using their last name or ID's to search for their specific name/ID but it would be nice if the progam could just find a new value in the ID column and calculate the time in RX for that patient.

Since I realize what I just said might only make sense to me, I've attatched a WorkSample that is obviously much smaller than the sample I'm working with. The sample has the correct amount of time already in column G(where the output is going), I just need to automate it somehow base on the above. Sorry for the inconvenience and thank you ahead of time for the support :D

i think a pivot table with a calculated field should work - using Max date for visit - but cant get a working example right now
i work on later today/tomorrow - unless someone else replies

I have added the calculation

=F2-MAX(IF(C2=C2:C10,E2:E10))
enter
as an array formula - so use
Control+Shift+Enter - to get {} around the formula

I than created a pivot table and used Max for the RX duration

I have also used a table for the data and then that way , you can add extra rows of data
go to the pivot table and click on refresh to include the new data

any questions please ask

see attached name PVT - for the pivot table