code to cut row and paste it to column

[dennisli]

Good afternoon,
I have a data distributed as shown by table 1 from cell A1: all the data in three rows, each two columns are one calendar year information ( suppose the data spread until the last column). How can cut and paste it into two columns as shown by table 2 from cell A1? Code maybe the only solutions. Right now I just give up my manual cut and paste as I have the data until the last column.
Thanks lot.
Dennis

Table 1
2003 1 2002 3 2001 5
2004 2 2003 4 2002 6
2005 3 2004 5 2003 7
table 2
2003 1
2004 2
2005 3

2002 3
2003 4
2004 5

2001 5
2002 6
2003 7

 

[erik.van.geit]

dennisli,
try this
without any checks etcetera ...

Sub test()
For I = 1 To Columns.Count - 1 Step 2
Range(Cells(1, I), Cells(3, I + 1)).Copy Cells(I * 2 + 3, 1)
Next I
End Sub

kind regards,
Erik

 

[RalphA]

Hi, Eric. Since I don't do VBA, I came up with a formula solution.

Here is a formula solution, assuming you want the results starting in A6 (don't change A6, or the formulas will have to be adjusted!):

In A6, enter =INDIRECT(CHAR(64+COLUMN()+(ROW()-6)/2)&1)
In A7, enter =INDIRECT(CHAR(64+COLUMN()+(ROW()-7)/2)&1)
In A8, enter =INDIRECT(CHAR(64+COLUMN()+(ROW()-8)/2)&1)
Leave A9 blank.

Highlight the range A6:A8, do a Copy, highlight the range A6:A9+4*n, that is, highlight the range A6:A9 on down in steps of 4, then do a Paste.

Done!

 

[erik.van.geit]

Hi, Ralph,

Since I don't do VBA

Do you have a reason for this ?
Knowing both formulas and VBA, you can choose which solution would be the most adequate in the given circumstances.
I love VBA but often choose formula-solution

best regards,
Erik

 

[RalphA]

Eric, I have used BASIC to program some electrical engineering problems. Also, QuickBASIC 4.5. Just wet my feet in Visal Basic. Looked at VBA, and decided I didn't have enough reason to learn so many things! Everywhere I looked, there were new words, methods, what-have-you to learn, so, i decided to stay clear of VBA, even VB, unless I really can't solve my problems by programming in QuickBASIC. I am retired, 80 years old, and don't have much need to solve new problems, so, I'm taking the easy way, and sticking with what I already know, pretty much.

 

[erik.van.geit]

RalphA,
Thank you for responding!
Good to know why people make their decisions.
With all my respect
Erik

 

[dennisli]

thank you, guys, Ralpha and Erik. It's wonderful.