none
How to get value of column Measure for each 30s time interval of column A RRS feed

  • Question

  • Dear all,

    I have a first column named Date Time and countains a date time value which has over 2000 entries related to an aquisition system records

    In second column Mesaure I have the return measure of the device at a given data time

    So sample snipet data will be as below :

    Using a VBA macro I need to extract from the first column records which are mesaure every 10s in the exemple above and then colect the date time and measure for that each 10s record and copy them in a second sheet.

    The 10s intervale count be change to 30s if needed

    Any idea how can I write this simple VBA code, I am new to this

    Thanks for that

    regards

    Tuesday, February 26, 2019 4:48 PM

All replies

  • Do you want to take average of the 3 reading that make up 30s?
    Tuesday, February 26, 2019 10:18 PM
  • No no average, I need to simply extract the record to a second sheet within the time interval.

    Then next time I have a new set of aquisition, I will simply click a button "Extract data" and then it will do it automatically

    Any help will be great not used to this

    regards

    Wednesday, February 27, 2019 8:03 AM
  • This code assumes the input worksheet is "Data1" and output worksheet is called "Data2".  I recreate "Data2" each time.  There is a header row.  

    Option Explicit
    Option Base 0
    
    Sub ExtractData()
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim i As Integer, j As Integer
      
      Set ws1 = Worksheets("Data1")
      Call DelAddSheet("Data2", ws2)
      
      i = 2
      j = 2
      ws2.Cells(1, 1) = ws1.Cells(1, 1)
      ws2.Cells(1, 2) = ws1.Cells(1, 2)
     
      While ws1.Cells(i, 1) <> ""
        ws2.Cells(j, 1) = ws1.Cells(i, 1)
        ws2.Cells(j, 1).NumberFormat = "m/d/yy h:mm:ss"
        ws2.Cells(j, 2) = ws1.Cells(i, 2)
        i = i + 3
        j = j + 1
      Wend
    End Sub
    
    Sub DelAddSheet(s As String, ws1 As Worksheet)
      s = Left(s, 31)
      Application.DisplayAlerts = False     ' Delete Sheet if it exists
      On Error Resume Next
      Sheets(s).Delete
      Application.DisplayAlerts = True
    
      Set ws1 = Sheets.Add(Type:=xlWorksheet)     'Create new sheet
      ws1.Name = s                                'Rename it
    End Sub
    

    Wednesday, February 27, 2019 4:44 PM
  • Thanks for your sample, but from code you provide, where are you telling to extract data for every 30s sample ? could not see.

    I do not know in advance how time records will be fetch, so my goal is that I need to loop through all rows and determine if the current row fetch time is 30s  compare to row before

    The sample row I highlight are the records that I need to get , but I need to fetch them dynamically by identifying if time interval is 30s for each loop, not by hardcode the record as it can be also :

    28.02.2019 8:00:00      12
    28.02.2019 8:00:01      13
    28.02.2019 8:00:02      14
    28.02.2019 8:00:03      15
    28.02.2019 8:00:10      16
    28.02.2019 8:00:17      17
    28.02.2019 8:00:28      18
    28.02.2019 8:00:30      19
    28.02.2019 8:00:32      20
    28.02.2019 8:00:35      21
    28.02.2019 8:01:00      22
    28.02.2019 8:01:12      23

    In above I should retunr data value 19 and 22

    how to do that extraction ?

    Thanks for help

    regards


    • Edited by wakefun Thursday, February 28, 2019 8:04 AM
    Thursday, February 28, 2019 7:57 AM
  • Does it need to be >= to every 30 seconds or exactly at 30 seconds? So if measurement 19 was at 8:00:31 seconds would it get extracted?  If it did get extracted then would it add 30 seconds to 8:00:31 and extract something >= 8:01:01.

    • Edited by mogulman52 Thursday, February 28, 2019 2:47 PM
    Thursday, February 28, 2019 2:38 PM
  • Hello,

    The time interval for which I need exctracted data can be a parameter that I can change but if I define a 30 s time interval, it should be exactly 30s from the first record time.

    Let's make it simple first to have an idea, we suppose the aquisition system is constant and record every 1s, we suppose there will be no gap for this sample approach

    Ex :

    if first record time is 8:00:02, then the extracted record will 8:00:02 +30 s  = 8:00:32, etc

    regards


    • Edited by wakefun Thursday, February 28, 2019 3:37 PM
    Thursday, February 28, 2019 2:52 PM
  • This should work.

    Option Explicit
    Option Base 0
    
    Sub ExtractData()
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      Dim i As Integer, j As Integer
      Dim refDate As Date
      Const refSecs As Long = 30
      
      Set ws1 = Worksheets("Data1")
      Call DelAddSheet("Data2", ws2)
      
      i = 3
      j = 3
      ws2.Cells(1, 1) = ws1.Cells(1, 1)
      ws2.Cells(1, 2) = ws1.Cells(1, 2)
      ws2.Cells(2, 1) = ws1.Cells(2, 1)
      ws2.Cells(2, 1).NumberFormat = "m/d/yy h:mm:ss"
      ws2.Cells(2, 2) = ws1.Cells(2, 2)
      
      refDate = ws1.Cells(2, 1)
      
      While ws1.Cells(i, 1) <> ""
        If DateDiff("s", refDate, ws1.Cells(i, 1)) Mod refSecs = 0 Then
          ws2.Cells(j, 1) = ws1.Cells(i, 1)
          ws2.Cells(j, 1).NumberFormat = "m/d/yy h:mm:ss"
          ws2.Cells(j, 2) = ws1.Cells(i, 2)
          j = j + 1
        End If
        i = i + 1
      Wend
    End Sub
    
    Sub DelAddSheet(s As String, ws1 As Worksheet)
      s = Left(s, 31)
      Application.DisplayAlerts = False     ' Delete Sheet if it exists
      On Error Resume Next
      Sheets(s).Delete
      Application.DisplayAlerts = True
    
      Set ws1 = Sheets.Add(Type:=xlWorksheet)     'Create new sheet
      ws1.Name = s                                'Rename it
    End Sub
    

    Thursday, February 28, 2019 8:25 PM
  • Thnaks this is a good start.

    In case my time series does not have a regular time span of seconds, how can I decide how to extract ?

    ex :

    28.02.2019 8:00:00      12
    28.02.2019 8:00:17      13
    28.02.2019 8:00:32      14
    28.02.2019 8:00:43      15
    28.02.2019 8:00:55     16
    28.02.2019 8:00:56      17
    28.02.2019 8:00:01      18
    28.02.2019 8:00:05      19

    In sample above as you can see the time span is not equal, so in that case If my time sample is 30s still I should be able to extract value 13,14,15,16,17,18

    advise, as here MOD will not work

    regards

    Friday, March 1, 2019 3:24 PM
  • I'm confused what you are saying.  Can you explicitly state the algorithm you need?  I consult for an environmental testing company and have done a lot of IT work on analyzing data from systems such as Agilent and Instrunet.  It seems like you are getting data from an IOT device. 
    Friday, March 1, 2019 5:32 PM