# Copy and paste/transpose each row by n times

Solved/Closed
liznguyen - Oct 19, 2011 at 10:29 PM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
- Oct 21, 2011 at 03:36 AM
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.

Thank you very much.

## 2 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Oct 20, 2011 at 05:54 AM
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.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
Thank you very much Venkat. It works perfectly. You saved Bankscope researcher. Have you ever used SAS before? I need to delete banks that have less than three consecutive yearly observations. Thank you.
Thank you very much Venkat. It works perfectly. You saved Bankscope researchers :) . Have you ever used SAS before? I need to delete banks that have less than three consecutive yearly observations. If you have ever encountered this problem, could you please help me? Thank you
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Oct 21, 2011 at 03:36 AM
what is SAS?