Combinations in Calc (was "Permutations in Calc" )

[take2]

Hello, is it possible to create permutations combinations in Calc? It is the same thing answered here for Excel.

What I have in the document:

A     B    
Abc   12:34
Def   56:78
Ghi   90:12
Jkl   34:56
...

What I want to achieve with those values:

C    D      E    F
Abc  12:34  Def  56:78
Abc  12:34  Ghi  90:12
Abc  12:34  Jkl  34:56
Def  56:78  Ghi  90:12
Def  56:78  Jkl  34:56
Ghi  90:12  Jkl  34:56
...

Edit @Karolus: its about Combinations (of 2) but not Permutations /edit

Explanation:

Columns A and B can contain any combination of text and numbers (if that is important at all), this example only displays the most common structure. It should create combinations only for rows on the way down, i. e. “Abc…Def…” is enough, there shouldn’t be “Def…Abc…”.

Here is the solution for Excel (credits to John):

Sub CreatePermutation()

Dim FirstCell As Integer
Dim SecondCell As Integer
Dim NumRows As Integer
Dim OutputRow As Long

    ' Get the total number of rows in column A
    NumRows = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row()
    
    ' You want to start outputting in row 1
    OutputRow = 1
    
    For FirstCell = 1 To NumRows - 1
        For SecondCell = FirstCell + 1 To NumRows

            ' Put in the data from the first cell into columnc C & D
            Cells(OutputRow, 3).Value = Cells(FirstCell, 1).Value
            Cells(OutputRow, 4).Value = Cells(FirstCell, 2).Value
            
            ' Put in the data from the second cell into column E & F
            Cells(OutputRow, 5).Value = Cells(SecondCell, 1).Value
            Cells(OutputRow, 6).Value = Cells(SecondCell, 2).Value
            
            ' Move to the next row to output
            OutputRow = OutputRow + 1

        Next SecondCell
    Next FirstCell
End Sub

Is it possible to translate this macro to Calc or some other way to perform this task?

[Lupp]

Better use formulae instead of a “macro” (Sub). See attached.

ask51731UpwardPairs002.ods

Editing with regard to the comment:

“This seems quite complicated, i. e. I need to manually setup columns A-H, right?”

No. B1:B5 collect a bit of meta-information. A and B are empty with that exception. G throguh I (I as reserve) are the data columns. C and D are helper columns containing simple formulae filled down and K through P are output columns containing two slightly different formulae again filled down as far as needed. The sheet is an engine processing whatever you put into the appropriate places. E, F and J are left empty again, and I, M, and P are kept as reserve for an additional data column which will come rather sooner than later.

“Number of values is never the same, sometimes it’s 4-5 of them, sometimes it’s 30-40,…”

That’s the reason for what I used the cell B2 and prepared the output range for up to 10000 rows. This can easily be extended.

Using custom programming was often discussed under fundamental points of view. I won’t repeat the arguments here. For my part I go into programming only in very few cases and nearly without exception creating FUNCTIONs, not SUBs. That’s not because I dislike programming (I once loved it). It’s because it is in conflict with important principlec applicable to office documents, long term interchangeability one of the most valuable.

We will see you once in a while here looking for a variant / extension / adaption of your SUB. I hope, you are welcome then.

[take2]

This seems quite complicated, i. e. I need to manually setup columns A-H, right? Number of values is never the same, sometimes it’s 4-5 of them, sometimes it’s 30-40, so I’d prefer a solution that doesn’t care about the number of those values.

[karolus]

technically we talk about Combinations not Permutations

given List of 4 : (1, 2, 3, 4)

2 member Permutations:

((1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 3),
(2, 4),
(3, 1),
(3, 2),
(3, 4),
(4, 1),
(4, 2),
(4, 3))

and 2 member Combinations:

((1, 2),
 (1, 3),
 (1, 4),
 (2, 3),
 (2, 4),
 (3, 4))

with python instead (basic, vba):

from itertools import combinations

def combinations_of_two(*_):
    """
    combinations of 2 rows from Current Selection
    output to the columns right
    """

    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    data = sel.DataArray
    sheet = sel.Spreadsheet
    out = tuple( a + b for a, b in combinations( data, 2 ) )
    positions = (2, 1, 5, len(out))
    target = sheet.getCellRangeByPosition(*positions)
    target.setDataArray(out)

[take2]

Thanks for the suggestion, but I get this error:

com.sun.star.uno.Exception (Error during invoking function combinations_of_two in module file:///usr/…/combinations.py (<class ‘ooo_script_framework.com.sun.star.lang.IndexOutOfBoundsException’>:
/usr/…/combinations.py:15 in function combinations_of_two() [target = sheet.getCellRangeByPosition(*positions)]
/usr/lib/libreoffice/program/pythonscript.py:869 in function invoke

Is there something else I need to add in that document?

[karolus]

yes, you have to select the range from which the Combinations are should come from.

and btw.:
store your script into path:
/home/ <you> /.config/libreoffice/4/user/Scripts/python/combinations.py

[take2]

That did it, thank you! Only minor thing that could be improved is that combinations are displayed from row 2 downwards - it would be better if they could start in row 1, but it’s important that it works.

[karolus]

change third line from bottom up:

positions = ( 2, 0, 5, len(out)-1 )