Expert: Miguel Zapico - 7/23/2008

Question
Hi,
I have a large amount of data in 2 columns, one being the yearly population and the other being the country. There are eighteen years of population data, and a total of 216 countries, making each column have 2160 rows.
So this is what it looks like
CountryA   pop90
CountryA   pop91
CountryA   .
CountryA   .
CountryA   .
CountryA   .
CountryA   .
CountryA   .
CountryA   pop2007
CountryA   pop 2008
CountryB   pop90
CountryB   pop91
CountryB   .
CountryB   pop2008
.          .
.          .
.          .


Now, I have another sheet with the years on one row.

Country 90 91 92 93 94 95 96 97 98 99 00 01 02 03..08

So, how do i get the second sheet to have the values of each country in years?
In other words, I would like my end result to be:

Country  90 91 92 93 94 95 96 97 98 99 00 01 02 03..08
CountryA  pop90 pop91 pop92.........................pop2008
CountryB  pop90 pop91 pop92.........................pop2008

I tried using transpose, and it didnt work for some reason. Please could you help me by writing a macro or telling me how to do this.
Thank you
K.G.

Answer
I am assuming that the original data is on sheet1, it is ordered by country and year, it has just two columns, and it has headers, so the values starts at A2.  With that, on the second sheet, once you have the names of the countries on column A, you can use the following formula on B2:
=OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)+COLUMN(A1)-2,1)
And then copy it to all the cells on the table.  This should make the transpose that you are looking for.

Hope this helps,
Miguel