Array to single column

[danleung1]

I'm sure this has been discuss before, but It was not quite what I
want to do, I want to go from

A D G J M
B E H K N
C F I L O to

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O

newbie excel user, TIA

 

[Bernie Deitrick]

Dan,

Use this formula in a cell

=INDEX($A$1:$E$5,MOD(ROW()-ROW($A$7)-1,ROWS($A$1:$E$5))+1,INT((ROW()-ROW($A$7)-1)/COLUMNS($A$1:$E$5))+1)

Watch the line wrapping.... Replace the three instances of $A$1:$E$5 with the address of the block
of data, replace the two instances of $A$7 with the address of the cell one row above where you
place this formula (both should be in absolute style - with the $ $ ) and then copy down until you
get an error.

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

I'm sure this has been discuss before, but It was not quite what I
want to do, I want to go from

A D G J M
B E H K N
C F I L O to

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O

newbie excel user, TIA

There are probably simpler formulas to generate the required series, but this
should work:

Select your table and NAME it tbl.

Paste this formula in some cell:

=IF(ROWS($1:1)>COUNTA(tbl),"",INDEX(tbl,ROWS($1:1)-INT((ROWS($1:1
)-1)/ROWS(tbl))*ROWS(tbl),INT((ROWS($1:1)-1)/ROWS(tbl))+1))

Then fill down at least far enough.
--ron

 

[Rick Rothstein \(MVP - VB\)]

If you can use a macro, this will **move** the contents of whatever is range
is selected from a rectangular array of values to a linear column of values
(preserving the first column of the selection)...

Sub ListSelection()
Dim X As Long
Dim NumRows As Long
Dim NumCols As Long
NumRows = Selection.Rows.Count
NumCols = Selection.Columns.Count
For X = 1 To NumCols - 1
Selection(1).Offset(NumRows * X, 0).Resize(NumRows, 1).Value= _
Selection(X + 1).Resize(NumRows, 1).Value
Next
Selection(1).Offset(0, 1).Resize(NumRows, NumCols - 1).Clear
End Sub

If there are formulas involved in your selection array, then change the two
occurrences of "Value" to "Formula".

Rick

 

[T. Valko]

Another one:

Assume your data is in the range E1:I3

Assume you want the data extracted starting in cell A1.

Enter this formula in A1 and copy down until you get a contiguous return of
0s:

=OFFSET(E$1,MOD(ROWS($A$1:A1)-1,3),INT((ROWS($A$1:A1)-1)/3))

Where 3 = number of rows in your table.

Or, assuming there are no empty cells within the table, this version will
return blanks once you've extracted all the data:

=IF(ROWS(A$1:A1)<=COUNTA(E$1:I$3),OFFSET(E$1,MOD(ROWS($A$1:A1)-1,3),INT((ROWS($A$1:A1)-1)/3)),"")

Or, this version calculates the number of rows in the table:

=IF(ROWS(A$1:A1)<=COUNTA(E$1:I$3),OFFSET(E$1,MOD(ROWS($A$1:A1)-1,ROWS(E$1:I$3)),INT((ROWS($A$1:A1)-1)/ROWS(E$1:I$3))),"")

Or, if you have the Morefunc.xll add-in from http://xcell05.free.fr/english/
then this array formula** entered in A1 and copied down:

=INDEX(ARRAY.JOIN(TRANSPOSE(E$1:I$3)),ROWS(A$1:A1))

With an error trap:

=IF(ROWS(A$1:A1)<=COUNTA(E$1:I$3),INDEX(ARRAY.JOIN(TRANSPOSE(E$1:I$3)),ROWS(A$1:A1)),"")