Converting 5 minute data into 1 minute Data

[Bastiaan]

I have 2 lists of data 1 with 1 minute data and with 5 minute data. ordered like this : |29-12-2017 18:00:00|

I would like to turn my 5 minute data into 1 minute data.

Basicly inserting the same row 4 extra times beneath the first adding for each row 1 minute |29-12-2017 18:01:00|

Or inserting the important colums into the 1 minute data By autofiltering the 1 minute data by 5 minutes. Turning Autofilter off and duplicating the value in the added collums down 4x


Hope some1 can send me in a general direction

 

[Gerald Higgins]

If you only need to do this once, this should be do-able with a little copy/pasting.

It will help if your times are stored as Excel times (i.e. a number formatted into a date/time format) and may be more difficult if they are really text strings.
If your data is really numbers, then simple formulas can do the 1 minute advances.

 

[Bastiaan]

I have to do this quite alot. I was thinking of adding an extra collumn and copy Cell A2 to (B2 - B6) then Copy Cell A3 to (B7 - B11). Anyone got tips to make a nice Loop out of this?

 

[offthelip]

You haven't said where the one minute data is or where the five minute data is or how many column to copy, I made the assumption that the 5 minute data was in columns J to P and the one minute data was in column A
This will do it very rapidly using variant arrays.

Sub timedata()
lastrow5 = Cells(Rows.Count, "J").End(xlUp).Row
fivemindata = Range(Cells(1, 10), Cells(lastrow5, 16))
lastrow1 = Cells(Rows.Count, "A").End(xlUp).Row
onemindata = Range(Cells(1, 1), Cells(lastrow1, 1))
Range(Cells(1, 2), Cells(lastrow1, 8)) = ""
outarr = Range(Cells(1, 2), Cells(lastrow1, 8))
For i = 1 To lastrow5 - 1
  For j = 1 To lastrow1
   If fivemindata(i + 1, 1) > onemindata(j, 1) And fivemindata(i, 1) <= onemindata(j, 1) Then
     For k = 1 To 6
      outarr(j, k) = fivemindata(i, k + 1)
     Next k
    End If
  Next j
Next i


Range(Cells(1, 2), Cells(lastrow1, 8)) = outarr




End Sub