-1

I have a google sheets spreadsheet that has lots of user data, timestamped. This data is entered into the google sheet by administration AND end users via a google form, so the data points don't come in at regular intervals. I want to be able to query against this datapool, but limit the results shown based a defined amount of time between result. Data-Sample-1

So say I wanted to query for Bob, I would get 3 results. Is it possible to query this data in a way that it would see the 11\11 result, but disregard the 11\12 result becuase it's within a week of the previous result? Really I'd like to be able to do this for any timeframe.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
d90
  • 767
  • 2
  • 10
  • 28
  • Possible. You would sort your data, find the time difference between adjacent timestamps and then pass it to query, where the diff >1wk. Share your sheet. – TheMaster Nov 21 '18 at 21:08
  • 2
    Trying to think this through - If you had 3 timestamps each four days apart, presumably you would want to keep the first and third, because they are more than a week apart? – Tom Sharpe Nov 21 '18 at 22:34

1 Answers1

1

Well it's easy enough to do as an ordinary formula that you can pull down like this, but I don't see a way of doing it as a query or array formula:

=iferror(if(istext(F1),index(A$2:A,match(true,(B$2:B=$F$1),0)),index(A$2:A,match(1,(B$2:B=$F$1)*((A$2:A-F1)>=7),0))))

where the person's name is in F1.

enter image description here

This is a fairly rough outline of an answer, I haven't taken account of any time part of the timestamps. The last two dates are only separated by 6 days, but the formula includes the third to last and the last one as mentioned in my comment because they are separated by 7 days.

Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37