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

alanend

New Member
Joined
Aug 28, 2014
Messages
9
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
Joined
Nov 28, 2011
Messages
2,277
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:
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))
 
Upvote 0

alanend

New Member
Joined
Aug 28, 2014
Messages
9
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:

123456123456
patient_idYEARTestdate1test1Testdate2test2Testdate3test3Testdate4test4Testdate5test5Testdate6test6height1height2height3height4height5height6weight1weight2weight3weight4weight5weight6
420125/18/2012852/28/2012811/31/20128010/18/2011847/19/2011850175175175175175075.477.376.775.7760#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
 
Upvote 0

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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
 
Upvote 0

alanend

New Member
Joined
Aug 28, 2014
Messages
9
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!
 
Upvote 0

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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.
 
Upvote 0

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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.
 
Upvote 0

alanend

New Member
Joined
Aug 28, 2014
Messages
9
Thank you again!!! I filled in the blanks with a nonsense year that could not possibly match the year of interest and it worked!
 
Upvote 0

Forum statistics

Threads
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?

Disable AdBlock

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
Top