In Excel how can I transpose multiple rows into a single column?

I have rows of data all of equal length, e.g. A1:A5, B1:B5, C1:C5, etc. I want to put these into a single column, e.g. D1:D5, D6:D10, D11:D15, etc. There are no headers or row identifiers. I think that using INDIRECT with a combination of ROW and COLUMN values should work, but I can't seem to find the right combo.

2 Answers

With data in A1 through C5, in D1 enter:

=OFFSET($A$1,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3))

and copy down. This will transpose the data into a single column:

It is equally easy to place the data into a single column without transposition.

This will ignore column headers and will not delete the data from the original cells. Depending on your worksheet, you may need to change the values for the columns.

Sub Macro1() Const firstRowWithData = 2 ' assumes labels in row 1 Dim anyWS As Worksheet Dim copyRange As Range Dim CP As Integer For Each anyWS In ThisWorkbook.Worksheets For CP = Range("B1").Column To Range("D1").Column Set copyRange = anyWS.Range(anyWS.Cells(2, CP).Address & ":" & _ anyWS.Cells(Rows.Count, CP).End(xlUp).Address) copyRange.Copy anyWS.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) Next Next
End Sub

before

A Header B Header C Header
a b c
a b c
a b c

after

A Header B Header C Header
a b c
a b c
a b c
b
b
b
c
c
c 

Then manually delete the original columns.