how to display rows of data in a single column

[IUM]

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.

 

[Don]

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]

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

 

[Ragdyer]

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)

 

[IUM]

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

 

[RagDyeR]

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

 

[Brad]

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