PDA

View Full Version : Solved: Re-arranging Data



Hamond
04-09-2012, 03:47 PM
Hi,

I have the attached data file with months at the top accross columns and codes and year to which the data/month relates going down rows. (see sheet "raw")

i.e:

CodeYearJanFebMarAprMayJuneJulyAug Sept OctNovDecAMTMVS199222788622896023857523988824362724570824576324283624460324 3490247071248163AMTMVS19932477982496672502092488052519412529362490942493982 52572256506255374255982AMTMVS1994260272261247261706263348266838267949271236 273787272880276547279426286836AMTMVS199528710528854328455828966328558628870 1287937289702295112292999291615300577


I need to re-arrange the data into logical order so that it alines up in three columns only with the code field in the first, the period to which the code and data values relates to (month and year) in the second column and the value in the 3rd column so that the rearranged data looks like what's in the sheet "Ordered"

i.e CodePeriodValueAMTMVSJan-58AMTMVSFeb-58AMTMVSMar-58AMTMVSApr-58AMTMVSMay-58AMTMVSJun-58AMTMVSJul-58AMTMVSAug-58AMTMVSSep-58AMTMVSOct-58AMTMVSNov-58AMTMVSDec-58AMTMVSJan-59AMTMVSFeb-59AMTMVSMar-59AMTMVSApr-59AMTMVSMay-59AMTMVSJun-59AMTMVSJul-59AMTMVSAug-59AMTMVSSep-59AMTMVSOct-59AMTMVSNov-59AMTMVSDec-59AMTMVSJan-60


I've tried using a pivot table to get it into the right format but this does not seem to work.

Hoping someone can suggest a solution!

Note I am only showing an example of the data in the raw sheet, in reality I have lots of codes which takes the last value to row 43,571. Also I can work in both excel 2003 and 2007 so row limitations for a solution is at 1 million!

Thanks,

Hamond

Bob Phillips
04-09-2012, 04:22 PM
It's slow



Public Sub ProcessData()
Dim sh As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long, j As Long

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Ordered").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
sh.Name = "Ordered"
sh.Range("A1:C1").Value = Array("Code", "Period", "Value")
nextrow = 1

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Worksheets("Raw")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.UsedRange.Sort key1:=.Range("B1"), order1:=xlAscending, _
key2:=.Range("A1"), order2:=xlAscending, _
Header:=xlYes

For i = 2 To lastrow

For j = 3 To 14

If .Cells(1, j).Value <> "" Then

nextrow = nextrow + 1
sh.Cells(nextrow, "A").Value = .Cells(i, "A").Value
sh.Cells(nextrow, "B").Value = DateValue("01-" & .Cells(1, j).Value & "-" & .Cells(i, "B").Value)
sh.Cells(nextrow, "C").Value = .Cells(i, j).Value
End If
Next j
Next i

sh.Columns("B").NumberFormat = "mmm-yyyy"
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Hamond
04-14-2012, 12:11 PM
Hi Xld.

Thanks for your help.

I tested this code on the full data and did it in under two minutes which I can live with!

I than ran a pivot table off teh results it to get each code along the top and dates/data in the rows (so the dates don't repeat themselves!

Hamond