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

[Solved] flip the data in a column

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


Joined: 08 Jun 2012
Posts: 12

PostPosted: Fri Jun 08, 2012 5:48 am    Post subject: [Solved] flip the data in a column Reply with quote

Hello. I am trying to reverse the data in a specific column but can't seem to do this seemingly simple task. I have a bunch of random data in each column like this:
Code:

 Col.A   Col.B   Col.C  etc.
============================
  djf     woe     sfj   etc.
  soi     sne     ofw   etc.
  wrj     aos     civ   etc.
  pql     rwl     dif   etc.
  vic     qwr     xfc   etc.

As you can see, they are not in any order and I do not want to sort them. I just want to reverse the data in just one column. So how could I for example change my example spreadsheet into the following? (Column B has been reversed while all others stay the same).
Code:

 Col.A   Col.B   Col.C  etc.
============================
  djf     qwr     sfj   etc.
  soi     rwl     ofw   etc.
  wrj     aos     civ   etc.
  pql     sne     dif   etc.
  vic     woe     xfc   etc.

I appreciate any help on this matter.

Tagged as solved - floris v, moderator
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Fri Jun 08, 2012 6:27 am    Post subject: Re: flip the data in a column Reply with quote

zagg wrote:
Hello. I am trying to reverse the data in a specific column but can't seem to do this seemingly simple task. I have a bunch of random data in each column like this:
Code:

 Col.A   Col.B   Col.C  etc.
============================
  djf     woe     sfj   etc.
  soi     sne     ofw   etc.
  wrj     aos     civ   etc.
  pql     rwl     dif   etc.
  vic     qwr     xfc   etc.

As you can see, they are not in any order and I do not want to sort them. I just want to reverse the data in just one column. So how could I for example change my example spreadsheet into the following? (Column B has been reversed while all others stay the same).
Code:

 Col.A   Col.B   Col.C  etc.
============================
  djf     qwr     sfj   etc.
  soi     rwl     ofw   etc.
  wrj     aos     civ   etc.
  pql     sne     dif   etc.
  vic     woe     xfc   etc.

I appreciate any help on this matter.


there probably is an easier way but I'd just insert a column beside the target column and put numbers 1 to n there and sort by the numbers.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Fri Jun 08, 2012 7:18 am    Post subject: Reply with quote

Hallo

This (Python)-code flips the current Selection in Spreadsheet:

Code:
def flip_selection():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.getCurrentSelection()
    sel.setFormulaArray( sel.getFormulaArray()[::-1] )



Karo
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Fri Jun 08, 2012 8:18 am    Post subject: Re: flip the data in a column Reply with quote

jrkrideau wrote:

there probably is an easier way but I'd just insert a column beside the target column and put numbers 1 to n there and sort by the numbers.


yea, that's the way I've been doing it but I was wondering if there was a quicker way to do it considering I have to do this a lot... I can't believe there isn't just like a button you push that automatically flips the data.
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Fri Jun 08, 2012 8:19 am    Post subject: Reply with quote

karolus wrote:
Hallo

This (Python)-code flips the current Selection in Spreadsheet:

Code:
def flip_selection():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.getCurrentSelection()
    sel.setFormulaArray( sel.getFormulaArray()[::-1] )



Karo


Hey thanks for the reply... But can you explain exactly what I do with this code? (I'm kinda a newbie with this stuff). thanks.
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Fri Jun 08, 2012 8:57 am    Post subject: Reply with quote

Hallo

Open your favourite Texteditor (Notepad++ etc. ) with new file.
Copy and paste the code above.
Store it into Path ...***/user/Scripts/python/sheethelper.py
    **first create subfolder python
    ***lookup the Parentfolder 'user' in OOo-menu →Tools→Options→Path...



In Calc select your range to flip and execute →Tools→Macros →Organize Makros→Python→MyMacros→sheethelper→flip_selection

Karo
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Fri Jun 08, 2012 10:43 am    Post subject: Reply with quote

it works like a charm!
just one question though... when I run it manually from the menu, it works fine. However, when I create a custom button on the toolbar and then try to use it, I get this error:
Code:

com.sun.star.uno.RuntimeExceptionError during invoking function
flip_selection in module
file:///***/user/Scripts/python/sheethelper.py
(<type 'exceptions.TypeError'>: flip_selection() takes no arguments (1 given)
 /usr/lib/openoffice/basis-link/program/pythonscript.py:790 in function
invoke() [ret = self.func(*args )]
)

Any idea how to fix it?
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Fri Jun 08, 2012 11:04 am    Post subject: Reply with quote

ok well, though I couldn't get the custom button on the toolbar working, I created a keyboard shortcut which is just as convenient and THAT does work. So thanks again, karolus! You really helped me out. Smile
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Fri Jun 08, 2012 11:08 am    Post subject: Reply with quote

Hallo
Quote:
Any idea how to fix it?


Insert a Dummy-argument in Functionsignatur:

def flip_selection(*dummy):

Karo
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Fri Jun 08, 2012 11:29 am    Post subject: Reply with quote

karolus wrote:
Hallo
Quote:
Any idea how to fix it?


Insert a Dummy-argument in Functionsignatur:

def flip_selection(*dummy):

Karo


yep, that works too!
thanks again for all your help.
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