Search code examples
sqlgoogle-sheetsgoogle-sheets-query

Query that returns results that are at least a week apart

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.

Solution

  • 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.