How to convert a spreadsheet with years as rows and months (Jan-Dec) as columns into YYYY-MM columns only
What I'd like to know is a clever way to convert this data, without programming, into a YYYY-MM columns from Jan 1948 to Dec 2008, with the data values in one row under each appropriate month.
What's the best way to do this?
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$5 Answers
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
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.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$http://en.wikipedia.org/wiki/Pivot_table
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$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
I just know. :)
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
Your approach arrives at the wrong answer faster than mine, which deserves some honorable mention, but unfortunately it doesn't arrange them in single series columnar format which is my objective. It arranges the dates row-by-row, in dozens, instead of as one continuous set of adjacent columns (proceeding from January 1948 at A1 until December 2008 at An). Hope this makes sense.
=INDIRECT(ADDRESS(P$5+P$7,P4+P$6))
picture here: http://twitpic.com/117rz
green areas I assume you can easily populate out.
just me.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
See my comments to Ifnovo; your answer might have something to it but frankly I don't quite understand how to apply it.
I will post a video when I get home (faster connection),and you can use the formula [=IF(AVERAGE(O2:Z2)=Z2,Z2+1,Z2) to get your row of years populated across all the columns. For example, if you at least make 12 columns of value "1948" starting at cell"O2" than the 13th column put that formula and copying all they way out.
I guess I could have just put the example xlsx file somewhere for you too.
http://cid-8a0f84dc4bf39da9.skydrive.live.com/self.aspx/Public/Book2.xlsx
Your approach arrives at the wrong answer faster than mine, which deserves some honorable mention, but unfortunately it doesn't arrange them in single series columnar format which is my objective. It arranges the dates row-by-row, in dozens, instead of as one continuous set of adjacent columns (proceeding from January 1948 at A1 until December 2008 at An). Hope this makes sense.
So where are you mixing the data? Still in a spreadsheet? It would be easier to write a formula that can pull from an x/y chart rather than stringing the chart out.
Sorry--my first reply was meant for Ifnovo.
I'm using OpenOffice actually, but I can live with an Excel solution if that's provided. They are pretty comparable in most cases.
I want to serialize data that I currently have in an X-Y format. Specifically, it is the Bureau of Labor Statistics unemployment figures, which are delivered in an X-Y chart from the BLS Web site (with years as the row labels and months as the column identifiers). I would like to translate this into serialized unemployment rate per month so that I can mix the data with other month-pegged data to do some comparisons and contrasts.