Transform hourly data in minute timestep data

I have an Excel file with hourly data, that is 8760 values on one year. I need to have data at the minute-step. I need to take the hourly value, and copy it to the 60 cells over and over again, until I have 525 600 values, being my minute timestep.

I've tried a couple things already, but haven't been able to do anything else than copy/paste it by hand, which is going to take long...

Update/Solution :

Sub test()

Worksheets("MySheet").Activate

Dim i As Double 'minutes increment
Dim j As Integer 'hourly increment
Dim k As Double

k = 0 'initialization of k

Dim Var1 As Single
Dim Var2 As Single

    For j = 1 To 8760

        Var1 = Cells(j, 8).Value 'Row "j"/from 1 to 8760, column "8"/H --> hourly values
        Var2 = Cells(j, 7).Value 'Row "j"/from 1 to 60, column "7"/G --> minutes values

        For i = 1 To 60
    
            k = k   1 'incrementation of k to be able to insert the values in the 60 cells (corresponding to 60 minutes)
            Cells(k   3, 10) = Var1 'insert those values in the 10th column (J) 
            Cells(k   3, 9) = Var2 'insert those values in the 10th column (I)
        
        Next i
    Next j 
End Sub

This can also be done without VBA, as given in answer, with the function =INDEX.

Thanks for your help !

CodePudding user response：

If the data are stored in a column (let's assume in column A) and you want a result also stored in a colum (let's assume in column B), a formula similar to this one might work:

=INDEX(A:A,QUOTIENT(ROW(B1)-1,60) 1)

Place it in cell B1 and drag it down.

If the data are stored in a row (let's assume in row 1) and you want a result also stored in a row (let's assume in row 2), a formula similar to this one might work:

=INDEX(1:1,QUOTIENT(COLUMN(A2)-1,60) 1)

Place it in cell A2 and drag it right.

As you can see the formula are basically the same. They are different just because one will work by being dragged down, the other by beign dragged right. If your list do not start from A1, the formulas can be easily adapted.

You could also write down a group of 60 cells the first one being 1, the following 59 being equal to the first one (by formula); then the 61st being would have been equal to the first plus 1 (always by formula), the following 59 equat to the 61st (again by formula). You can easily copy the second group as far as necessary. You then use a simple index formula linked to your new sequence of numbers to target the desired cell in the desired list.

Then again, you can also use VBA. It might be an overkill, though.

A data sample and/or more details might be needed if this answer isn't enouth.