+ Reply to Thread
Results 1 to 2 of 2

Need a formula to identify transactions that have been reprocessed in a short time frame

  1. #1
    Registered User
    Join Date
    10-27-2016
    Location
    Newcastle, NSW
    MS-Off Ver
    2010
    Posts
    1

    Question Need a formula to identify transactions that have been reprocessed in a short time frame

    I am trying to work out a formula for identifying the occurrences of a certain situation within a data set.

    I need to identify when a transaction has been reprocessed within a short time frame of another similar but not necessarily identical transaction.
    Column A consists of Date
    Column B consists of Time
    Columns C-O consist of other data relevant to the transaction.

    I need a formula that identifies a duplicate in Column A and a difference between the data of Column B of no more than 2 minutes. Is this even possible??

    For example:
    A B
    29/08/2016 18:57:07 < Need to identify
    29/08/2016 18:56:29 < Need to identify
    17/10/2016 12:45:27 < Don't need to identify
    17/10/2016 15:00:44 < Don't need to identify

    I hope this makes sense... I am struggling to get my head around it as it is. Any help would be greatly appreciated
    Last edited by Mel_00; 10-27-2016 at 10:26 PM.

  2. #2
    Registered User
    Join Date
    06-08-2011
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Please help, tricky formula

    If the dates and times are entered sequentially, then just identifying the ones that have the same date as the previous entry and not more than two minutes after the previous entry would work.

    Date Time

    A B C
    1 1-Nov-16 03:34:00
    2 1-Nov-16 03:36:00 Identify
    3 2-Nov-16 04:15:23
    4 2-Nov-16 04:34:00
    5 2-Nov-16 04:36:00 Identify
    6 3-Nov-16 05:11:00
    7 3-Nov-16 06:32:00
    8 3-Nov-16 06:33:00 Identify
    9 3-Nov-16 06:34:00 Identify

    Where the formula in the third column for C2 is: =IF(A2<>A1,"",IF(B2-B1<=0.00138888889,"Identify",""))
    And, of course, copied down throughout column C.

+ 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. Tricky Formula
    By TRI0N in forum Excel General
    Replies: 1
    Last Post: 10-24-2009, 08:21 PM
  2. Tricky Formula
    By ONGExcel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-31-2007, 04:40 AM
  3. Tricky formula
    By martins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2007, 07:18 PM
  4. Tricky Formula
    By martins in forum Excel General
    Replies: 5
    Last Post: 10-17-2006, 08:27 PM
  5. Tricky Formula
    By andrewc in forum Excel General
    Replies: 22
    Last Post: 07-20-2006, 06:45 AM
  6. Help with a tricky formula......
    By nevi in forum Excel General
    Replies: 1
    Last Post: 05-31-2006, 05:45 PM
  7. tricky formula.. please help
    By Aaron H in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-14-2006, 08:36 PM
  8. [SOLVED] Tricky Formula.. Please Help
    By Aaron H in forum Excel General
    Replies: 1
    Last Post: 01-17-2005, 08:06 AM

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