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.

If anyone how to run macro in VBA, could you please help me?

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.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
2
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.
0
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
0
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?
0