how to display rows of data in a single column
 

I would like to set up a simple formula turn the follow array into a single
column:

123456
abcdef

result:
1
a
2
b
3
c
4
d
5
e
6
f
Thank you.

how to display rows of data in a single column
 

one way would be to copy / past special + Transpose 1-6 into colum B. In
column A put 1 through 6. Then copy a-f and in B7 past special + transpose
and next in column A put 1 through 6 beside a-f. Then you can sort by A to
get it in the order you need.

If you have a larger list, you can setup a macro to do this and have the
first set 1-1000 with a formula doing the ordering and 1001-2000 for the next
set, copy past special to another sheet and sort.

I think the "offset" function could help here also?

"IUM" wrote:

I would like to set up a simple formula turn the follow array into a single
column:

123456
abcdef

result:
1
a
2
b
3
c
4
d
5
e
6
f
Thank you.

how to display rows of data in a single column
 

Don,
Thank you for the quick response, the 2 rows is only a sample, the
worksheets are quite large. I can use 'offset' to create a single column
if it is row by row. I am having problem when I need to take values from more
than one row and then form a single column. Wendy


"Don" wrote:

one way would be to copy / past special + Transpose 1-6 into colum B. In
column A put 1 through 6. Then copy a-f and in B7 past special + transpose
and next in column A put 1 through 6 beside a-f. Then you can sort by A to
get it in the order you need.

If you have a larger list, you can setup a macro to do this and have the
first set 1-1000 with a formula doing the ordering and 1001-2000 for the next
set, copy past special to another sheet and sort.

I think the "offset" function could help here also?

"IUM" wrote:

I would like to set up a simple formula turn the follow array into a single
column:

123456
abcdef

result:
1
a
2
b
3
c
4
d
5
e
6
f
Thank you.

how to display rows of data in a single column
 

This will work for 3 rows - say A1 to Z3,

with the pattern of
A1
A2
A3
B1
B2
B3
.... etc.

Start *anywhere* and copy down as needed:

=INDEX($A$1:$Z$3,MOD(ROWS($1:1)-1,3)+1,ROWS($1:3)/3)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"IUM" wrote in message
...
Don,
Thank you for the quick response, the 2 rows is only a sample, the
worksheets are quite large. I can use 'offset' to create a single
column
if it is row by row. I am having problem when I need to take values from
more
than one row and then form a single column. Wendy


"Don" wrote:

one way would be to copy / past special + Transpose 1-6 into colum B.
In
column A put 1 through 6. Then copy a-f and in B7 past special +
transpose
and next in column A put 1 through 6 beside a-f. Then you can sort by A
to
get it in the order you need.

If you have a larger list, you can setup a macro to do this and have the
first set 1-1000 with a formula doing the ordering and 1001-2000 for the
next
set, copy past special to another sheet and sort.

I think the "offset" function could help here also?

"IUM" wrote:

I would like to set up a simple formula turn the follow array into a
single
column:

123456
abcdef

result:
1
a
2
b
3
c
4
d
5
e
6
f
Thank you.

how to display rows of data in a single column
 

Ragdyer,
Thank you very much, it works perfectly...

"Ragdyer" wrote:

This will work for 3 rows - say A1 to Z3,

with the pattern of
A1
A2
A3
B1
B2
B3
.... etc.

Start *anywhere* and copy down as needed:

=INDEX($A$1:$Z$3,MOD(ROWS($1:1)-1,3)+1,ROWS($1:3)/3)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"IUM" wrote in message
...
Don,
Thank you for the quick response, the 2 rows is only a sample, the
worksheets are quite large. I can use 'offset' to create a single
column
if it is row by row. I am having problem when I need to take values from
more
than one row and then form a single column. Wendy


"Don" wrote:

one way would be to copy / past special + Transpose 1-6 into colum B.
In
column A put 1 through 6. Then copy a-f and in B7 past special +
transpose
and next in column A put 1 through 6 beside a-f. Then you can sort by A
to
get it in the order you need.

If you have a larger list, you can setup a macro to do this and have the
first set 1-1000 with a formula doing the ordering and 1001-2000 for the
next
set, copy past special to another sheet and sort.

I think the "offset" function could help here also?

"IUM" wrote:

I would like to set up a simple formula turn the follow array into a
single
column:

123456
abcdef

result:
1
a
2
b
3
c
4
d
5
e
6
f
Thank you.

how to display rows of data in a single column
 

You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"IUM" wrote in message
...
Ragdyer,
Thank you very much, it works perfectly...

"Ragdyer" wrote:

This will work for 3 rows - say A1 to Z3,

with the pattern of
A1
A2
A3
B1
B2
B3
.... etc.

Start *anywhere* and copy down as needed:

=INDEX($A$1:$Z$3,MOD(ROWS($1:1)-1,3)+1,ROWS($1:3)/3)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"IUM" wrote in message
...
Don,
Thank you for the quick response, the 2 rows is only a sample, the
worksheets are quite large. I can use 'offset' to create a single
column
if it is row by row. I am having problem when I need to take values from
more
than one row and then form a single column. Wendy


"Don" wrote:

one way would be to copy / past special + Transpose 1-6 into colum B.
In
column A put 1 through 6. Then copy a-f and in B7 past special +
transpose
and next in column A put 1 through 6 beside a-f. Then you can sort by
A
to
get it in the order you need.

If you have a larger list, you can setup a macro to do this and have
the
first set 1-1000 with a formula doing the ordering and 1001-2000 for
the
next
set, copy past special to another sheet and sort.

I think the "offset" function could help here also?

"IUM" wrote:

I would like to set up a simple formula turn the follow array into a
single
column:

123456
abcdef

result:
1
a
2
b
3
c
4
d
5
e
6
f
Thank you.

how to display rows of data in a single column
 

I should be able to figure this out, but I am stumped for now. How do you
reverse the order so that that the data is read across the row and then to
the next row? I will want it to end up in one column. I assume it has to do
with changing the rows command to the column command, but can't get it to
work. Is there a way to set the bottom of the array without knowing it in
advance, say be getting to look for an empty cell?

Brad


"RagDyeR" wrote:

You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"IUM" wrote in message
...
Ragdyer,
Thank you very much, it works perfectly...

"Ragdyer" wrote:

This will work for 3 rows - say A1 to Z3,

with the pattern of
A1
A2
A3
B1
B2
B3
.... etc.

Start *anywhere* and copy down as needed:

=INDEX($A$1:$Z$3,MOD(ROWS($1:1)-1,3)+1,ROWS($1:3)/3)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"IUM" wrote in message
...
Don,
Thank you for the quick response, the 2 rows is only a sample, the
worksheets are quite large. I can use 'offset' to create a single
column
if it is row by row. I am having problem when I need to take values from
more
than one row and then form a single column. Wendy


"Don" wrote:

one way would be to copy / past special + Transpose 1-6 into colum B.
In
column A put 1 through 6. Then copy a-f and in B7 past special +
transpose
and next in column A put 1 through 6 beside a-f. Then you can sort by
A
to
get it in the order you need.

If you have a larger list, you can setup a macro to do this and have
the
first set 1-1000 with a formula doing the ordering and 1001-2000 for
the
next
set, copy past special to another sheet and sort.

I think the "offset" function could help here also?

"IUM" wrote:

I would like to set up a simple formula turn the follow array into a
single
column:

123456
abcdef

result:
1
a
2
b
3
c
4
d
5
e
6
f
Thank you.