Removing Duplicates with Timestamp

Andy0311

New Member
Hello,
I have a column of datestamps in a sheet and I need to separate the date from the time (=Int or =left both work). Then I copy the formula down. With the column highlighted, I go to Data and use the Remove Duplicates function. The result lists the distinct dates, but when I click OK, the dialog box closes and what remains is only the first date times the number of distinct days. For example, here is a column where I want to remove the duplicates. When I do that, in the column to the right, I do not get 9/17/2019, 9/18/2019, and 9/19/2019, but instead 9/17/2019 listed three times.

9/17/20199/17/2019
9/17/20199/17/2019
9/18/20199/17/2019
9/19/2019
9/17/2019

<tbody>
</tbody>


If I do the same operation with :
1
2
1
3


I get:
1
2
3

Does anyone know why that is happening? Thank you for your help.
 

MARK858

Well-known Member
With the original data (after the timestamp is removed) if you change the format of the cells to a number format rather than a date format what do you get returned?
 
Last edited:

SuzB17

New Member
Hi - sorry if you know this already but it is linnked to the fact that the cells contain a formula not data. If you just list out the dates and use the remove duplicates, it works fine. It is only when the list uses the =LEFT formula to create it that the problem occurs. I'm not sure why but you can get around this by using an index formula to find unique values. {=INDEX($A$2:$A$6,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$6),0))} (note it's an array formula). Might be a bit more long winded but works!
 

MARK858

Well-known Member
Removed as on testing SuzB17 appears to be correct.
 
Last edited:

Andy0311

New Member
Thank you so much! I will try this. Do you think using Text to Columns to separate the date portion from the timestamp would work. I will try the =Index. Thanks again.

A
 

Andy0311

New Member
I'm trying it soon. I'm sure it will work. Thanks again.
 

Joe4

MrExcel MVP, Junior Admin
Do you think using Text to Columns to separate the date portion from the timestamp would work.
You can determine if it is date or text by using a formula like this (for an entry in cell A1):
=ISNUMBER(A1)
If it returns TRUE, it is valid date. If it returns FALSE, it is text.

If it is Text, then Text to Columns would probably work on this.

If a valid date, then you can use mathematical functions, like (for a value in cell A1):
Date piece: =INT(A1)
Time piece: =MOD(A1,1)
and use the appropriate formatting.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top