Removing Duplicates with Timestamp

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
58
Office Version
365, 2016
Platform
Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,700
Office Version
365, 2010
Platform
Windows, Mobile
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:

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
58
Office Version
365, 2016
Platform
Windows
I get only the same number. Same result.
 

SuzB17

New Member
Joined
Oct 17, 2019
Messages
5
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,700
Office Version
365, 2010
Platform
Windows, Mobile
Removed as on testing SuzB17 appears to be correct.
 
Last edited:

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
58
Office Version
365, 2016
Platform
Windows
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

Board Regular
Joined
Oct 16, 2019
Messages
58
Office Version
365, 2016
Platform
Windows
I'm trying it soon. I'm sure it will work. Thanks again.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,731
Office Version
365
Platform
Windows
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.
 

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
58
Office Version
365, 2016
Platform
Windows
Worked like a charm! Thanks again.

A
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,731
Office Version
365
Platform
Windows
Curious, which suggestion ended up working for you?
 

Forum statistics

Threads
1,084,838
Messages
5,380,198
Members
401,654
Latest member
etusch

Some videos you may like

This Week's Hot Topics

Top