How to flip vertically a column of data in Calc

[matu]

Hello All,

I have a problem to flip data in vertical column (not in ascending/descending order)

Currently the lottery results on the link below are shown with the latest lotto draw results to appear at the top of the table.
I want to flip the vertical column of data with the latest lotto draw results to show at the bottom, how can this be done?

http://www.alllotto.com/Washington-Hit-5-Dec-2015-Lottery-Results.php

I understand it can be done in MS Office 2013 with "flip vertically" from its ribbon menu but I want to do it in OpenOffice.

The macro below asterisks is from http://www.extendoffice.com/documents/excel/712-excel-flip-column.html
My question is - do you have a way to run the macro below in OpenOffice so that we can flip numerical data vertically?

Please reply
Thanks

matu

*************************************************************************************************************************************
Flip a column of data order in Excel with VBA

If you know how to use a VBA code in Excel, you may flip / reverse data order vertically as follows:

Step 1: Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

Step 2: Click Insert > Module, and paste the following macro in the Module Window.

VBA: Flip / reverse a range data order vertically in Excel.

Code: Select all

Sub FlipColumns()
'Updateby20131126
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For j = 1 To UBound(Arr, 2)
    k = UBound(Arr, 1)
    For i = 1 To UBound(Arr, 1) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(k, j)
        Arr(k, j) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

[karolus]

Hallo

why not sorting on Date-Column?

[Lupp]

If "matu" is insisting on custom code he may use the following from my toolbox:

Code: Select all

Function CentralReflection(pRange())
REM The function must be used in array mode.
REM It is applicable to any m x n - range where m, n are integers, the value 1 allowed in both places.
If NOT IsArray(pRange) Then
			Dim hRange(1 To 1, 1 To 1)
	hRange(1,1) = pRange
	pRange = hRange
EndIf
		Dim a As Long, b As Long, m As Long, n As Long,  aLim As Long, bLim As Long, h
m = UBound(pRange(),1)
n = UBound(pRange(),2)
aLim = m \ 2
REM The upper half and the lower half of the array are mirrored now over the center excluding the middle row if one exists at all.
bLim = n
For a = 1 To aLim
	For b = 1 To bLim
		h = pRange(a, b)
		pRange(a, b) = pRange(m-a+1, n-b+1)
		pRange(m-a+1, n-b+1) = h
	Next b
Next a
REM Only in case of an odd number of rows two half rows need mirroring one onto the other.
REM Including this in the above loop for a would mirror twice and thus lead back to the original middle row.
REM A rejected alternative was to limit the loop for b conditionally.
If (m MOD 2) = 1 Then
	a = aLim + 1
	bLim = n \ 2
	For b = 1 To bLim
		h = pRange(a, b)
		pRange(a, b) = pRange(m-a+1, n-b+1)
		pRange(m-a+1, n-b+1) = h
	Next b
EndIf
CentralReflection = pRange
End Function

As I prefer to not manipulate data in situ based on custom programming but to create a function occupying an output range, I did it this way. I thus got the advantage of an easy way to pass the range as parameter for free. Of course, "matu" may rework the code for flipping in situ. The range to work on must be passed in another way to the then Sub.

Usage: I did not make a difference about flipping horizontally or vertically because both the operations were subsumed under the term of central reflection. Therefore it was also obsolete to speak of columns or of rows. Rectangular ranges are what the function is working on.
An application of the function only makes sense if entered in array mode (Ctrl+Shift+Enter).

Flipping a group of columns may either be done for every column one by one or by an adapted routine for axial mirroring.

Addendum:
I missed to mention that flipping and central reflection together with a move to a new location can also be done based on standard functions.

Code: Select all

{=OFFSET(A1;ROWS(A1:A18)-ROW(A1:A18);COLUMNS(A1:B1)-COLUMN(A1:B1))}

e.g. will produce the range A1:B18 mirrored over the center to the range strating with the cell where the formula is entered.

Code: Select all

{=OFFSET(A1;ROWS(A1:A18)-ROW(A1:A18);COLUMN(A1:B1)-1)}

will vertically flip and move the same range.[/strike]
Sorry! Anothger case where a solution well working in LibreOffice did not work in AOO.

[Villeroy]

A Python macro:

Code: Select all

def reverseDataArray():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.getCurrentSelection()
    a = sel.getDataArray()
    sel.setDataArray(a[::-1])

[karolus]

Hallo

@villeroy
In the latter case I would prefer the slightly more obvious use of `reversed` -runtime-function, because not any reader knows about "slicing-syntax" [::-1] in python.

Code: Select all

def flip_selection():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sel.setDataArray( tuple( reversed( sel.DataArray ) ) ) 

[karolus]

Hallo

@Lupp

Code: Select all

=OFFSET(A19;-ROW(A1:A18);0) 

Karolus

[jackrcook]

A very simple way to to this is to insert a temporary helper column. Put numbers in the temporary column starting with 1 down to whatever you need. Then sort the entire data range including the helper column by reversing the sort order of the helper column from largest to smallest. The adjacent column (or columns) are now in reverse order as desired. Lastly, you can delete the helper column.

[matu]

I tried both the VBA script and the Python macros but l must have made mistakes so they cannot flip. I will list what I did step by step and please let me know what went wrong in each step:

First use the VBA script

Step 1 - I used Alt + F11 to insert Lupp's script using the Edit button.
Step 2 - I blocked (high-lighted) column B (only the lotto draw results)
Step 3 - I clicked the Tools menu > Macro > Run Macro
Step 4 - I clicked inside the Library panel > My Macros > Standard > Module 1 then the CentralReflection appears inside Macro name panel
Step 5 - I clicked the Run button
Then the OpenOffice Error dialog box appears
A Scripting Framework error occurred while running ..... Standard.Module1.CentralReflection
Message: wrong number of parameters!

----------------------------------------------------------------------------------------------------------------

I ran Villeroy's Python scripts - I actually turned it to a Function reverseDataArray():

Function flip_selection():
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
sel.setDataArray( tuple( reversed( sel.DataArray ) ) )
End Function

and the error arrow points to doc = XSCRIPTCONTEXT.getDocument() . The error message is "runtime error: Object variable not set"

----------------------------------------------------------------------------------------------------------------

I ran Villeroy's Python scripts - I actually turned it to a Function reverseDataArray():

Function reverseDataArray():
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.getCurrentSelection()
a = sel.getDataArray()
sel.setDataArray(a[::-1])
End Function

and the error arrow points to sel.setDataArray(a[::-1]) . The error message is "syntax error: Parenthesis do not match"

But all these scripts seem to be fine

I am wondering if using column A (the most recent date is on the top of the column) and column B (the lotto draw results for a specific date) joined by some link such that if I sort the column A with the most recent date at the bottom, then the column B lotto results will automatically associate with it so that I do not have to flip the column B lotto results. Is there a way I can do that?

Please reply
Thanks

matu

[matu]

The reason why I used http://www.extendoffice.com/documents/e ... olumn.html
is because I used a script similar to that one in Ms Office 2003 and it worked fine. Now my old computer crashed so I cannot even access it let alone using MS Office 2003. Microsoft actually wanted to charge around US$220 if I plan to use MS Office 2003 on my Windows 8 last year - it is true no jokes,I actually phoned the support last year. They suggested this because there was a bunch of files need downloading and installed so as to be compatible with new file system of Windows 8.

My last idea of joining/referencing column A and column B together so that if I sort column A in reverse order, then column B will change automatically with changes in column A so we can save the flipping. It is similar to jackrcook's idea

Please help

matu

[Lupp]

I tried both the VBA script and ...

There was no VBA script. The language used was OpenOffice BASIC (pure; no usage of uno-API). The code was not a 'Sub' but a 'Function' definition. As the only parameter is not declared optional you will get an error to exactly this effect if you try to run the function as if it was a Sub. You need to create a formula in a Calc sheet passing a valid range to the parameter and enter it in array mode. See attached example.

I did not design the code specifically for the subject under discussion here, but simply took what I had in my toolbox and what was supposed to be easily adaptable to your needs. For the purposes I wrote it this function made obsolete to also have functions for flipping horizontally/vertically. See attached example!

If you definitely want to flip the current selection vertically in situ, you need to rewrite code or to resort to the possibly not yet reliable "Option VBAsupport 1". See attached again. The Sub will not catch an expectable error. (I suppose it also will not if applied in Excel.)

@karolus : Can you tell me for what reason the formula in A31 of the example does not work in AOO while it works in LibO?

[Villeroy]

Save the Python script in plain text with name suffix .py under %APPDATA%\OpenOffice\4\user\Scripts\Python\ (create subdirectory Python if necessary).