OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved]How to Copy Multiple Data Columns to a single colmn

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
cucubura
General User
General User


Joined: 04 Jun 2011
Posts: 7

PostPosted: Tue Jun 19, 2012 11:34 am    Post subject: [Solved]How to Copy Multiple Data Columns to a single colmn Reply with quote

Suppose i have a data like this

1 2 3 4 5 6 7 8
9 10 11 12 13 14 15

into this

1
2
3
4
5
6
7
8
9
10 and so on. Thanks for your answers.


Last edited by cucubura on Wed Jun 20, 2012 8:30 am; edited 1 time in total
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Tue Jun 19, 2012 12:22 pm    Post subject: Reply with quote

Select all the numbers in a row and copy. Click into a vacant column then Edit > Paste Special and check Transpose.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue Jun 19, 2012 4:49 pm    Post subject: Reply with quote

Attached doc (Transpose variable columns of data.ods) shows one way using a formula in column M that refers to three helper formulae in columns J, K and L.
The helper formula in column J has a zero included in the cell above.
If the data includes text (see 2nd sheet) then the SUMPRODUCT formula replaces the COUNT formula.
http://www.mediafire.com/view/?saf6osr8nbsgkam
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).


Last edited by ken johnson on Sun Sep 23, 2012 3:12 am; edited 1 time in total
Back to top
View user's profile Send private message
cucubura
General User
General User


Joined: 04 Jun 2011
Posts: 7

PostPosted: Wed Jun 20, 2012 4:09 am    Post subject: It will take too much time if i had so many coloumn Reply with quote

I want all the columns and rows to be transposed into a single column
Back to top
View user's profile Send private message
cucubura
General User
General User


Joined: 04 Jun 2011
Posts: 7

PostPosted: Wed Jun 20, 2012 4:12 am    Post subject: I already know this Reply with quote

JohnV wrote:
Select all the numbers in a row and copy. Click into a vacant column then Edit > Paste Special and check Transpose.


I already know this. What i wanted is to automatically copy all the columns into a single columns instead of manually copying each rows and then transposing
Back to top
View user's profile Send private message
cucubura
General User
General User


Joined: 04 Jun 2011
Posts: 7

PostPosted: Wed Jun 20, 2012 4:24 am    Post subject: Reply with quote

ken johnson wrote:
Attached doc shows one way using a formula in column M that refers to three helper formulae in columns J, K and L.
The helper formula in column J has a zero included in the cell above.
If the data includes text (see 2nd sheet) then the SUMPRODUCT formula replaces the COUNT formula.
http://www.4shared.com/file/n4BcHz6b/Transpose_variable_columns_of_.html
Ken Johnson


Hi, your solution worked like a wonder. I am using text and number sheet. But what should i do to increase the columns? I have more than 121 columns...
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jun 20, 2012 4:59 am    Post subject: Reply with quote

InputRange = name of the incoming cell range
Other sheet:
A1: 1
A2: =$A1+($B2=$C2)
B2: =COUNTIF($A$1:$A1;$A1)
C2: =COUNT(INDEX(InputRange;$A1))
D1: 1
D2: =IF($A2=$A1;$D1+1;1)
E1: =INDEX(InputRange;$A1;$D1)

Copy E1 to E2
Copy down the formulas in row #2 until you get errors in E.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed Jun 20, 2012 5:20 am    Post subject: Reply with quote

Maybe have the formulae on another sheet.
See attached (Transpose many columns of data.ods)...
http://www.mediafire.com/view/?pxylrjq4pyxxtqe
Formulae on other sheet can handle data in up to column EZ (column 156) and down to row 500 on the Text and Numbers sheet.
If you have more than 500 rows you will need to change the 500s in the formula in 'Transposed Columns'.B2 to a greater number of rows...
IF(ROW(B2)-ROW(B$2)>=SUMPRODUCT('Text and Numbers'.$A$2:$EZ$500<>"");"";MATCH(ROW(B2)-ROW(B$2);$A$1:$A$500))
[SUMPRODUCT, being an array formula, is very slow when given a large range of cells to process]

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).


Last edited by ken johnson on Sun Sep 23, 2012 3:08 am; edited 1 time in total
Back to top
View user's profile Send private message
cucubura
General User
General User


Joined: 04 Jun 2011
Posts: 7

PostPosted: Wed Jun 20, 2012 8:19 am    Post subject: Reply with quote

ken johnson wrote:
Maybe have the formulae on another sheet.
See attached
http://www.4shared.com/file/iI1eHZ5H/Transpose_many_columns_of_data.html
Formulae on other sheet can handle data in up to column EZ (column 156) and down to row 500 on the Text and Numbers sheet.
If you have more than 500 rows you will need to change the 500s in the formula in 'Transposed Columns'.B2 to a greater number of rows...
IF(ROW(B2)-ROW(B$2)>=SUMPRODUCT('Text and Numbers'.$A$2:$EZ$500<>"");"";MATCH(ROW(B2)-ROW(B$2);$A$1:$A$500))
[SUMPRODUCT, being an array formula, is very slow when given a large range of cells to process]

Ken Johnson


Thanks for your kind help aussie. I appreciate your help and also willing to help other forum users in the future. Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group