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 tried a couple things, but haven't been able to do anything.
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.
-
So you will have 60 cells of the same data? Record a macro.findwindow– findwindow2022年06月14日 15:31:18 +00:00Commented Jun 14, 2022 at 15:31
-
I've tried that, but how do I transpose it to the 8759 next iterations ?Clément– Clément2022年06月14日 15:51:40 +00:00Commented Jun 14, 2022 at 15:51
-
Move to the next row and repeat? Modify the macro?findwindow– findwindow2022年06月14日 15:56:36 +00:00Commented Jun 14, 2022 at 15:56
-
I have answered a similar question where the OP asked how to copy every row 4 times. Have a look if you can adapt it to your needs, just replace the 4 with 60: stackoverflow.com/a/53317056/7599798FunThomas– FunThomas2022年06月14日 15:57:28 +00:00Commented Jun 14, 2022 at 15:57
-
Or even enter something in column 61 on down to set a "wall" then copy your first column, ctr shift right then paste?findwindow– findwindow2022年06月14日 16:01:12 +00:00Commented Jun 14, 2022 at 16:01
1 Answer 1
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.