Sep 10 2019 08:53 AM
Hello all,
I'm hoping someone can help with this issue I am having
I have a data set which includes multiple measurements per second, and I want to filter this data set so that it only includes the first measurement taken every second.
For example, a sample of my data set looks like:
12:04:20 PM | -2.5058022 | 1.025907 | 0 |
12:04:20 PM | -2.5058022 | 1.025907 | -0.64 |
12:04:20 PM | -2.5058022 | 1.025907 | -0.64 |
12:04:20 PM | -2.5058022 | 1.025907 | -0.6 |
12:04:21 PM | -2.5058022 | 1.025907 | -0.6 |
12:04:21 PM | -2.5058022 | 1.025907 | -0.59 |
12:04:21 PM | -2.5058022 | 1.025907 | -0.59 |
12:04:21 PM | -2.5058022 | 1.025907 | -0.6 |
12:04:21 PM | -2.5058022 | 1.025907 | -0.6 |
12:04:22 PM | -2.5058022 | 1.025907 | -0.59 |
12:04:22 PM | -2.5058022 | 1.025907 | -0.59 |
12:04:22 PM | -2.5058022 | 1.025907 | -0.59 |
And I would like to delete all rows for multiple measurements taken in the same second, so in the end it should look like ..
12:04:20 PM | -2.5058 | 1.025907 | 0 |
12:04:21 PM | -2.5058 | 1.025907 | -0.6 |
12:04:22 PM | -2.5058 | 1.025907 | -0.59 |
Thanks so much for your help!
Sep 10 2019 09:26 AM - edited Sep 10 2019 09:30 AM
Sep 10 2019 09:26 AM - edited Sep 10 2019 09:30 AM
Assuming your data is in column A:D then create a helper column E with the following formula...
=COUNTIF(A$2:A2,A2)=1
and then filter the column E with TRUE values.
Please refer to the attached for details.
Sep 10 2019 09:45 AM
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?
Sep 10 2019 09:52 AM - edited Sep 10 2019 09:52 AM
Sep 10 2019 09:52 AM - edited Sep 10 2019 09:52 AM
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.
Sep 10 2019 10:09 AM
You may remove duplicates by Data-Remove Duplicates
assuming you have proper sorted data
Result is
Sep 10 2019 10:32 AM
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?
Sep 10 2019 10:34 AM
I tried that first, but it seems excel will not recognize my time data as the same value. There must be some trailing milliseconds that I cannot see, even though I have formatted the cells to the custom h:mm:ss. Do you know of another way to truncate the data to get ride of any trailing milliseconds?
Thank you!
Sep 10 2019 10:49 AM - edited Sep 10 2019 10:50 AM
Sep 10 2019 10:49 AM - edited Sep 10 2019 10:50 AM
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.
Sep 10 2019 11:00 AM
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.
Sep 10 2019 01:16 PM
Or you may apply Remove Duplicates to the helper column which @Subodh_Tiwari_sktneer suggested.
If you'd like not cut milliseconds but round to nearest second, when
=MROUND(A2,"00:00:01")