The way I would do it is:

1-) Instead of months, use the month numbers, like 1 for January and so on.

Then, use this formula

=CONCATENATE($A5,"-",RIGHT("0" & B$4,2))

Where A5 is "1948" cell and B4 is "1" cell (for January). Then, just apply the formula to the other cells and this should work fine. This will also take care of the leading zeros, so it will show: "1948-01" as you wanted.

Here is the link of the file that I did as an example:
http://www.yousendit.com/download/WnBTZEV5OC83bUJMWEE9PQ
Source(s):
I just know. :)

Permalink | Report

You might have a problem right out of the gate, Excel 2003 only supports 256 columns per worksheet, you're going to have 600. More importantly, why are you trying to do this - for a graph? This makes the data more difficult to manage.
Permalink | Report

Yeah it sounds like what you want is a Pivot Table. which you can get MS Excel to do pretty easily.
http://en.wikipedia.org/wiki/Pivot_table
Source(s):
http://en.wikipedia.org/wiki/Pivot_table

Permalink | Report

As themshow said, if you have enough cols, this is what I would use.

=INDIRECT(ADDRESS(P$5+P$7,P4+P$6))

picture here: http://twitpic.com/117rz

green areas I assume you can easily populate out.
Source(s):
just me.

Permalink | Report

If you are going to run out of columns like he says then you are just going to run out of columns and there isn't anything we can do about that. You can convert some of the data into your format until you run out of columns. Maybe analyzing the most recent ten years of data or something will be useful.

Anyway, you can look at this as two problems:

1) Get all of the data arranged on one row instead of 60.
-- one way to do this is 60 cut/copy and paste operations. Boring, but definitely will solve the problem. I think a key combination like End-right arrow or control-right arrow may help you position the cursor at the "edge" of the data (at least for Excel).

Another way to do this would be to use CELL("value", range) to lookup the value where you would basically divide the column number (e.g. column("A5")) by 12 to get the row number. The problem with this, besides being a difficult formula is that it does require programming because amazingly there isn't any way to specify a cell based on row and column number (in a regular formula) and in order to convert column numbers into column letters you need a VBA function http://www.freevbcode.com/ShowCode.asp?ID=4303 .

2) Properly label each column according to YYYY-MM.
-- The simplest thing to do would be to copy these values from the worksheet generated by lfnovo.


Down the line if you want to keep analyzing the data, Pivot Tables or OLAP might be helpful. If you can get the data into MS Access then I think you can use Excel's OLAP features with Access. Certainly you can do that with SQL Server.
Permalink | Report