+ Reply to Thread
Results 1 to 3 of 3

Query Formula To Find Oldest Duplicates That Contain Certain Keywords - Google Sheets

  1. #1
    Registered User
    Join Date
    06-22-2022
    Location
    AUs
    MS-Off Ver
    NA
    Posts
    8

    Query Formula To Find Oldest Duplicates That Contain Certain Keywords - Google Sheets

    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!

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,531

    Re: Query Formula To Find Oldest Duplicates That Contain Certain Keywords - Google Sheets

    I cannot get to the spreadsheet referenced in post #1; however, this is how I might attempt to accomplish the task if I were using Excel formulas.
    1. On Sheet1 add a column D populated using: =COUNTIFS(B$2:B2,B2,C$2:C2,C2)
    Note that countifs is supported in google sheets.
    2. On Sheet2 filter the data so that only rows of data corresponding to a 1 in column D are displayed.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Query Formula To Find Oldest Duplicates That Contain Certain Keywords - Google Sheets

    Problem #1: column A data is a mix of text and dates.
    Problem #2: you are trying to search for cells that contain the explicit value of "OFF", which no cells have... the data is actually "customer account off"

    to fix Problem #2, the formula should be:
    Please Login or Register  to view this content.
    but as for Problem #1, you need to fix your data so that you only use dates OR text.. not a mixture of both... this is because QUERY will only accept ONE data type in a column... it will NOT accept TWO data types in the same column.

    link to file for others to check:
    https://docs.google.com/spreadsheets...um0/edit#gid=0
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Query(Importrange) in Google Sheets
    By PGLeeM in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 12-10-2021, 05:22 PM
  2. Google Sheets: <=0 vs < =' ' in a query
    By BJANeeeco in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 07-28-2021, 08:44 AM
  3. Query in Google Sheets
    By PhilippBonnie in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 03-26-2021, 10:18 PM
  4. Find the nearest date in a range of dates formula (Google sheets)
    By kaytoc in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-20-2017, 05:20 PM
  5. [SOLVED] Converting Google Sheets QUERY to Correct Excel Formula
    By davis909 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2017, 09:14 AM
  6. How to highlight duplicates in google sheets?
    By ImranBhatti in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-16-2017, 11:37 AM
  7. Formula to find oldest date in group of duplicates
    By chiccaboom in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2016, 11:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1