Hi guys! Thanks for taking the time out to read this post & help me out here.
I'm attempting to create a Query / Arrayformula (or whatever works, really) formula on Google Sheets that can list out two things for us:
1. When a customers' subscription has been created
2. When the same customers' subscription has deleted
Thing is; this happens multiple times for clients (some clients can create & delete their subscriptions for our service multiple times per month).
We have a running log on a master sheet on when this happens, but we have needed to create another worksheet to simplify it all for viewing purposes.
Essentially we're hoping to only pull in the oldest 2 records for a specific lead/customer: one entry showing the oldest time their subscription had been created, and the other entry being the oldest time their subscription had been deleted.
Everything else after this isn't required (all the other times they've created/deleted their account).
It's only the first time around we're looking at it for statistical purposes.
In this master sheet, we have all data we need; including dates (Column A), a unique customer ID (Column B) and the note of when their subscription had been turned on/off (Column C)
However, after trying all day to create this formula I'm heavily struggling. I've tried to create a query formula where it contains the keywords in the Notes such as 'on' and 'off' to list all items - and this works.
But, of course, it just brings in every single entry. I cannot find a way to only show the first time(s) this happened for both on & off purposes based on dates.
You can see the yellow highlighted cell on the "Simplified" worksheet for the formula.
Is this possible by any chance? I've never really seen it done, however happy to learn from you all.
The spreadsheet is located at:
docs(dot)google(dot)com/spreadsheets/d/1R2BexV0XCP04ecaFa53G0i8ukDhG97MNh7dlfvTLum0/
In summary: trying to implement a filter to get first values of the column containing the keywords 'off' and/or 'on'
I've also tried doing a simple data filter, however this still lists the multiple instances where this happens for each customer - not sure how to narrow it down to just the oldest for both on and off scenarios.
For reference, these are the threads I've researched to possibly help:
blog(dot)coupler(dot)io/filter-function-google-sheets/
webapps(dot)stackexchange(dot)com/questions/107034/find-latest-entry-of-a-duplicate-row-in-google-sheets
I apologise for the manual work required to access the above - I am not yet able to post links here!
Bookmarks