Transpose Time Series Data

[cshenoy]

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.

[HansV]

I'd use a pivot table:

x211.png

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:

Example.xlsx

[cshenoy]

Of course! Thanks so much.