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
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