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 transpose rows into columns

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


Joined: 20 Jul 2012
Posts: 3

PostPosted: Fri Jul 20, 2012 10:10 am    Post subject: SOLVED: How to transpose rows into columns Reply with quote

I'm trying to create a data import CSV file for a website CMS.

I have in Word doc (s).
Title
Description
Blank Line
Title
Description
Blank Line

I want
Title,Description
Title,Description

Can anyone please help me figure this out.

THANKS!!


Last edited by lownotes on Sun Jul 22, 2012 10:59 am; edited 1 time in total
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Fri Jul 20, 2012 2:15 pm    Post subject: Reply with quote

With your first title in cell A1, then this in B1
Code:
=INDEX($A$1:$A$30;(ROW()*3)-2)

and this in C1
Code:
=INDEX($A$1:$A$30;(ROW()*3)-1)

Both dragged down as far as necessary.
Note the '$A$1:$A$30' portions of these formula should be expanded to match your list
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
lownotes
Newbie
Newbie


Joined: 20 Jul 2012
Posts: 3

PostPosted: Sat Jul 21, 2012 1:40 pm    Post subject: Close but there is a problem in the formula Reply with quote

Thank you for you help here.

I think we're close but that the formula is off. What you gave only works for the first row and then it gets out of sync.

What i found that is needed that the last value in the formula needs to add -1 to each row.

=INDEX($A$1:$A$50;(ROW()*3)-2)
=INDEX($A$1:$A$50;(ROW()*3)-3)
=INDEX($A$1:$A$50;(ROW()*3)-4)
=INDEX($A$1:$A$50;(ROW()*3)-5)

is there any way to increment -1 the last value as part of the formula?
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Sat Jul 21, 2012 6:13 pm    Post subject: Reply with quote

My formula picks rows - 1, 4, 7, 10, 13 as per sample supplied
Your new request picks rows - 1, 3, 5, 7, 9, 11
Code:
=INDEX($A$1:$A$50;(ROW()*3)-(ROW()+1))

_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Sun Jul 22, 2012 7:05 am    Post subject: Reply with quote

You can also use array formulas:

Code:
=INDEX($A$1:$A$50;(ROW(A1:A50)*3)-2)


entered with Ctrl+Shift+Enter

and:

Code:
=INDEX($A$1:$A$50;(ROW(A1:A50)*3)-1)


entered with Ctrl+Shift+Enter.

If you don't like any error messages you get you just need to use ISERROR:

Code:
=IF(ISERROR(INDEX($A$1:$A$50;(ROW(A1:A50)*3)-2));"";INDEX($A$1:$A$50;(ROW(A1:A50)*3)-2))

_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
lownotes
Newbie
Newbie


Joined: 20 Jul 2012
Posts: 3

PostPosted: Sun Jul 22, 2012 10:58 am    Post subject: Reply with quote

Whoooops... my bad

like I said, I had a bunch of docs. The one I grabbed (which wasn't the same as the first) to try your formula had a different construct.

title
description
title
description

that's why it didn't work for me. My fault!

I so appreciate your help an am now on my way!!!!

Have a great day!
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Sun Jul 22, 2012 3:32 pm    Post subject: Reply with quote

Happy we could help and you are sorted
Your thanks are appreciated
Cheers
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
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