0

I have a large data file from a test where I send a voltage that is increment by 1mv every 30s from 0-5V) to test the accuracy of my system. The computer outputs a file that has over 70000 rows of data but all I am really concerned with is data that occurs every 30s. Is there a way to filter for only the data that aligns with the 30s interval and ideally having around 5000 rows of data?

I am stuck and I really don't want to manually sort through 70000 lines of data, any help is greatly appreciated..

Quade Howald
  • 25
  • 3

1 Answers1

1

So you want to filter and only see the rows that occur every 30 seconds? You can add a calculated column in Excel to extract the seconds and filter by that column:

=RIGHT(TEXT(A1, "hh:mm:ss"),2)

This will extract the seconds from a time, and then you can filter where the seconds are 30. Replace A1 with your correct column.

Ryan
  • 7,835
  • 2
  • 29
  • 36
  • 1
    Then a vba code that deletes all that are not needed. ie keep 00 and 30... – Solar Mike Mar 08 '21 at 21:17
  • Yes, you can either delete the non-matching rows or export to a new file. – Ryan Mar 08 '21 at 21:28
  • This is great, thanks guys! My new problem is that my data doesn't always have data every 30 seconds, a lot of them skip 30. How would I create a VBA script that observes each row looks for the 30 second value, but if it is not there chooses the next closest value such as 29? – Quade Howald Mar 09 '21 at 16:04
  • @QuadeHowald that gets a lot more complex. You may have to use VBA to get there. I would add another column, iterate through the rows and then "rank" the row on 30 seconds or the closest match. An alternative might be possible using the "Data Analysis" tools in Excel and treating the "30 second windows" as "bins" in a histogram and find the median for each bin. However, I think it is a new and different question. – Ryan Mar 09 '21 at 20:24