| View previous topic :: View next topic |
| Author |
Message |
reflux
Newbie
Joined: 29 May 2007
Posts: 4
|
 Posted: Wed Mar 26, 2008 12:00 am Post subject: merging columns in CALC |
 |
|
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 |
|
 |
swingkyd
OOo Advocate
Joined: 15 Sep 2004
Posts: 479
|
| Posted: Wed Mar 26, 2008 7:54 am Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
swingkyd
OOo Advocate
Joined: 15 Sep 2004
Posts: 479
|
| Posted: Wed Mar 26, 2008 8:38 am Post subject: |
|
|
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 |
|
|
|
|
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
|
