+ Reply to Thread
Results 1 to 7 of 7

Flag Yes If CustomerID occurs again within 3 days

  1. #1
    Registered User
    Join Date
    01-21-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    5

    Flag Yes If CustomerID occurs again within 3 days

    Hi,
    I have the following named Ranges
    Activity ID Numeric, Unique --Key Field
    Customer Name Name
    Customer ID Numeric,
    Ticket ID Numeric
    ClosedDate1 Date
    AgentID1 FLast
    SCIP Event Yes / No

    What I am attempting to do, is Flag SCIP Event With a "Yes" for each activity ID where the Customer ID has a duplicate with a higher Activity ID and occurs within 3 days.
    I currently have the following, which works, but requires an advanced sort of Customer ID and Ticket ID on the raw data page (20k rows currently) to work properly, this has to be redone every time the raw data is updated (daily)

    =IFERROR(IF(AND(COUNTIFS(CustomerID,[@[Customer ID]])>1,OFFSET([@ClosedDate1],1,0,1,1)-[@ClosedDate1]<4,OFFSET([@[Customer ID]],1,0,1,1)=CustomerID),"Yes","No"),"No")

    I initially tried using an If(And(Index(Match)))), which obviously failed due to match only going to the first record. I would prefer to do this without VBA, but any working solutions would be appreciated.

    Sample file attached.
    SCIPSample.xlsx
    Last edited by wrush; 02-02-2016 at 06:05 PM.

  2. #2
    Registered User
    Join Date
    01-21-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    5

    Re: Flag Yes If CustomerID occurs again within 3 days

    Bumping, any assistance would be appreciated greatly.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Flag Yes If CustomerID occurs again within 3 days

    Shouldn't Row 18 be Yes? Or is 1/14 considered more than 4 days from 1/10?

    Try this

    =IF(SUMPRODUCT(--([Date]>=[@Date]),--( [Date]<= [@Date]+3),-- ([Activity ID]>[@[Activity ID]]), --([Customer ID]=[@[Customer ID]])), "Yes", "No")
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    01-21-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    5

    Re: Flag Yes If CustomerID occurs again within 3 days

    Its within 3 days that I need flagged as Yes

    This appears to work, It does on the sample data, I am testing it on the full data now, will take a few.... Thanks sooo much. I will update when I know for certain.
    Last edited by wrush; 02-02-2016 at 05:49 PM.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Flag Yes If CustomerID occurs again within 3 days

    Did you try that formula?

  6. #6
    Registered User
    Join Date
    01-21-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    5

    Re: Flag Yes If CustomerID occurs again within 3 days

    trying it now

  7. #7
    Registered User
    Join Date
    01-21-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    5

    Thumbs up Re: Flag Yes If CustomerID occurs again within 3 days

    It works! Thank You soo much.

+ 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. Excel 2007 : Flag up?
    By Ghost123 in forum Excel General
    Replies: 4
    Last Post: 11-15-2011, 05:46 AM
  2. lookup and flag
    By annabelle1 in forum Excel General
    Replies: 4
    Last Post: 10-14-2011, 04:33 PM
  3. Red Flag
    By sgt.bsc in forum Excel General
    Replies: 3
    Last Post: 06-30-2010, 03:19 PM
  4. Flag each group in col C if one or more flag in col A
    By oldchippy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-17-2008, 03:42 AM
  5. Flag 1,3,5 and then every 5 after that?
    By dcnc in forum Excel General
    Replies: 2
    Last Post: 01-19-2007, 03:56 PM
  6. Excel flag
    By Max_power in forum Excel General
    Replies: 3
    Last Post: 03-08-2006, 11:04 AM
  7. [SOLVED] Flag box
    By Rossella in forum Excel General
    Replies: 3
    Last Post: 02-20-2006, 11:30 AM
  8. [SOLVED] flag cells
    By M.A. in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-05-2005, 05:00 PM

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