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

How to turn an unequal panel into an equal one or a matrix

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


Joined: 07 May 2007
Posts: 36

PostPosted: Sun Nov 25, 2007 3:21 am    Post subject: How to turn an unequal panel into an equal one or a matrix Reply with quote

I have the this:

Cou Year Value

Afgh 1980 4
Afgh 1982 5
Alba 1982 3
Alba 1982 2
Alba 1983 5
Argn 1980 2
. . .
. . .
. . .
Zamb 1979 3
Zamb 1979 1
Zimba 1980 2



, but I'd like to have that:

Afgh 1979
Afgh 1980 4
Afgh 1981
Afgh 1982 5
Afgh 1983
Alba 1979
Alba 1980
Alba 1981
Alba 1982 3
Alba 1983 5
Argn 1979
Argn 1980 2
Argn 1981
Argn 1982
Argn 1983
. . .
. . .
. . .
Zamb 1979 3
Zamb 1980
Zamb 1981
Zamb 1982
Zamb 1983
Zimb 1979
Zimb 1980 2
Zimb 1981
Zimb 1982
Zimb 1983



Is there any easy way to accomplish that? It doesn't matter if it involves 10 or more steps, the important thing is that I don't want to do it for every country in the world manually. (Caution: The double values for one year are no typos.)
_________________
OpenOffice 3.2
on
Ubuntu 10.04
Back to top
View user's profile Send private message
h1h
OOo Enthusiast
OOo Enthusiast


Joined: 18 Jun 2006
Posts: 152
Location: Switzerland

PostPosted: Sun Nov 25, 2007 9:07 am    Post subject: Reply with quote

try this macro. based on your original data being on sheet1 in columns a through c, the new data going to sheet2
Code:

Sub Main
obook = thiscomponent
osheet1 = obook.getsheets().getbyname("sheet1")
osheet2 = obook.getsheets().getbyname("sheet2")
oldr=0
newr=0
wco = osheet1.getcellbyposition(0,oldr).string
do
   for nc = 1979 to 1983
      coun = osheet1.getcellbyposition(0,oldr).string
      yea = osheet1.getcellbyposition(1,oldr).value
      nbr = osheet1.getcellbyposition(2,oldr).value
      if yea < nc and coun = wco then
         nc = yea
      else
         coun = wco
      end if
      osheet2.getcellbyposition(0,newr).string= coun
      osheet2.getcellbyposition(1,newr).value = nc
      wco = coun
      if nc = yea and nbr > 0 then
         osheet2.getcellbyposition(2,newr).value = nbr
         oldr=oldr+1
      end if
      newr=newr+1
   next nc
   wco = osheet1.getcellbyposition(0,oldr+1).string
loop while wco <> ""
End Sub

_________________
OOo 2.3 on Linux
Back to top
View user's profile Send private message
davidh182
OOo Advocate
OOo Advocate


Joined: 01 Apr 2004
Posts: 413

PostPosted: Mon Nov 26, 2007 2:49 am    Post subject: Reply with quote

Can possibly avoid writing a macro by concatenating country and year in a cell and using this in VLOOKUP (but haven't gone through all the details... Wink )
Back to top
View user's profile Send private message Send e-mail
h1h
OOo Enthusiast
OOo Enthusiast


Joined: 18 Jun 2006
Posts: 152
Location: Switzerland

PostPosted: Tue Nov 27, 2007 1:50 pm    Post subject: Reply with quote

of course you can try that, but it will not insert years missing in your original data. depending on how many countires you need to work through, doing this manually might be time consuming. it's up to you.
_________________
OOo 2.3 on Linux
Back to top
View user's profile Send private message
asdir
General User
General User


Joined: 07 May 2007
Posts: 36

PostPosted: Thu Nov 29, 2007 7:10 am    Post subject: Reply with quote

Thanks a million, h1h, the macro helped a lot. It saved me between 2 and 3 working days, I guess.
I still have to weed out those with double years, but that is easily done. (A simple IF-Command and then filter will do the trick, I guess)
I am wondering right now, if it is unusual to thank a whole community (others in this forum have helped me too) in the "acknowledgements" of theses? Wink
_________________
OpenOffice 3.2
on
Ubuntu 10.04
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