How can I copy data vertically

  • Thread starter Thread starter 1234
  • Start date Start date
1

1234

Hi,

I have data like this:

B C D
text1 text2 text3
text22 text23 text24

I need data to show like this in column A

A

text1
text2
text3
text22
text23
text24

How can I do this?

Thanks!!
 
I have data like this:
B                 C             D
text1           text2           text3
text22  text23  text24

I need data to show like this in column A

  A

text1
text2
Click to expand...
(etc, etc)

This solution may appear a little inelegant, but sometimes you have to
put up with that for a "one formula fits all" approach.

Assuming B1 holds "text1", D2 holds "text24" and you want to start
putting "text1" in A1, "text2" in A2, etc, then put this formula into
A1 and fill down as necessary:

=INDEX(B:D,CEILING(ROW(A1)/3,1),IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3)))

...and that should fill in Column A for any number of rows that you
have completed in B, C &D.

HTHs.
 
Your source data as posted assumed in B1:D1 down
In A1: =OFFSET($B$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy down as far as required to exhaust the extract
 
Assumes always 3 columns >then just delete the original columns

Sub transposerows()
mc = 2 'column B
lr = Cells(Rows.Count, mc).End(xlUp).Row
For i = 1 To lr
nar = Cells(Rows.Count, mc + 3).End(xlUp).Row + 1
Cells(i, mc).Resize(, 3).Copy
Cells(nar, mc + 3).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Next i
End Sub
 
Back
Top