Deleting multiple values from the same second

Copper Contributor

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.50580221.0259070
12:04:20 PM-2.50580221.025907-0.64
12:04:20 PM-2.50580221.025907-0.64
12:04:20 PM-2.50580221.025907-0.6
12:04:21 PM-2.50580221.025907-0.6
12:04:21 PM-2.50580221.025907-0.59
12:04:21 PM-2.50580221.025907-0.59
12:04:21 PM-2.50580221.025907-0.6
12:04:21 PM-2.50580221.025907-0.6
12:04:22 PM-2.50580221.025907-0.59
12:04:22 PM-2.50580221.025907-0.59
12:04:22 PM-2.50580221.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.50581.0259070
12:04:21 PM-2.50581.025907-0.6
12:04:22 PM-2.50581.025907-0.59

 

Thanks so much for your help!

9 Replies

@G_excel_questions 

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.

 

 

@Subodh_Tiwari_sktneer 

 

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?

@G_excel_questions 

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.

@G_excel_questions 

You may remove duplicates by Data-Remove Duplicates

image.png

assuming you have proper sorted data

Result is

image.png

@Subodh_Tiwari_sktneer 

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?

 

@Sergei Baklan 

 

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!

@G_excel_questions 

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.

@Subodh_Tiwari_sktneer 

 

It is unfortunately still not working, as this is what I am getting:

 

12:04:20-2.50580221.02590712:04:20TRUE
12:04:20-2.50580221.02590712:04:20FALSE
12:04:20-2.50580221.02590712:04:20FALSE
12:04:20-2.50580221.02590712:04:20TRUE
12:04:21-2.50580221.02590712:04:21TRUE
12:04:21-2.50580221.02590712:04:21FALSE
12:04:21-2.50580221.02590712:04:21FALSE
12:04:21-2.50580221.02590712:04:21TRUE
12:04:21-2.50580221.02590712:04:21TRUE
12:04:22-2.50580221.02590712:04:22TRUE
12:04:22-2.50580221.02590712:04:22TRUE
12:04:22-2.50580221.02590712:04:22FALSE
12:04:22-2.50580221.02590712:04:22TRUE
12:04:22-2.50580221.02590712:04:22FALSE
12:04:23-2.50580221.02590712:04:23TRUE
12:04:23-2.50580221.02590712:04:23TRUE
12:04:23-2.50580221.02590712:04:23TRUE
12:04:23-2.50580221.02590712:04:23TRUE
12:04:23-2.50580221.02590712:04:23TRUE
12:04:24-2.50580221.02590712:04:24TRUE
12:04:24-2.50580221.02590712:04:24TRUE
12:04:24-2.50580221.02590712:04:24TRUE
12:04:24-2.50580221.02590712:04:24TRUE
12:04:24-2.50580221.02590712:04:24TRUE
12:04:25-2.50580221.02590712:04:25TRUE
12:04:25-2.50580221.02590712:04:25TRUE
12:04:25-2.50580221.02590712:04:25TRUE
12:04:25-2.50580221.02590712:04:25TRUE
12:04:25-2.50580221.02590712:04:25TRUE

 

But when I manually enter a subset of the times, it works correctly and produces this:

 

12:04:20TRUE
12:04:20FALSE
12:04:20FALSE
12:04:20FALSE
12:04:21TRUE
12:04:21FALSE
12:04:21FALSE
12:04:21FALSE
12:04:21FALSE
12:04:22TRUE
12:04:22FALSE
12:04:22FALSE
12:04:22FALSE
12:04:22FALSE
12:04:23TRUE
12:04:23FALSE
12:04:23FALSE
12:04:23FALSE
12:04:23FALSE
12:04:24TRUE
12:04:24FALSE
12:04:24FALSE
12:04:24FALSE
12:04:24FALSE
12:04:25TRUE
12:04:25FALSE
12:04:25FALSE
12:04:25FALSE
12:04:25FALSE

 

Thanks so much for all of your input.

@G_excel_questions 

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")