0

I have about 100 files of MS Excel which consist of weather data.

I need the data on yearly basis but the file has a single column with data of about 140 years and manually copy-paste is very time consuming. So, is there any way to split the data by using simple commands which can be dragged so that data of 1 years(365 cells) is copied in continuous rows.

Also, there is the leap year problem that data after every 3 years should be 366 cells instead of the rest 365 cells.

8
  • Welcome to Super User. We need a little more information from you. Please edit your question to include how the data is laid out and what you've tried so far. Please keep in mind this site isn't a service, rather a place to ask questions when you have attempted something and get stuck.
    – CharlieRB
    May 14, 2014 at 11:55
  • What is the actual date of the first data point? Are you looking to transpose the data? Or move it to another column? Or highlight different years different colors? Or are you looking to get an average? What is your goal? May 14, 2014 at 12:49
  • The data in the excel file of about 140 years has been put into the column A with no break between months or years Now i want to extract data of each year separately from column A into multiple columns such that column B has data of ist year, column C has data of secont year and so o. The probllem now arises for the fourth year as the fourt year happens to be a leap year and data has to be of 366 cells and the fifth year should begin after that. Please check the file also herelink
    – user322740
    May 14, 2014 at 13:51
  • Basically i have an excel file The data is all kept in column A with No break in between1 I want to split the data in multiple columns such that Column B=1st 365 enteries of column A Column C=2nd 365 enteries of column A Column D=3rd 365 enteries of colum A But, Column D=4th 366 enteries of column A Column E=5th365 enteries of column A Column F=6th 365 enteries of column A Column G=7th 365 entries of colum A But, Column H=8th 366 enteries of column A and so on.
    – user322740
    May 14, 2014 at 14:36
  • What year does it start with? Is the value in A1 the 1st of January Year 20XX?
    – Dave
    May 14, 2014 at 14:47

2 Answers 2

2

As you can't seem to get the other macro working per this question, I figured I'd work with the hard facts you've presented here and make it easy for you.

For your specific date range, this macro will work -

Sub test()
'fill dates
Application.ScreenUpdating = False
Dim NumCells As Integer
NumCells = [counta(A:A)]
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Cells(1, 1) = "1/1/61"
Range(Cells(1, 1), Cells(NumCells, 1)).DataSeries

'move data
Dim c As Range
Dim cCol As Integer
cCol = 4
label:
For Each c In Range("A:A")
If c.Value = "" Then Exit Sub

    For i = 1 To 366
        If Not Right(c, 4) = Right(c.Offset(i), 4) Then
         GoTo label2
        End If
    Next
label2:
        Range(c, c.Offset(i - 1, 1)).Copy
        Cells(1, cCol).PasteSpecial Paste:=xlPasteValues
        Range(c, c.Offset(i - 1, 1)).Delete xlShiftUp
        Range(Cells(1, cCol), Cells(366, cCol)).NumberFormat = "mm/dd/yyyy"
        cCol = cCol + 3
        GoTo label

Next
Application.ScreenUpdating = True
End Sub
1
  • I think this is a much better answer than mine... Less code too...
    – Dave
    May 15, 2014 at 7:18
0

Try this macro

Sub Button1_Click()

Dim row As Integer
row = 1

Dim otherRow As Integer
otherRow = 1

Dim year As Integer
year = 1

Dim column As Integer
column = 66 ' start on B

Dim preColumn As Integer
preColumn = 64

Do While (True)

If Range("A" & row).Value = "" Then
    Exit Do
End If

If (column > 90) Then
    preColumn = preColumn + 1
    column = 65
End If


If (preColumn = 64) Then

Range(Chr(column) & otherRow).Value = Range("A" & row).Value

Else

Range(Chr(preColumn) & Chr(column) & otherRow).Value = Range("A" & row).Value

End If


Dim addition As Integer
addition = 0

If (year = 4) Then
    addition = 1
End If

If (otherRow = 365 + addition) Then
column = column + 1
otherRow = 0

year = year + 1

End If


If (year > 4) Then
 year = 1
End If

row = row + 1
otherRow = otherRow + 1

Loop

End Sub

Fairly useless screen shot of it working:

enter image description here

enter image description here

Please try it on a copy of your data... I'm not responsible for breaking everything. You will also need to test it properly and ensure all data is present, but I think it's fine.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .