+ Post New Thread
Results 1 to 6 of 6
Office Software Thread, Excel Removing Duplicates in Technical; Hi all, I have a list of date and times (in one column), the data showing is just dd/mm/yyyy hh:mm ...
  1. #1

    Join Date
    Sep 2007
    Posts
    14
    Thank Post
    2
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Excel Removing Duplicates

    Hi all, I have a list of date and times (in one column), the data showing is just dd/mm/yyyy hh:mm but the data for the seconds is also present and if I change the format I can see it. What I would like is to create a list of the unique records but only down to the minute data. Unfortunately if I use the filter option to copy unique records I get a list of dates and times that are unique down to the second.

    Here's a sample of the data;
    05/09/2013 13:02:03
    05/09/2013 14:02:53
    05/09/2013 14:02:55
    05/09/2013 14:02:58
    05/09/2013 14:03:00
    05/09/2013 14:03:02
    05/09/2013 14:03:04
    05/09/2013 14:03:06
    05/09/2013 14:03:08
    05/09/2013 13:02:05
    05/09/2013 13:30:40
    05/09/2013 14:02:33
    05/09/2013 13:01:01
    05/09/2013 14:02:54
    05/09/2013 14:03:07
    05/09/2013 14:02:37
    05/09/2013 14:02:58
    05/09/2013 14:03:03
    05/09/2013 14:02:41


    So the output I'd need would just be 5 records of
    05/09/2013 13:01
    05/09/2013 13:02
    05/09/2013 13:30
    05/09/2013 14:02
    05/09/2013 14:03
    (and preferably in chronological order)

    I have a lot of these to sort on a huge scale so a formula solution would be better, I've tried using =Left but that uses the raw data (e.g. 41522.5430902778 for 13:02:03) and I don't know how/if I can convert that back to the format I need.
    So any help greatly appreciated.
    Thanks
    Last edited by FatBoyFonz; 7th November 2013 at 08:54 AM.

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Can't you just apply a data type to the column? i.e. highlight the column, go to Properties and then "Data Type" (or might be "Number Type" or something, memory fails me), set it to "Custom" and the mask would be "dd/mm/yyyy hh:mm"

  3. #3

    Join Date
    Sep 2007
    Posts
    14
    Thank Post
    2
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Yeah tried that - but the second data is still there, and when filtering it takes it into account. I had to edit the custom type to show me the seconds (as I couldn't understand why my unique list had duplicates).

    Have now tried =TEXT(A2,"dd/mm/yyyy HH:MM") then filtered the results but the filter is still including the second data!
    Edit-
    Ok now I've copied the values to another column and filtered that and it works. Now is there a more automated way I can do this so I can paste my data into the sheet and get a list of unique times (to the minute) without having to potch about with copying and filtering?
    Last edited by FatBoyFonz; 7th November 2013 at 09:45 AM.

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by FatBoyFonz View Post
    Now is there a more automated way I can do this so I can paste my data into the sheet and get a list of unique times (to the minute) without having to potch about with copying and filtering?
    Perhaps a formula to pull out the component parts? Not at a PC right now but something like:

    =day(A1)&"/"&month(A1)&"/"&year(A1)&" "&hour(A1)&":"&minute(A1)

  5. Thanks to LosOjos from:

    FatBoyFonz (7th November 2013)

  6. #5

    Join Date
    Sep 2007
    Posts
    14
    Thank Post
    2
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Yeah that kinda works, but single digit days are created meaning that the sort that needs to come later doesn't work!



    I'll probably stick with what I've got and create a macro to hopefully do the copying and pasting and apply the filter.

  7. #6

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Got to a PC now, this is the formula you want:

    Code:
    =TEXT(A1,"mm/dd/yyyy hh:mm")

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2010] Excel - How to remove date time formatted cell?
    By dany2010 in forum Office Software
    Replies: 9
    Last Post: 6th March 2014, 02:40 PM
  2. [SIMS] Filtering & Removing duplicates
    By Potato2013 in forum MIS Systems
    Replies: 3
    Last Post: 9th September 2013, 01:48 PM
  3. Replies: 1
    Last Post: 17th January 2011, 02:36 PM
  4. Removing Duplicate Folders in a users home
    By Arcath in forum Windows 7
    Replies: 1
    Last Post: 24th February 2010, 03:43 PM
  5. Restricting the use of removeable storage - do you?
    By tarquel in forum School ICT Policies
    Replies: 16
    Last Post: 10th July 2005, 04:13 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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