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

Author Message
asdir
General User

Joined: 07 May 2007
Posts: 36

 Posted: Sun Nov 25, 2007 3:21 am    Post subject: How to turn an unequal panel into an equal one or a matrix 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
h1h
OOo Enthusiast

Joined: 18 Jun 2006
Posts: 152
Location: Switzerland

Posted: Sun Nov 25, 2007 9:07 am    Post subject:

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
davidh182

Joined: 01 Apr 2004
Posts: 413

 Posted: Mon Nov 26, 2007 2:49 am    Post subject: 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... )
h1h
OOo Enthusiast

Joined: 18 Jun 2006
Posts: 152
Location: Switzerland

 Posted: Tue Nov 27, 2007 1:50 pm    Post subject: 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
asdir
General User

Joined: 07 May 2007
Posts: 36

 Posted: Thu Nov 29, 2007 7:10 am    Post subject: 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? _________________OpenOffice 3.2 on Ubuntu 10.04
 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