Complex query advice needed: selecting highest test value among up to six tests according to year

[alanend]

Hi all,

I have a data set with the variables below. To summarize, each patient had six tests on six different days (all recorded in the same row), and we are interested in the best test score for tests that occurred in the year we are interested in for our study. So, if “Year” is 2012, we want to drop any tests for that patient that occurred in a year other than 2012, and among those tests that occurred in 2012, select the test score with the highest value, then retain height and weight for the selected test number.

Each patient's year is entered individually, so patient 1 may have 2012 but patient 2 may have 2014 so we cannot do a query of the entire database for only 2012. It has to be row by row according to that patient's year of interest.

I realize that the data should have been set up differently but this is how it was given to me. The file has too many records to do it manually.

Thank you!
***********
Patient ID (unique identifier)
Year (year of interest for the study)
Test1Date(dd/mm/yyyy of the first test for that patient)
Test1 (first test score)
Test2Date(dd/mm/yyyy of the second test for that patient)
Test2 (second test score)
...all the way to
Test6Date(dd/mm/yyyy of the sixth test for that patient)
Test6 (sixth test score)
Height1 (height for test 1)
Height 2 (height for test 2)..all the way to Height 6.
Ditto for weight 1, weight 2.

 

[alanend]

Me again.

I want to do the exact same thing, but this time I have additional test data for each date representing different types of tests performed on that date. So for each date, I have test A-D plus the same height and weight for 1-6 for each of the same dates as before. In other words:
Patient ID (unique identifier)
Year (year of interest for the study)
Test1Date(dd/mm/yyyy of the first test for that patient)
ATest1 (first test score for test type A)
BTest1 (first test score for test type B)
CTest1
DTest1
Test2Date(dd/mm/yyyy of the second test for that patient)
ATest2 (second test score for test type B)
BTest2
...all the way to
Test6Date(dd/mm/yyyy of the sixth test for that patient)
Test6 (sixth test score)
Height1 (height for test 1)
Height 2 (height for test 2)..all the way to Height 6.
Ditto for weight 1, weight 2.

See:
https://docs.google.com/spreadsheet...GVd6YmkkS3G3Wxw1dQgd2BUfmg/edit#gid=338777305

Can you tell me how to do the same thing but pull over A-D tests for the selected test data?

Also, is there an easy way to autofill missing dates? I would like any missing date data to be 1/1/1999; I did this once before but now I can't remember how I did it!

Thank you!
Danielle

 

[RonB1111]

Your link requires a login, but I understand what your new data looks like. The way your data is structured, I can't think of any formula(s) that wouldn't be ridiculously long. Suggest you break the data into 4 separate sheets in the same format as your original post and then use the formula set previously given. Here a 2 alternative ways to set up those 4 sheets:
1. (Better method) - Name your sheet (I'll use Master). Add 4 additional sheets. Use a simple formula like =Master!A1 to bring over relevant column headers onto each sheet and then copy down so that when it's done each sheet will have the relevant data for just 1 test and the columns will be the same as your original post.
2. (OK method). Make 4 copies of your data sheet. Then on one copy delete columns not related to test1, on a 2nd sheet delete columns not related to test2, etc.
The 1st method is better because once it's done you can use it as a template anytime you get a new data set.

To autofill the missing dates:
1. Select the range of date cells involved
2. Press the function key F5
3. Click "Special"
4. Click "Blanks"
5. Click OK
6. Type: 1/1/1999
7. Press Ctrl-Enter keys at the same time