how to display rows of data in a single column

  • Thread starter Thread starter IUM
  • Start date Start date
I

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.
 
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?
 
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
 
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)
 
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
 
Back
Top