Transposing Data using Paste Special and Formula?

[Barghouti]

Hello,

I'm new to Excel. What I'm trying to do is take a set of data that looks just like this, but keeps going with every country...

[TABLE="width: 150"]
<tbody>[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="width: 75, align: right"]1980[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]1985[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]1990[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Afghanistan[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]1980[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]1985[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]1990[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Albania[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]1980[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]1985[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]1990[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Algeria[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]1980[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]1985[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]1990[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Andorra[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]1980[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]1985[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]1990[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Angola[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]1980[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]1985[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]1990[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Antigua and Barbuda[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]1980[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]1985[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]1990[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Argentina[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]1980[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]1985[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]1990[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 75"]Armenia[/TD]
[TD="align: right"]2013[/TD]
[/TR]
</tbody>[/TABLE]

...and to take the following data and add it as a third column above:

[TABLE="width: 1050"]
<tbody>[TR]
[TD="class: xl25, width: 75"]Afghanistan[/TD]
[TD="class: xl24, width: 75"]0.23[/TD]
[TD="class: xl24, width: 75"]0.23[/TD]
[TD="class: xl24, width: 75"]0.296[/TD]
[TD="class: xl24, width: 75"]0.341[/TD]
[TD="class: xl24, width: 75"]0.396[/TD]
[TD="class: xl24, width: 75"]0.406[/TD]
[TD="class: xl24, width: 75"]0.416[/TD]
[TD="class: xl24, width: 75"]0.43[/TD]
[TD="class: xl24, width: 75"]0.437[/TD]
[TD="class: xl24, width: 75"]0.453[/TD]
[TD="class: xl24, width: 75"]0.458[/TD]
[TD="class: xl24, width: 75"]0.466[/TD]
[TD="class: xl24, width: 75"]0.468[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Albania[/TD]
[TD="class: xl24, width: 75"]0.603[/TD]
[TD="class: xl24, width: 75"]0.602[/TD]
[TD="class: xl24, width: 75"]0.609[/TD]
[TD="class: xl24, width: 75"]0.655[/TD]
[TD="class: xl24, width: 75"]0.689[/TD]
[TD="class: xl24, width: 75"]0.694[/TD]
[TD="class: xl24, width: 75"]0.699[/TD]
[TD="class: xl24, width: 75"]0.703[/TD]
[TD="class: xl24, width: 75"]0.705[/TD]
[TD="class: xl24, width: 75"]0.708[/TD]
[TD="class: xl24, width: 75"]0.714[/TD]
[TD="class: xl24, width: 75"]0.714[/TD]
[TD="class: xl24, width: 75"]0.716[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Algeria[/TD]
[TD="class: xl24, width: 75"]0.509[/TD]
[TD="class: xl24, width: 75"]0.551[/TD]
[TD="class: xl24, width: 75"]0.576[/TD]
[TD="class: xl24, width: 75"]0.634[/TD]
[TD="class: xl24, width: 75"]0.675[/TD]
[TD="class: xl24, width: 75"]0.68[/TD]
[TD="class: xl24, width: 75"]0.686[/TD]
[TD="class: xl24, width: 75"]0.695[/TD]
[TD="class: xl24, width: 75"]0.703[/TD]
[TD="class: xl24, width: 75"]0.709[/TD]
[TD="class: xl24, width: 75"]0.715[/TD]
[TD="class: xl24, width: 75"]0.715[/TD]
[TD="class: xl24, width: 75"]0.717[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Andorra[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]0.832[/TD]
[TD="class: xl24, width: 75"]0.831[/TD]
[TD="class: xl24, width: 75"]0.83[/TD]
[TD="class: xl24, width: 75"]0.83[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Angola[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]0.377[/TD]
[TD="class: xl24, width: 75"]0.446[/TD]
[TD="class: xl24, width: 75"]0.465[/TD]
[TD="class: xl24, width: 75"]0.478[/TD]
[TD="class: xl24, width: 75"]0.49[/TD]
[TD="class: xl24, width: 75"]0.491[/TD]
[TD="class: xl24, width: 75"]0.504[/TD]
[TD="class: xl24, width: 75"]0.521[/TD]
[TD="class: xl24, width: 75"]0.524[/TD]
[TD="class: xl24, width: 75"]0.526[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Antigua and Barbuda[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]0.778[/TD]
[TD="class: xl24, width: 75"]0.772[/TD]
[TD="class: xl24, width: 75"]0.773[/TD]
[TD="class: xl24, width: 75"]0.774[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Argentina[/TD]
[TD="class: xl24, width: 75"]0.665[/TD]
[TD="class: xl24, width: 75"]0.688[/TD]
[TD="class: xl24, width: 75"]0.694[/TD]
[TD="class: xl24, width: 75"]0.753[/TD]
[TD="class: xl24, width: 75"]0.758[/TD]
[TD="class: xl24, width: 75"]0.766[/TD]
[TD="class: xl24, width: 75"]0.771[/TD]
[TD="class: xl24, width: 75"]0.777[/TD]
[TD="class: xl24, width: 75"]0.789[/TD]
[TD="class: xl24, width: 75"]0.799[/TD]
[TD="class: xl24, width: 75"]0.804[/TD]
[TD="class: xl24, width: 75"]0.806[/TD]
[TD="class: xl24, width: 75"]0.808[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Armenia[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]0.632[/TD]
[TD="class: xl24, width: 75"]0.648[/TD]
[TD="class: xl24, width: 75"]0.693[/TD]
[TD="class: xl24, width: 75"]0.707[/TD]
[TD="class: xl24, width: 75"]0.721[/TD]
[TD="class: xl24, width: 75"]0.722[/TD]
[TD="class: xl24, width: 75"]0.717[/TD]
[TD="class: xl24, width: 75"]0.72[/TD]
[TD="class: xl24, width: 75"]0.724[/TD]
[TD="class: xl24, width: 75"]0.728[/TD]
[TD="class: xl24, width: 75"]0.73[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Australia[/TD]
[TD="class: xl24, width: 75"]0.841[/TD]
[TD="class: xl24, width: 75"]0.853[/TD]
[TD="class: xl24, width: 75"]0.866[/TD]
[TD="class: xl24, width: 75"]0.898[/TD]
[TD="class: xl24, width: 75"]0.912[/TD]
[TD="class: xl24, width: 75"]0.915[/TD]
[TD="class: xl24, width: 75"]0.918[/TD]
[TD="class: xl24, width: 75"]0.922[/TD]
[TD="class: xl24, width: 75"]0.924[/TD]
[TD="class: xl24, width: 75"]0.926[/TD]
[TD="class: xl24, width: 75"]0.928[/TD]
[TD="class: xl24, width: 75"]0.931[/TD]
[TD="class: xl24, width: 75"]0.933[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Austria[/TD]
[TD="class: xl24, width: 75"]0.736[/TD]
[TD="class: xl24, width: 75"]0.754[/TD]
[TD="class: xl24, width: 75"]0.786[/TD]
[TD="class: xl24, width: 75"]0.835[/TD]
[TD="class: xl24, width: 75"]0.851[/TD]
[TD="class: xl24, width: 75"]0.857[/TD]
[TD="class: xl24, width: 75"]0.861[/TD]
[TD="class: xl24, width: 75"]0.868[/TD]
[TD="class: xl24, width: 75"]0.87[/TD]
[TD="class: xl24, width: 75"]0.877[/TD]
[TD="class: xl24, width: 75"]0.879[/TD]
[TD="class: xl24, width: 75"]0.88[/TD]
[TD="class: xl24, width: 75"]0.881[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Azerbaijan[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]0.639[/TD]
[TD="class: xl24, width: 75"]0.686[/TD]
[TD="class: xl24, width: 75"]0.703[/TD]
[TD="class: xl24, width: 75"]0.715[/TD]
[TD="class: xl24, width: 75"]0.724[/TD]
[TD="class: xl24, width: 75"]0.736[/TD]
[TD="class: xl24, width: 75"]0.743[/TD]
[TD="class: xl24, width: 75"]0.743[/TD]
[TD="class: xl24, width: 75"]0.745[/TD]
[TD="class: xl24, width: 75"]0.747[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Bahamas[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]0.766[/TD]
[TD="class: xl24, width: 75"]0.787[/TD]
[TD="class: xl24, width: 75"]0.789[/TD]
[TD="class: xl24, width: 75"]0.791[/TD]
[TD="class: xl24, width: 75"]0.791[/TD]
[TD="class: xl24, width: 75"]0.788[/TD]
[TD="class: xl24, width: 75"]0.788[/TD]
[TD="class: xl24, width: 75"]0.789[/TD]
[TD="class: xl24, width: 75"]0.788[/TD]
[TD="class: xl24, width: 75"]0.789[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Bahrain[/TD]
[TD="class: xl24, width: 75"]0.677[/TD]
[TD="class: xl24, width: 75"]0.718[/TD]
[TD="class: xl24, width: 75"]0.729[/TD]
[TD="class: xl24, width: 75"]0.784[/TD]
[TD="class: xl24, width: 75"]0.811[/TD]
[TD="class: xl24, width: 75"]0.808[/TD]
[TD="class: xl24, width: 75"]0.809[/TD]
[TD="class: xl24, width: 75"]0.81[/TD]
[TD="class: xl24, width: 75"]0.811[/TD]
[TD="class: xl24, width: 75"]0.812[/TD]
[TD="class: xl24, width: 75"]0.812[/TD]
[TD="class: xl24, width: 75"]0.813[/TD]
[TD="class: xl24, width: 75"]0.815[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Bangladesh[/TD]
[TD="class: xl24, width: 75"]0.336[/TD]
[TD="class: xl24, width: 75"]0.357[/TD]
[TD="class: xl24, width: 75"]0.382[/TD]
[TD="class: xl24, width: 75"]0.453[/TD]
[TD="class: xl24, width: 75"]0.494[/TD]
[TD="class: xl24, width: 75"]0.502[/TD]
[TD="class: xl24, width: 75"]0.51[/TD]
[TD="class: xl24, width: 75"]0.515[/TD]
[TD="class: xl24, width: 75"]0.527[/TD]
[TD="class: xl24, width: 75"]0.539[/TD]
[TD="class: xl24, width: 75"]0.549[/TD]
[TD="class: xl24, width: 75"]0.554[/TD]
[TD="class: xl24, width: 75"]0.558[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Barbados[/TD]
[TD="class: xl24, width: 75"]0.658[/TD]
[TD="class: xl24, width: 75"]0.688[/TD]
[TD="class: xl24, width: 75"]0.706[/TD]
[TD="class: xl24, width: 75"]0.745[/TD]
[TD="class: xl24, width: 75"]0.761[/TD]
[TD="class: xl24, width: 75"]0.764[/TD]
[TD="class: xl24, width: 75"]0.771[/TD]
[TD="class: xl24, width: 75"]0.776[/TD]
[TD="class: xl24, width: 75"]0.782[/TD]
[TD="class: xl24, width: 75"]0.779[/TD]
[TD="class: xl24, width: 75"]0.78[/TD]
[TD="class: xl24, width: 75"]0.776[/TD]
[TD="class: xl24, width: 75"]0.776[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Belarus[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]..[/TD]
[TD="class: xl24, width: 75"]0.725[/TD]
[TD="class: xl24, width: 75"]0.738[/TD]
[TD="class: xl24, width: 75"]0.751[/TD]
[TD="class: xl24, width: 75"]0.764[/TD]
[TD="class: xl24, width: 75"]0.775[/TD]
[TD="class: xl24, width: 75"]0.779[/TD]
[TD="class: xl24, width: 75"]0.784[/TD]
[TD="class: xl24, width: 75"]0.785[/TD]
[TD="class: xl24, width: 75"]0.786[/TD]
[/TR]
[TR]
[TD="class: xl25, width: 75"]Belgium[/TD]
[TD="class: xl24, width: 75"]0.753[/TD]
[TD="class: xl24, width: 75"]0.773[/TD]
[TD="class: xl24, width: 75"]0.805[/TD]
[TD="class: xl24, width: 75"]0.873[/TD]
[TD="class: xl24, width: 75"]0.865[/TD]
[TD="class: xl24, width: 75"]0.868[/TD]
[TD="class: xl24, width: 75"]0.871[/TD]
[TD="class: xl24, width: 75"]0.873[/TD]
[TD="class: xl24, width: 75"]0.873[/TD]
[TD="class: xl24, width: 75"]0.877[/TD]
[TD="class: xl24, width: 75"]0.88[/TD]
[TD="class: xl24, width: 75"]0.88[/TD]
[TD="class: xl24, width: 75"]0.881[/TD]
[/TR]
</tbody>[/TABLE]


I hope this makes sense, I know I'm very new to this so I'm not sure if I'm explaining correctly.

I know there's a way to do it but I think there's a formula, and you have to do a Paste Special and Transpose. Any help would be appreciated! Thank you very much!

 

[Barghouti]

Also, I know I can do it country by country, but that's going to take forever, and I saw someone do it using a formula so I'd much rather use that.

 

[Partho]

Barghouti

Country and Year data assumed to be in Sheet1 and Country and Decimal Values in Sheet2 (both starting from cell A1)

Copy this macro in macro Module:

Sub TransposeCopy()
Application.ScreenUpdating = False
Dim n As Long
n = 1
Do Until Sheets("Sheet2").Range("A" & n) = ""
Sheets("Sheet1").Select
Range("A:A").Select
Cells.Find(What:=Sheets("Sheet2").Range("A" & n), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Activate
ActiveCell.Offset(0, 2).Activate
Sheets("Sheet2").Select
Range(Range("A" & n).Offset(0, 1), Range("A" & n).Offset(0, 13)).Copy

Sheets("Sheet1").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
n = n + 1
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Then run the macro. Hope it helps!

 

[Barghouti]

Partho,

Thanks for being willing to help me. I'm using Excel 2008 for Mac, 12.0. On the Menu bar, I clicked on Tools, selected Macros, and then a box came up where I could input the macro. I tried but the Run button remains greyed out and it won't let me click it. I have 3 bottom tabs open: Sheet1, Sheet2, Data.

Barghouti

Country and Year data assumed to be in Sheet1 and Country and Decimal Values in Sheet2 (both starting from cell A1)

Copy this macro in macro Module:

Sub TransposeCopy()
Application.ScreenUpdating = False
Dim n As Long
n = 1
Do Until Sheets("Sheet2").Range("A" & n) = ""
Sheets("Sheet1").Select
Range("A:A").Select
Cells.Find(What:=Sheets("Sheet2").Range("A" & n), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Activate
ActiveCell.Offset(0, 2).Activate
Sheets("Sheet2").Select
Range(Range("A" & n).Offset(0, 1), Range("A" & n).Offset(0, 13)).Copy

Sheets("Sheet1").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
n = n + 1
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Then run the macro. Hope it helps!

 

[Partho]

Not sure why your macro run button is greyed out; I am not familiar with Mac. Do any other forum members know how to enable the button? Otherwise you could try researching this topic by Googling. By the way, if you are able to run the macro you may find the decimal numbers for the first country are out of alignment by one row; for some reason they start from row2 instead of row1, So you will have to manually fix the first country's numbers. The macro works as expected for all the other countries.
Meanwhile I will try to develop a formula for you which is capable of being copied into column C.