2

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.

  • 1
    Are the comments ordered by timestamps? – user79865 Aug 13 '15 at 16:45
  • @NormalHuman Google Form responses are ordered by timestamp in ascending order by default. – Rubén Aug 13 '15 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? – user79865 Aug 13 '15 at 23:22
  • Yes the comments are ordered by timestamps. The data comes from a google form. – steve Aug 14 '15 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? – steve Aug 14 '15 at 14:45
1

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:

  • filter selects the rows where the username matches the given one;
  • vlookup find 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, vlookup will simply return the last one, i.e., the most recent comment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.