# Combinations in Calc (was "Permutations in Calc" )

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?

edit retag close merge delete

Sort by » oldest newest most voted

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
out = tuple( a + b for a, b in combinations( data, 2 ) )
positions = (2, 1, 5, len(out))
target = sheet.getCellRangeByPosition(*positions)
target.setDataArray(out)

more

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?

( 2015-06-10 12:23:08 +0200 )edit

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

and btw.:
/home/ <you> /.config/libreoffice/4/user/Scripts/python/combinations.py

( 2015-06-10 12:33:56 +0200 )edit

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.

( 2015-06-10 13:56:56 +0200 )edit

change third line from bottom up:

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

( 2015-06-10 14:08:03 +0200 )edit

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

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.

more