You are here:

Excel/Select nearest result time for each admission time for each individual


Excel Query
Excel Query  
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,
Best wishes,


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.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Richard Roberts


Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.


Have been working with Excel for about 20 years primarily in accounting and financial areas.

BA, Certified Public Accountant

©2016 All rights reserved.