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

#### alanend

##### New Member
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### RonB1111

##### Well-known Member
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 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:
Code:
``=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:
Code:
``=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:
Code:
``=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

##### New Member
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

##### Well-known Member
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:
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

##### New Member
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

##### Well-known Member
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.

#### RonB1111

##### Well-known Member
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

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

You're welcome!

Replies
1
Views
5K

1,191,131
Messages
5,984,850
Members
439,920
Latest member
mejatom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back