6 Replies Latest reply on Jan 9, 2020 7:02 AM by Deepak Rai

    Research survey data - how to use data only from most recent survey taken per participant

    Luke Maschinot



      I am working with some research data in the form of a survey taken yearly by research participants. I have been unable to come up with a method for displaying only the most recent data from any given participant.


      I have attached some sample data that is functionally the same as the actual data I am using. "Participant ID" is an identifier for each individual participant. "Response #" is the ordinal number of the survey for the participant (i.e., 1 is the first survey taken, 2 is the second survey taken, etc.)


      As an example: I would like to create a sheet that displays a crosstab with the most recent response for "How many books did you read in the past year?" for each participant. Thus, it would only display the result from the 3rd survey if one exists, then the 2nd, etc. If this is possible, it would look something like the following:



      Participant IDHow many books have you read in the past year?


      I have only been able to filter to either display only results from year 3 surveys (thus excluding recent year 2 and year 1 surveys), or to aggregate and display the highest/lowest number for each participant (thus displaying some out-of-date results, such as participant B's 67 books read in year 1).


      Thank you!