Move Multi-rows of numbers to one column

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

Hello,
I keep thinking there must be a way to do this and I haven't found it yet.

I have a spreadsheet with 8 columns of about 15 numbers each so it looks
like this (example):

5079 5080 5082 5091 5108
5109 5111 5115 5117 5118
5119 5120, etc.

I really want all of these numbers in 1 column so I can use sort to make me
a list of all the numbers.

How do I do this without cutting and pasting all of the numbers from the
second column below the first, and then the third below the first and
second, etc.

Thanks, JW
 
Jerry said:
Hello,
I keep thinking there must be a way to do this and I haven't found it yet.

I have a spreadsheet with 8 columns of about 15 numbers each so it looks
like this (example):

5079 5080 5082 5091 5108
5109 5111 5115 5117 5118
5119 5120, etc.

I really want all of these numbers in 1 column so I can use sort to make me
a list of all the numbers.

How do I do this without cutting and pasting all of the numbers from the
second column below the first, and then the third below the first and
second, etc.

Thanks, JW
Click to expand...
One way, if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

Array enter into a column of cells at least large enough to accommodate
all the numbers =ArrayReshape(numberRange,COUNTA(numberRange),1)

Alan Beban
 
Alan,
I'm afraid the array function is over my head but thanks. I was trying an
OFFSET function sort of like:

=OFFSET(Sheet1!$A$1,(ROW+1)-(2-COLUMN(A13)),0)

which I saw in another answer I'm just having trouble figuring out how to
replace the row and column values to do what I want them to do.

I'm not used to working in Excel at all and didn't expect it to be as
esoteric as all this.

Thanks though, JW
 
With your data in A1:H15 (No Headers), in cell A16 put the following and copy
down:-

=OFFSET($A$1,MOD(ROW()-16,15),FLOOR((ROW()-16)/15,1))

Then copy and paste special as values
 
Jerry,

Unless you are going to do this often, I would paste the data into Wor
as a tabel, then use the table>convert tabel to text tool.
This should separate the data into one column.

Then copy the list back to excel
 
Back
Top