Not sure what code to use.

[Jacko1996]

Hey so I have a whole bunch of daily data for a weather station. What I want it to do is create a table made of monthly data for all the years using the daily data. I'm not sure what formula to use.

The table below shows kind what it looks like, however on a much larger scale

[TABLE="width: 297"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Day[/TD]
[TD]Rainfall amount (millimetres)[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1893[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7.9[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2.3[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]64.5[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12.7[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]31.8[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]29.7[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]38.4[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8.1[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]9.7[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]99.1[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]16.5[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]14.7[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4.8[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4.8[/TD]
[/TR]
[TR]
[TD="align: right"]1894[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]56.4[/TD]
[/TR]
</tbody>[/TABLE]

Below is what i want it to look like the values are made up but those would be the values of rainfall for the months.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Nov[/TD]
[TD]Oct[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]1983[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]120[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]1984[/TD]
[TD]230[/TD]
[TD]43[/TD]
[TD]668[/TD]
[TD]26[/TD]
[TD]1300[/TD]
[TD]34[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jacko1996]

Sorry I used Rick Rothestein code but perhaps you hiker95 could tell me what these values mean?

Thanks

 

[hiker95]

Sorry I used Rick Rothestein code but perhaps you hiker95 could tell me what these values mean?

Jacko1996,

You will have to ask Rick Rothstein to explain his macro code to you.

 

[apo]

Not important.. but here's 2 alternate ways of doing this:

.Range("A1:M1") = Array("Year", "Jan", "Feb", "Mar", "Apr", "May", _
                      "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

[A1:M1] = Split("Year" & vbLf & Join(Application.GetCustomListContents(3), vbLf), vbLf)

or

[A1] = "Year": [B1:M1] = Application.GetCustomListContents(3)

 

[Jacko1996]

Hey Rick Rothstein

How am I able to add a annual column to this code to create a sum of that years data?

So It looks like This

[TABLE="width: 1000"]
<colgroup><col span="8"><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Annual[/TD]
[/TR]
[TR]
[TD="align: right"]1992[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]817.9[/TD]
[TD="align: right"]533.6[/TD]
[TD="align: right"]268.5[/TD]
[TD="align: right"]121.5[/TD]
[TD="align: right"]22.1[/TD]
[TD="align: right"]22.1[/TD]
[TD="align: right"]20.4[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]13.6[/TD]
[TD="align: right"]172.2[/TD]
[TD="align: right"]130.6[/TD]
[TD="align: right"]2193.5[/TD]
[/TR]
[TR]
[TD="align: right"]1993[/TD]
[TD="align: right"]143.9[/TD]
[TD="align: right"]228.6[/TD]
[TD="align: right"]268[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]17.9[/TD]
[TD="align: right"]105.5[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]78.7[/TD]
[TD="align: right"]103.6[/TD]
[TD="align: right"]178.6[/TD]
[TD="align: right"]170.9[/TD]
[TD="align: right"]1524.7[/TD]
[/TR]
</tbody>[/TABLE]

The Code I have used is
Sub YearMonthRainfallToo()
Dim r As Long, LastRow As Long, Data As Variant, Result As Variant
LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Data = Sheets("Sheet1").Range("A2:D" & LastRow)
ReDim Result(Data(1, 1) To Data(UBound(Data), 1), 0 To 12)
For r = 1 To UBound(Data) - LBound(Data) + 1
If Result(Data(r, 1), 0) = "" Then Result(Data(r, 1), 0) = Data(r, 1)
Result(Data(r, 1), Data(r, 2)) = Result(Data(r, 1), Data(r, 2)) + Data(r, 4)
Next
With Sheets("Rainfall")
.Cells.Clear
.Range("A1:M1") = Array("Year", "Jan", "Feb", "Mar", "Apr", "May", _
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
.Range("A2").Resize(UBound(Result) - LBound(Result) + 1, 13) = Result
On Error Resume Next
.Range("A2").Resize(UBound(Result) - LBound(Result) + 1, 13).SpecialCells(xlBlanks) = 0
On Error GoTo 0
End With
End Sub

 

[Jacko1996]

Hey anyone, whos knows about this code, I need it bad.

Thanks