Forum Discussion
Deleting multiple values from the same second
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!
9 Replies
You may remove duplicates by Data-Remove Duplicates
assuming you have proper sorted data
Result is
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!
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")
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.
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.
