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...

Afghanistan	1980
Afghanistan	1985
Afghanistan	1990
Afghanistan	2000
Afghanistan	2005
Afghanistan	2006
Afghanistan	2007
Afghanistan	2008
Afghanistan	2009
Afghanistan	2010
Afghanistan	2011
Afghanistan	2012
Afghanistan	2013
Albania	1980
Albania	1985
Albania	1990
Albania	2000
Albania	2005
Albania	2006
Albania	2007
Albania	2008
Albania	2009
Albania	2010
Albania	2011
Albania	2012
Albania	2013
Algeria	1980
Algeria	1985
Algeria	1990
Algeria	2000
Algeria	2005
Algeria	2006
Algeria	2007
Algeria	2008
Algeria	2009
Algeria	2010
Algeria	2011
Algeria	2012
Algeria	2013
Andorra	1980
Andorra	1985
Andorra	1990
Andorra	2000
Andorra	2005
Andorra	2006
Andorra	2007
Andorra	2008
Andorra	2009
Andorra	2010
Andorra	2011
Andorra	2012
Andorra	2013
Angola	1980
Angola	1985
Angola	1990
Angola	2000
Angola	2005
Angola	2006
Angola	2007
Angola	2008
Angola	2009
Angola	2010
Angola	2011
Angola	2012
Angola	2013
Antigua and Barbuda	1980
Antigua and Barbuda	1985
Antigua and Barbuda	1990
Antigua and Barbuda	2000
Antigua and Barbuda	2005
Antigua and Barbuda	2006
Antigua and Barbuda	2007
Antigua and Barbuda	2008
Antigua and Barbuda	2009
Antigua and Barbuda	2010
Antigua and Barbuda	2011
Antigua and Barbuda	2012
Antigua and Barbuda	2013
Argentina	1980
Argentina	1985
Argentina	1990
Argentina	2000
Argentina	2005
Argentina	2006
Argentina	2007
Argentina	2008
Argentina	2009
Argentina	2010
Argentina	2011
Argentina	2012
Argentina	2013
Armenia	1980
Armenia	1985
Armenia	1990
Armenia	2000
Armenia	2005
Armenia	2006
Armenia	2007
Armenia	2008
Armenia	2009
Armenia	2010
Armenia	2011
Armenia	2012
Armenia	2013

<tbody>
</tbody>

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

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

<tbody>
</tbody>

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.