consolidating data from many rows per subject into medians and means per variable in a single row

Copper Contributor

Hi gang,


I have 64 rows of measurement data (reaction time, decision accuracy, memory recall accuracy) for 30+participants with 3 manipulated test variables (high/low, 1/2/3/4, difficult/medium).  This gives me over 2000 rows of data.   The cells contain a mix of time values that measure a reaction speed and binary values that compare an answer to truth data.  They also contain some demographic data that are consistent across all rows for that participant.


Could you please walk me through how to establish a function(s) to consolidate my data into a single row for each subject in a separate worksheet capturing median for the time variables and % correct for the binary variables? 


Output I am looking for is one summary row for each participant with a single consolidated value for each of the test variables (e.g. response time mean for Low, response time mean for 1, etc).


Many thanks,


8 Replies
It is very do-able. Do you happen to have some anonymized sample data to share?

Hi @Patrick2788 ,

Is there an option in here to attach a .xls file or do I need to cut and paste into a table within the chat?

You may post into a message here if you like. Several sample rows would be great.


@Patrick2788 ,

sorry it's an image rather than a table...



This helps. Looking at this example, what's the desired result for 516288?

@Patrick2788 ,

ideally I’m trying to get to a single row for each participant with a mean time for each of the manipulated trial conditions (e.g. a cell/column for median time for WM_load Low, then WM_Load High, then for Visualisation_Grouped, Numeric and so on. 
I’m also trying to combine all of the binary accuracy values into a %correct cell/column for each of the manipulated trial conditions


at the end, I would like to have just a single row for each subject. 

There are currently 34 subjects each with 64 rows of data that match the different combinations of the three variables. 

Have you considered creating a PivotTable to consolidate the data eliminate redundancies?
I'm happy to consider it, but I'm not experienced at doing so.