Excel/Select nearest result time for each admission time for each individual
QUESTION: Hi Richard,
I have limited excel knowledge and wonder if you could help me please.I have a list of individuals, each has an admission date and date a sample was taken. However some individuals have multiple samples taken. I want to include the first sample time for each admission date for each individual.I hope the attachement will illustrate what I am looking for.
Thank you for your time and help in advance.
Sorry I couldn't get back to you quicker. Here is how I would handle the problem.
FIrst sort your data.
The sort should be done as follows
First by ID number
Second by Admissions date
Third by time of sample
Second, in column G put this formula
You can copy this formula and paste it directly into cell G2.
The result of the formula will be a 1 if it is the first test of each patient for each admission and a 0 if it is other than the first test for each patient for each admission.
I believe I have tested the formula to allow for all circumstances you might run into, but if you find a situation where it isn't working let me know.
Two other points.
If you want to leave your data as it is and do not want to do the sort of your original data, I suggest that you copy the data to another worksheet or another part of the same worksheet and then apply the formula. Of course you will need to change the formula references to allow for the change where necessary.
If you want to isolate the 1's in your data I suggest that, after you have done your sort and applied the formula, you copy the data and use the paste special/values to gain data without formulas. You can then sort that data
by column G and isolate the 1's from the 0's.
Hope this helps
---------- FOLLOW-UP ----------
QUESTION: Thank you Richard for your quick response, I was delighted to get your help. I have a follow up query however. The formula worked in the main but there were some false negative results. I have included two images of these which I highlighted, could you see why this may occur?
Thank you again,
From your screenshots, I cannot see any reason that the formula isn't working on the ones that are not correct. Send me your actual worksheet and leave them highlighted, and I will then be able to tell what the issue is and what needs tweaking in the formula.
Send it to
Just put Fiona in the subject line and I will know it is from you.