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
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?
ReplyDeleteHi Dan:
ReplyDeleteThanks 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
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?
ReplyDeleteHi Unknown:
ReplyDeleteIt 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