+ Reply to Thread
Results 1 to 4 of 4

Need help removing duplicate entries wihtin 30 minutes of each other.

  1. #1
    Registered User
    Join Date
    04-28-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    2

    Need help removing duplicate entries wihtin 30 minutes of each other.

    New to the forums, and have a headache I need help with.

    I have a spreadsheet with these columns:

    A: Customer ID (9 or 10 digit numeric)
    B: User ID (7 digit alpha numberic)
    C: Created (mm/dd/yy hh:mm:ss)

    Example:

    Customer ID User ID Created
    123456789 U11U11 04/11/2012 02:35:43
    123456789 U11U11 04/11/2012 02:35:56
    1123456789 X11X11 04/12/2012 08:52:12
    1123456789 X11X11 04/12/2012 08:54:13
    1123456789 X11X11 04/12/2012 08:59:12
    2123456789 X11X11 04/12/2012 09:01:45
    2123456789 T11T11 04/12/2012 09:52:55
    2123456789 X11X11 04/13/2012 12:15:22
    2123456789 X11X11 04/13/2012 12:18:22
    2123456789 T11T11 04/13/2012 01:18:25
    1234567890 T11T11 04/13/2012 02:25:54
    4123456789 J11J11 04/14/2012 17:55:29
    4123456789 J11J11 04/14/2012 18:01:42
    4123456789 J11J11 04/14/2012 18:02:36


    What I need to do, is filter out all of the timestamps by the same User ID, to the same Customer ID, that are within 30 minutes of each other. So in the above example, it would end up as:

    Customer ID User ID Created
    123456789 U11U11 04/11/2012 02:35:43
    1123456789 X11X11 04/12/2012 08:52:12
    2123456789 T11T11 04/12/2012 09:52:55
    2123456789 X11X11 04/13/2012 12:15:22
    2123456789 T11T11 04/13/2012 01:18:25
    1234567890 T11T11 04/13/2012 02:25:54
    4123456789 J11J11 04/14/2012 17:55:29


    Anyone have any ideas? I found something about adding a helper column on the web from 2004, but it is not working for me with the formula provided (found here http://www.mrexcel.com/forum/showthread.php?t=346085 )

    Any help would be greatly appreciated!

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: Need help removing duplicate entries wihtin 30 minutes of each other.

    Hello..

    Upload sample file ...
    Click *, if my suggestion helps you. Have a good day!!

  3. #3
    Registered User
    Join Date
    04-28-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Need help removing duplicate entries wihtin 30 minutes of each other.

    Here is a sample with the above data.
    Attached Files Attached Files

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Need help removing duplicate entries wihtin 30 minutes of each other.

    Perhaps this would be easier to follow if you use two helper columns.

    In D2
    Please Login or Register  to view this content.
    In E2
    Please Login or Register  to view this content.
    Drag/Fill Both Down

    Then the result in F2
    Please Login or Register  to view this content.
    Drag Across to H2, then down as required.

    Note
    I only have 2003 at the moment so you could replace the SUMPRODUCT() formula with COUNTIFS()
    and
    IF(ISERROR()) with IFERROR()

    [EDIT]
    Your data should best be sorted by
    Customer ID then by User ID then by Created
    Attached Files Attached Files
    Last edited by Marcol; 04-29-2012 at 02:43 AM. Reason: corrected column refs
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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