Copy and paste/transpose each row by n timesSolved/Closed

Question

Hello, 

To get my data in to an Enterprise program, I need to have my data like the following: 

Col A Col B Col C 
Bank A 2010  1      
Bank A 2009  3      
Bank A 2008  4      
Bank A 2007  2      
Bank B 2010  1      
Bank B 2009  6      
Bank B 2008  9      
Bank B 2007  3      

My data at the moment is: 

Col A Col B Col C Col D  Col E     
Bank  2010 2009 2008   2007
Bank A  1      3       4        2
Bank B   1      6       9       3
Note that the number of years are the same for each bank, but may be longer or shorter than the current sample. 
The numbers in Col C are varied between banks. 

If anyone how to run macro in VBA, could you please help me? 

Thank you very much. 

Configuration: Windows 7 / Firefox 3.6.10

venkat1926 · Accepted Answer

data is like this from A1

Bank	2010	2009	2008	2007
bank A	1	3	4	2
bank B	1	6	9	3

try this macro 

Sub test()
Dim rrow As Range, rcol As Range, crow As Range, ccol As Range, dest As Range
Set rrow = Range(Range("a2"), Range("A2").End(xlDown))
Set rcol = Range(Range("B1"), Range("B1").End(xlToRight))
For Each ccol In rcol
For Each crow In rrow
Set dest = Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)
dest = crow
dest.Offset(0, 1) = ccol
dest.Offset(0, 2) = Intersect(Rows(crow.Row), Columns(ccol.Column))
Next crow
Next ccol
Range("J2").CurrentRegion.Sort key1:=Range("J1"), header:=xlNo
Range("J2").CurrentRegion.Cut Range("J1")
End Sub
   
   
Sub undo()
Range(Range("J1"), Range("J1").End(xlToRight)).EntireColumn.Delete
End Sub

you will in column j1 down and beyond as 

bank A	2010	1
bank A	2009	3
bank A	2008	4
bank A	2007	2
bank B	2010	1
bank B	2009	6
bank B	2008	9
bank B	2007	3

venkat1926 · Answer

what is SAS?

Copy and paste/transpose each row by n times

2 replies

Subscribe To Our Newsletter!