[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
lownotes
Newbie

Joined: 20 Jul 2012
Posts: 3

 Posted: Fri Jul 20, 2012 10:10 am    Post subject: SOLVED: How to transpose rows into columns 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
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Fri Jul 20, 2012 2:15 pm    Post subject:

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).
lownotes
Newbie

Joined: 20 Jul 2012
Posts: 3

 Posted: Sat Jul 21, 2012 1:40 pm    Post subject: Close but there is a problem in the formula 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?
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Sat Jul 21, 2012 6:13 pm    Post subject:

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).
Robert Tucker
Moderator

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

Posted: Sun Jul 22, 2012 7:05 am    Post subject:

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)
lownotes
Newbie

Joined: 20 Jul 2012
Posts: 3

 Posted: Sun Jul 22, 2012 10:58 am    Post subject: 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!
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

 Posted: Sun Jul 22, 2012 3:32 pm    Post subject: 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).
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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