transpose a data set
 

I want an easy way to take the following data set:
1 2 3
4 5 6
7 8 9
and turn it into a single column of data like this:
1
2
....
9

I know how to transpose data and I can do 3 transpositions for this small
set, but I have hundreds of rows and a repeating application for a function
that will do this. When I use autofill Excel keeps skipping rows of data!
Thanks in advance.

transpose a data set
 

One easy way to derive the desired results

Source data assumed in A1:C1 down
In any startcell, say in F2:
=OFFSET(A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy F2 down as far as required to exhaust the source data extract

Adapt-to-suit keys:
A1 = top-left anchor cell of source data
"3" in INT/MOD = the number of cols

Voila? Do click the YES button below. It should work fine
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Hjordan" wrote:
I want an easy way to take the following data set:
1 2 3
4 5 6
7 8 9
and turn it into a single column of data like this:
1
2
...
9

I know how to transpose data and I can do 3 transpositions for this small
set, but I have hundreds of rows and a repeating application for a function
that will do this. When I use autofill Excel keeps skipping rows of data!
Thanks in advance.

transpose a data set
 

Thanks Max! I tested it and it worked perfectly. I figured it had something
to do with dividing the row number and using the remainder, but I couldn't
get it all put together correctly in a formula. This is definitely something
I will use in the future and I appreciate that this solution doesn't require
writing a macro.

"Max" wrote:

One easy way to derive the desired results

Source data assumed in A1:C1 down
In any startcell, say in F2:
=OFFSET(A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy F2 down as far as required to exhaust the source data extract

Adapt-to-suit keys:
A1 = top-left anchor cell of source data
"3" in INT/MOD = the number of cols

Voila? Do click the YES button below. It should work fine
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Hjordan" wrote:
I want an easy way to take the following data set:
1 2 3
4 5 6
7 8 9
and turn it into a single column of data like this:
1
2
...
9

I know how to transpose data and I can do 3 transpositions for this small
set, but I have hundreds of rows and a repeating application for a function
that will do this. When I use autofill Excel keeps skipping rows of data!
Thanks in advance.

transpose a data set
 

Delighted to hear. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Hjordan" wrote in message
...
Thanks Max! I tested it and it worked perfectly. I figured it had
something
to do with dividing the row number and using the remainder, but I couldn't
get it all put together correctly in a formula. This is definitely
something
I will use in the future and I appreciate that this solution doesn't
require
writing a macro.