Array to single column
 

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

Array to single column
 

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


wrote in message
...
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

Array to single column
 

On Tue, 26 Feb 2008 08:36:00 -0800 (PST), wrote:

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

Array to single column
 

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


wrote in message
...
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

Array to single column
 

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(R OWS($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(R OWS($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)),"")



--
Biff
Microsoft Excel MVP


wrote in message
...
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