Hi all,
I've got a large amount of time series data in columns. The columns are: an identifier, a year, and Var1. I'd like to transpose the data so each identifier has a row and the columns would be Var1_year1, Var1_year2, etc.
The problem is I don't have the same number of years for each identifier. I do know the earliest year is 1999 and the latest is 2008. I could use a lot of lookups, but I'm afraid it would be really slow. If someone has a better idea, I'd love to hear it.
I've attached an example spreadsheet. On the 1st tab is how the data is lined up now. On the 2nd tab, I've got an example of what I'd like.
Transpose Time Series Data
-
- StarLounger
- Posts: 59
- Joined: 08 Feb 2010, 14:26
- Location: Lawrence, KS
Transpose Time Series Data
You do not have the required permissions to view the files attached to this post.
Cathy
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transpose Time Series Data
I'd use a pivot table:
I formatted the value field as 0.000 like the original data and turned off the row and column totals; the rest was done by Excel.
See workbook:
I formatted the value field as 0.000 like the original data and turned off the row and column totals; the rest was done by Excel.
See workbook:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 59
- Joined: 08 Feb 2010, 14:26
- Location: Lawrence, KS