Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose Data | Excel Discussion (Misc queries) | |||
transpose data | Excel Discussion (Misc queries) | |||
Transpose certain data | Excel Discussion (Misc queries) | |||
transpose data | Excel Discussion (Misc queries) | |||
transpose data | Excel Worksheet Functions |