| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
ozzie
OOo Advocate
Joined: 29 Jul 2010
Posts: 316
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). |
|
| Back to top |
|
|
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? |
|
| Back to top |
|
|
ozzie
OOo Advocate
Joined: 29 Jul 2010
Posts: 316
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). |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
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)) |
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
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! |
|
| Back to top |
|
|
ozzie
OOo Advocate
Joined: 29 Jul 2010
Posts: 316
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). |
|
| Back to top |
|
|
|
