[ms office - 2010] Excel Removing Duplicates

[FatBoyFonz]

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

Edited November 7, 2013 by FatBoyFonz

[LosOjos]

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"

[FatBoyFonz]

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?

Edited November 7, 2013 by FatBoyFonz

[LosOjos]

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)

[FatBoyFonz]

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.

[LosOjos]

Got to a PC now, this is the formula you want:

 

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