| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
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 |
|
| Back to top |
|
|
davidh182
OOo Advocate
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...  ) |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
