Forum Discussion
Deleting multiple values from the same second
Thank you for the reply!
But when I enter the =COUNTIF(A$2:A2,A2)=1 formula into column E, all of the values in column E end of being TRUE, except for the very last one..
Am I missing something?
If that's the case, it means all your values in column A are unique values. They may look duplicate but maybe they contain some leading or trailing spaces which make them all unique somehow.
Did you check the file I uploaded? It contains your sample data along with the formula in column E and data is filtered in column E for True values.
Also, check if the Calculation Options is set to Manual, if so, set it to Automatic.
If that doesn't resolve your issue, please upload the truncated version of your file in the question.
I think you are right, there must be some trailing milliseconds attached to my time that I cannot see, because your code does work when I manually type out a few lines of the data ( i.e enter 12:04:20). I have tried to reformat my time column to hh:mm:ss, but it still does not work. Is there a better way to truncate my time data to milliseconds are not included, and so that COUNTIF will recognize them as the same value?
If the time value includes the milliseconds in column A, you may insert another helper column to extract just the hh:mm:ss portion from the time value using the formula...
In E2
=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
And then insert the helper column I suggested first...
In F2
=COUNTIF(E$2:E2,E2)=1
And now you may filter the column F for True values.
Let me know if that works for you.
It is unfortunately still not working, as this is what I am getting:
12:04:20 -2.5058022 1.025907 12:04:20 TRUE 12:04:20 -2.5058022 1.025907 12:04:20 FALSE 12:04:20 -2.5058022 1.025907 12:04:20 FALSE 12:04:20 -2.5058022 1.025907 12:04:20 TRUE 12:04:21 -2.5058022 1.025907 12:04:21 TRUE 12:04:21 -2.5058022 1.025907 12:04:21 FALSE 12:04:21 -2.5058022 1.025907 12:04:21 FALSE 12:04:21 -2.5058022 1.025907 12:04:21 TRUE 12:04:21 -2.5058022 1.025907 12:04:21 TRUE 12:04:22 -2.5058022 1.025907 12:04:22 TRUE 12:04:22 -2.5058022 1.025907 12:04:22 TRUE 12:04:22 -2.5058022 1.025907 12:04:22 FALSE 12:04:22 -2.5058022 1.025907 12:04:22 TRUE 12:04:22 -2.5058022 1.025907 12:04:22 FALSE 12:04:23 -2.5058022 1.025907 12:04:23 TRUE 12:04:23 -2.5058022 1.025907 12:04:23 TRUE 12:04:23 -2.5058022 1.025907 12:04:23 TRUE 12:04:23 -2.5058022 1.025907 12:04:23 TRUE 12:04:23 -2.5058022 1.025907 12:04:23 TRUE 12:04:24 -2.5058022 1.025907 12:04:24 TRUE 12:04:24 -2.5058022 1.025907 12:04:24 TRUE 12:04:24 -2.5058022 1.025907 12:04:24 TRUE 12:04:24 -2.5058022 1.025907 12:04:24 TRUE 12:04:24 -2.5058022 1.025907 12:04:24 TRUE 12:04:25 -2.5058022 1.025907 12:04:25 TRUE 12:04:25 -2.5058022 1.025907 12:04:25 TRUE 12:04:25 -2.5058022 1.025907 12:04:25 TRUE 12:04:25 -2.5058022 1.025907 12:04:25 TRUE 12:04:25 -2.5058022 1.025907 12:04:25 TRUE But when I manually enter a subset of the times, it works correctly and produces this:
12:04:20 TRUE 12:04:20 FALSE 12:04:20 FALSE 12:04:20 FALSE 12:04:21 TRUE 12:04:21 FALSE 12:04:21 FALSE 12:04:21 FALSE 12:04:21 FALSE 12:04:22 TRUE 12:04:22 FALSE 12:04:22 FALSE 12:04:22 FALSE 12:04:22 FALSE 12:04:23 TRUE 12:04:23 FALSE 12:04:23 FALSE 12:04:23 FALSE 12:04:23 FALSE 12:04:24 TRUE 12:04:24 FALSE 12:04:24 FALSE 12:04:24 FALSE 12:04:24 FALSE 12:04:25 TRUE 12:04:25 FALSE 12:04:25 FALSE 12:04:25 FALSE 12:04:25 FALSE Thanks so much for all of your input.