• 512K Members
• 5,329 Online
• 609K Conversations

## Deleting multiple values from the same second

Occasional Contributor

# 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.5058 1.02591 0 12:04:20 PM -2.5058 1.02591 -0.64 12:04:20 PM -2.5058 1.02591 -0.64 12:04:20 PM -2.5058 1.02591 -0.6 12:04:21 PM -2.5058 1.02591 -0.6 12:04:21 PM -2.5058 1.02591 -0.59 12:04:21 PM -2.5058 1.02591 -0.59 12:04:21 PM -2.5058 1.02591 -0.6 12:04:21 PM -2.5058 1.02591 -0.6 12:04:22 PM -2.5058 1.02591 -0.59 12:04:22 PM -2.5058 1.02591 -0.59 12:04:22 PM -2.5058 1.02591 -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.02591 0 12:04:21 PM -2.5058 1.02591 -0.6 12:04:22 PM -2.5058 1.02591 -0.59

Thanks so much for your help!

9 Replies

# Re: Deleting multiple values from the same second

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.

# Re: Deleting multiple values from the same second

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?

# Re: Deleting multiple values from the same second

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.

# Re: Deleting multiple values from the same second

You may remove duplicates by Data-Remove Duplicates

assuming you have proper sorted data

Result is

# Re: Deleting multiple values from the same second

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?

# Re: Deleting multiple values from the same second

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!

# Re: Deleting multiple values from the same second

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.

# Re: Deleting multiple values from the same second

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

 12:04:20 -2.5058 1.02591 12:04:20 TRUE 12:04:20 -2.5058 1.02591 12:04:20 FALSE 12:04:20 -2.5058 1.02591 12:04:20 FALSE 12:04:20 -2.5058 1.02591 12:04:20 TRUE 12:04:21 -2.5058 1.02591 12:04:21 TRUE 12:04:21 -2.5058 1.02591 12:04:21 FALSE 12:04:21 -2.5058 1.02591 12:04:21 FALSE 12:04:21 -2.5058 1.02591 12:04:21 TRUE 12:04:21 -2.5058 1.02591 12:04:21 TRUE 12:04:22 -2.5058 1.02591 12:04:22 TRUE 12:04:22 -2.5058 1.02591 12:04:22 TRUE 12:04:22 -2.5058 1.02591 12:04:22 FALSE 12:04:22 -2.5058 1.02591 12:04:22 TRUE 12:04:22 -2.5058 1.02591 12:04:22 FALSE 12:04:23 -2.5058 1.02591 12:04:23 TRUE 12:04:23 -2.5058 1.02591 12:04:23 TRUE 12:04:23 -2.5058 1.02591 12:04:23 TRUE 12:04:23 -2.5058 1.02591 12:04:23 TRUE 12:04:23 -2.5058 1.02591 12:04:23 TRUE 12:04:24 -2.5058 1.02591 12:04:24 TRUE 12:04:24 -2.5058 1.02591 12:04:24 TRUE 12:04:24 -2.5058 1.02591 12:04:24 TRUE 12:04:24 -2.5058 1.02591 12:04:24 TRUE 12:04:24 -2.5058 1.02591 12:04:24 TRUE 12:04:25 -2.5058 1.02591 12:04:25 TRUE 12:04:25 -2.5058 1.02591 12:04:25 TRUE 12:04:25 -2.5058 1.02591 12:04:25 TRUE 12:04:25 -2.5058 1.02591 12:04:25 TRUE 12:04:25 -2.5058 1.02591 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.

# Re: Deleting multiple values from the same second

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")``
Related Conversations
Pivot table
gabriellerocha in Excel on
5 Replies
How do I group rows in an excel table?
Marisa Medrano in Excel on
1 Replies