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

Year	Month	Day	Rainfall amount (millimetres)
1893	12	1	0
1893	12	2	0
1893	12	3	0
1893	12	4	0
1893	12	5	0
1893	12	6	0
1893	12	7	0
1893	12	8	0
1893	12	9	0
1893	12	10	0
1893	12	11	0
1893	12	12	0
1893	12	13	0
1893	12	14	0
1893	12	15	0
1893	12	16	0
1893	12	17	0
1893	12	18	2
1893	12	19	0
1893	12	20	0
1893	12	21	0
1893	12	22	0
1893	12	23	0
1893	12	24	0
1893	12	25	0
1893	12	26	0
1893	12	27	0
1893	12	28	0
1893	12	29	0
1893	12	30	0
1893	12	31	0
1894	1	1	7.9
1894	1	2	0
1894	1	3	0
1894	1	4	0
1894	1	5	0
1894	1	6	2.3
1894	1	7	0
1894	1	8	0
1894	1	9	64.5
1894	1	10	12.7
1894	1	11	0
1894	1	12	0
1894	1	13	31.8
1894	1	14	29.7
1894	1	15	38.4
1894	1	16	8.1
1894	1	17	0
1894	1	18	0
1894	1	19	9.7
1894	1	20	0
1894	1	21	0
1894	1	22	99.1
1894	1	23	0
1894	1	24	16.5
1894	1	25	14.7
1894	1	26	0.3
1894	1	27	4.8
1894	1	28	4.8
1894	1	29	56.4

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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.

Year	Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Nov	Oct	Dec
1983	0	0	0	0	0	0	4	6	2	0	120	80
1984	230	43	668	26	1300	34	2	3	4	6	7	9

<tbody>
</tbody>

 

[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

Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Annual
1992	0	817.9	533.6	268.5	121.5	22.1	22.1	20.4	71	13.6	172.2	130.6	2193.5
1993	143.9	228.6	268	50	76	17.9	105.5	103	78.7	103.6	178.6	170.9	1524.7

<colgroup><col span="8"><col><col><col span="3"><col></colgroup><tbody>
</tbody>

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