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

merging columns in CALC

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


Joined: 29 May 2007
Posts: 4

PostPosted: Wed Mar 26, 2008 12:00 am    Post subject: merging columns in CALC Reply with quote

Hi there expert team,
wondering of you can save me a LOAD of time.
I have a set of behavioral data, in columns by days, time in rows :

(March)
Time , DAY1, DAY2, DAY3
1200, sleep, move, eat
1210 sleep, eat , move
1220 move, eat , eat

and I want to turn them into single column summaries for the month like this:

MARCH
sleep
sleep
move
move
eat
eat
eat
move
eat

so that I can easily export them (month per column) into SPSS for frequency based analysis,

Is there a way to consolidate columns like this? Apart from lots of manual copy-paste (I have a years worth of data...)

Heres hoping

many thanks
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Wed Mar 26, 2008 7:54 am    Post subject: Reply with quote

would you be willing to use a macro?
How consistent is the data in the month?
[edit]eg:
Does the hours and days always have the same number? or are some rows have only two entries, or some columns have 9 while others have 2.
Does the order matter? here you are going by column. Can it be done by row as well?
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 Mar 26, 2008 8:00 am    Post subject: Reply with quote

Have a look at this file http://www.mediafire.com/download.php?ft321idd1v2
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Wed Mar 26, 2008 8:38 am    Post subject: Reply with quote

Villeroy wrote:
Have a look at this file http://www.mediafire.com/download.php?ft321idd1v2

Very nice solution Villeroy! Here is a breakdown of how I got it to work:
Quote:
Row/Col,B,C,D,E,F
2,Row,Col,Range Idx,Value,Formula
3,0,0,1,sleep,=T(INDEX(march;B3+2;C3+2;D3))
4,1,0,1,sleep,=T(INDEX(march;B4+2;C4+2;D4))
5,2,0,1,move,=T(INDEX(march;B5+2;C5+2;D5))
6,0,1,1,move,=T(INDEX(march;B6+2;C6+2;D6))
7,1,1,1,eat,=T(INDEX(march;B7+2;C7+2;D7))
8,2,1,1,eat,=T(INDEX(march;B8+2;C8+2;D8))
9,0,2,1,eat,=T(INDEX(march;B9+2;C9+2;D9))
10,1,2,1,move,=T(INDEX(march;B10+2;C10+2;D10))
11,2,2,1,eat,=T(INDEX(march;B11+2;C11+2;D11))

where "march" is a named range of the month.
It would be lovely if I could figure out how to get multiple ranges as a single name. Apparently this does not work so one would need to get the range for each month. But if multiple ranges could be named, then the "Range Idx" column would be useful.
Here is the "march" named range:
Quote:
Time,DAY1,DAY2,DAY3
1200,sleep,move,eat
1210,sleep,eat,move
1220,move,eat,eat
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