You are here:

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

Advertisement


Question
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.

ANSWER: Fiona

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
=+IF(AND(A2>0,A2=A4,C2=C4,A1<>A2),1,0)+IF(AND(A2>0,A2<>A1,A2<>A4),1,0)+IF(AND(A2=A1,C2>C1),1,0)

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

Richard
Florida
USA







---------- FOLLOW-UP ----------

Results1
Results1  

Results2
Results2  
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,
Fiona.

Answer
Fiona

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

RIRSTRESS@AOL.COM

Just put Fiona in the subject line and I will know it is from you.

Richard
Florida
USA
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Roberts

Expertise

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.

Experience

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

Education/Credentials
BA, Certified Public Accountant

©2016 About.com. All rights reserved.