In Google Sheets I have three columns: Timestamp, Username and Comment. How can I search a column for the most recent comment of a username and overwrite the most recent comment, in another cell? There will be up to 8 usernames and comments input from a Google Form.
-
1Are the comments ordered by timestamps?– user79865Commented Aug 13, 2015 at 16:45
-
@NormalHuman Google Form responses are ordered by timestamp in ascending order by default.– Rubén - Volunteer Moderator - ♦Commented Aug 13, 2015 at 18:41
-
When you write "overwrite the most recent comment, in another cell?" do you mean to copy the comment into another cell or overwrite it with the content of another cell?– user79865Commented Aug 13, 2015 at 23:22
-
Yes the comments are ordered by timestamps. The data comes from a google form.– steveCommented Aug 14, 2015 at 13:01
-
everytime the google form is used (via a website) I want the most recent comment by that user to overwrite the last comment in a different cell (a table beside all the data). Does that make sense?– steveCommented Aug 14, 2015 at 14:45
Add a comment
|
1 Answer
So, given a three-column table Timestamp | Username | Comment, you want a separate table that shows the most recent comment by every user: Username | Most Recent Comment. Let's say the first one is in columns A:C, and the other table will be D-E.
I would do the following:
D2 =unique(B2:B)
which picks the usernames from column B, listing each once. Then,
E2 =vlookup(999999, filter(A$2:C, B$2:B=D2), 3)
This does two things:
filterselects the rows where the username matches the given one;vlookupfind the last row where the value in column A is <=999999. Since "999999" corresponds to November 25 of year 4637, it's safe to say that all of your timestamps are below this value. Thus,vlookupwill simply return the last one, i.e., the most recent comment.