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.

 

[RonB1111]

For these formulas to work, it's important your columns are set up as described. Formulas are based on sample data in Cols A thru Z.
---Test 1 date is in Col C, Test 1 Score in Col D, etc
---Height1 is in Col O, Height2 in Col P etc
---Weight1 is in Col U, Weight2 in Col V etc

It is also critical that you enter these numbers in a blank row above your headers.
So, in row 1, please enter 1 in O1, 2 in P1, 3 in Q1, 4 in R1, 5 in S1 and 6 in T1.
Also in row 1, please enter 1 in U1, 2 in V1, 3 in W1, 4 in X1, 5 in Y1 and 6 in Z1.

Your headers are in row 2.
Your data begins in row 3.

Please enter these formula in row3 and copy down:

In AA3 for the highest score in the year of interest:

=MAX(IF(YEAR(C3)=B3,D3,0),IF(YEAR(E3)=B3,F3,0),IF(YEAR(G3)=B3,H3,0),IF(YEAR(I3)=B3,J3,0),IF(YEAR(K3)=B3,L3,0),IF(YEAR(M3)=B3,N3,0))

In AB3 for the associated height:

=INDEX(O3:T3,MATCH(MATCH(MAX(IF(YEAR(C3)=B3,D3,0),IF(YEAR(E3)=B3,F3,0),IF(YEAR(G3)=B3,H3,0),IF(YEAR(I3)=B3,J3,0),IF(YEAR(K3)=B3,L3,0),IF(YEAR(M3)=B3,N3,0)),C3:N3,0)/2,$O$1:$T$1,0))

In AC3 for the associated weight:

=INDEX(U3:Z3,MATCH(MATCH(MAX(IF(YEAR(C3)=B3,D3,0),IF(YEAR(E3)=B3,F3,0),IF(YEAR(G3)=B3,H3,0),IF(YEAR(I3)=B3,J3,0),IF(YEAR(K3)=B3,L3,0),IF(YEAR(M3)=B3,N3,0)),C3:N3,0)/2,$U$1:$Z$1,0))

 

[alanend]

Thank you so much for this advice! I think I must be doing something wrong because I am getting error messages. One thing to note is that some patients won't have six tests so there is missing data for some. I also wonder if my date formats are off? I don't know how to attach my data to this message but I have pasted below. Not sure if you can read it:

														1	2	3	4	5	6	1	2	3	4	5	6
patient_id	YEAR	Testdate1	test1	Testdate2	test2	Testdate3	test3	Testdate4	test4	Testdate5	test5	Testdate6	test6	height1	height2	height3	height4	height5	height6	weight1	weight2	weight3	weight4	weight5	weight6
4	2012	5/18/2012	85	2/28/2012	81	1/31/2012	80	10/18/2011	84	7/19/2011	85		0	175	175	175	175	175	0	75.4	77.3	76.7	75.7	76	0	#VALUE!	#VALUE!	#VALUE!

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col span="18"></colgroup><tbody>
</tbody>

Thanks again,
Danielle

 

[RonB1111]

Danielle,

Your sample data worked correctly for me, so I've uploaded my excel file for you to download and see. Here's the link:
https://drive.google.com/file/d/0B1mqGQ8FC-rCdjEtcTVxZXNTczg/edit?usp=sharing
Your sample data is highlighted in blue below my test data.

Your sample has the same columns and in the same order as mine. You've also have the 123456's in the proper place. The dates do need to be in excel "Date" format - any "Date" format will do. I can't see the formula's you used, it only comes thru as #Value, so that's an area to check. If you can't figure it out, please upload your sample excel file and paste the link in a post here so I can see it. Make sure anyone with the link can open it without a password.

Let me know what you find.

Ron

 

[alanend]

Thank you. I think I figured it out. The formula only works (for me) when all of the fields are populated. For those patients it works fine. The creators of the sheet entered "0" for all data points on patients who did not take one of tests, and for those it will not work and I get the error messages. When I remove the "0" for those missing test and leave it blank, it still doesn't work. It has something to do with missing data when a patient has fewer than six tests completed.

Thank you for your help. I am very close!

 

[RonB1111]

Did you see my excel file that the formula works with blanks and 0? I can get a #value error if I entered text or a spacebar in one of the date fields. Can you upload your file to a file sharing service? -delete any real names or confidential data 1st.

 

[alanend]

I hope this works:
https://drive.google.com/file/d/0B7axLjWarZSgMmY4eWFWaVVLS00/edit?usp=sharing

Thank you!

 

[RonB1111]

There is something in the blank date cells causing most of the problems. Not sure exactly what it is, but here's a quick solution.
1. Select Col C
2. From the ribbon, select Data - TextToColumns
3. Click "Finish"
Repeat these steps for Columns E, G, H, K and M

A few of your date fields have 0000/00/00 in them. Best to just delete those contents.

 

[alanend]

Thank you again!!! I filled in the blanks with a nonsense year that could not possibly match the year of interest and it worked!

 

[RonB1111]

You're welcome!