Tuesday, September 18, 2012

yogi_Filter Form Responses To Show Only The Most Recent (or Latest) Response Submitted By ID


Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #757   Sep 18, 2012     www.energyefficientbuild.com.
UPDATED Jul-24-2013

user mrdanpratt said: (http://productforums.google.com/forum/?zx=lkth9hlcc6om#!category-topic/docs/spreadsheets/o_Y9Nuesggw)

Filter form responses to show only the most recent response if data is duplicated
Let's say I want people to fill out a form with their email address and their favorite color (just for example).  So the spreadsheet (the sheet name is 'Form Responses') has column A with the timestamp, column B with the email address and column C with the color.  Let's say I want to display this data on a second sheet using =ARRAYFORMULA('Form Responses'!A:C) 

Let's say someone responds with red but later they want to change their response to blue.  So they respond again.  I don't want to display both of their choices.  I only want to display all the most recent ones on the second sheet.  Is there a way to create such a filter?  It would be fine to display the filtered data further to the right on the second sheet, like in columns D, E, and F.  Any help would be appreciated.

-------------------------------------------------------------------------------------
following is a solution to the problem

4 comments:

  1. I might be wrong but I believe you made a slight mistake. When I toggle through the sheets of your embedded spreadsheet, the formula in "latest response by ID" is the same formula in "earliest response by ID". Am I missing something?

    ReplyDelete
  2. Hi Dan:

    Thanks for your input ... I have corrected the typo

    formula for latest_response_by_ID is:
    =filter(Sheet1!A2:C,mmult((Sheet1!B2:B=transpose(Sheet1!B2:B))*
    (row(Sheet1!B2:B)<=transpose(row(Sheet1!B2:B))),row(Sheet1!B2:B)^0)=1)

    formula for earliest_response_by_ID is:
    =filter(Sheet1!A2:C,mmult((Sheet1!B2:B=transpose(Sheet1!B2:B))*(row(Sheet1!B2:B)>=transpose(row(Sheet1!B2:B))),row(Sheet1!B2:B)^0)=1)

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com


    ReplyDelete
  3. What if, rather than colors, these submissions were test scores? How could you adapt to not show the most recent timestamp, but rather the highest score?

    ReplyDelete
  4. Hi Unknown:

    It will help to be clear about what exactly you are working with ...

    Please share your spreadsheet with some sample but realistic data, and

    1) tell me where do you want the result posted
    in which cell?
    of which sheet?
    2) show me what is your expected result
    along with needed logic/explanation as to why that is the correct result

    and then let us take it from there.

    Cheers!
    Yogi

    ReplyDelete