How to convert repeated measures from rows to columns in Excel

Question

I have a data file of approximately 5000 repeated measures organized with rows containing IDs and repeated measures on weight, BMI, etc for children. I would like to find the maximum value of one variable (BMI) for each individual (out of up to 9 records). How can I do a lookup on multiple rows for each ID and return the max of a value for each person?

A very abbreviated example is as follows: HAVE:

ID  Date    BMI
1   1   20
1   2   18
1   3   24
2   4   23
2   5   19
2   6   17
3   7   25
3   8   18
3   9   21

WANT

ID  Highest BMI Corresponding date
1   24  3
2   23  4
3   25  7

Alternatively if there is a way to do this in SPSS or JMP (I don't have access to SAS now), please let me know. Thanks! Melissa

Answer 1

You can do this easily in Excel in two parts

• A PivotTable to extract the maxiumum BMI for each ID
• matching the maximum BMI per ID to a date

Part 1 - PivotTable

Create a PivotTable with

1. A Row Label of ID
2. Values as Max of BMI see below Part 2 - matching the date In the cell to the right of tge first BMI maximum, put this formula
   =SUMPRODUCT(--($A$2:$A$10=B14),--($C$2:$C$10=C14),$B$2:$B$10)/SUMPRODUCT(--($A$2:$A$10=B14),--($C$2:$C$10=C14))

(ensure you re-map your ranges if they differ from this example)

This formula the record that matches the ID and Max BMI