Page 1 of 1
[Solved] Combine all combinations from single column
Posted: Wed Jun 12, 2019 9:25 am
by Tanjamuse
Hi.
I hope this is the right place to post this.
I'd like to combine all possible combinations (text) from a single column into another in a macro.
Lets say I've got 6 fruits (Apple, Orange, Grapefruit, Banana, Kiwi, Grapes) in column 1 (A1-A6). I'd like libreoffice to automatically combine all the possible combinations into column B. The data would then look like Apple orange, Apple grapefruit, apple banana etc etc.
I only need the pairs which are in alphabetical order, which means
Banana, Orange but not,
Orange, Banana.
I don't need duplicate pairs, like
Apple, Apple.
I would like if it was possible to also create a macro where it's Triple combined like: Apple, Banana, Orange.
I have a macro who can do this for Excel but I would really like one for LibreOffice as well since my Office subscription expires soon.
This is the Macro for uniquepairs for Excel:
Code: Select all
Public Sub GetUniquePairs()
Dim lastRow As Long
Dim thisRow As Long
Dim i As Long
Dim j As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
thisRow = 1
For i = 1 To lastRow - 1
For j = i + 1 To lastRow
Cells(thisRow, 3).Value = Cells(i, 1).Value & "," & Cells(j, 1).Value
thisRow = thisRow + 1
Next j
Next i
End Sub
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 11:23 am
by JeJe
See attached document. (you'll need to check if it does give results you want)
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 11:28 am
by karolus
Hallo
pretty simple with python:
Code: Select all
from itertools import combinations
def combine():
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.Sheets[0] # first Sheet
#sheet = doc.Sheets.getByIndex(0) #for ApacheOpenOffice
sourcerange = sheet.getCellRangeByName("A1:A6")
data = list(zip(*sourcerange.DataArray))[0]
cursor = sheet.createCursorByRange(sourcerange)
for k in range(2,4): # 2 … 3
kombis = tuple((', '.join(kombi),) for kombi in combinations(data, k))
cursor.collapseToSize(1, len(kombis))
cursor.gotoOffset(1,0)
cursor.setDataArray(kombis)
Assuming your
things to combine are in
A1:A6 of the very first Sheet, this Function puts the Combinations of 2 into Column B and the Combinations of 3 into Column C
Edit: @jeje: very ugly unreadable Code
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 11:41 am
by Tanjamuse
It keeps telling me to install JRE but I've just installed some java and it still doesn't work.
@ Karolus, where do I add the string of code you've given me?
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 12:16 pm
by JeJe
Edit: @jeje: very ugly unreadable Code
Absolutely... here it is with some added comments... I could rename the variables into something more meaningful but life is not endless. There will be simplifications... the question is have you something that works... if so use and move on says I...
Code: Select all
REM ***** BASIC *****
Sub Main()
Dim res As New Collection 'ADDING TO A COLLECTION WITH ON ERROR RESUME NEXT WILL REMOVE DUPLICATE ITEMS
On Error Resume Next
sheet= thiscomponent.currentcontroller.activesheet
redim a(5) 'PUT THE ITEMS INTO AN ARRAY
for i =0 to 5
a(i)= sheet.getcellbyposition(0,i).string
next
For i = 0 To UBound(a) 'GO THROUGH EVERY TWO COMBINATION IN THE ARRAY
For j = 0 To UBound(a)
If a(j) > a(i) Then 'IF IN ALPHABETICAL ORDER ADD TO THE COLLECTION
st = a(i) & " " & a(j)
res.Add st, st
End If
Next
Next
For i = 1 To res.Count 'WRITE TO SPREADSHEET
sheet.getcellbyposition(1,i-1).string = res(i)
Next
res.clear 'CLEAR THE COLLECTION FOR REUSE
For i = 0 To UBound(a) 'SAME THING FOR COMBINATIONS OF 3 ITEMS
For j = 0 To UBound(a)
For k = 0 To UBound(a)
If a(j) > a(i) Then
If a(k) > a(j) Then
st = a(i) & " " & a(j) & " " & a(k)
res.Add st, st
End If
End If
Next
Next
Next
For i = 1 To res.Count
sheet.getcellbyposition(2,i-1).string = res(i)
Next
End Sub
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 12:34 pm
by karolus
Hallo
The python-Code is embedded in attached Document
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 12:39 pm
by RoryOF
JeJe wrote:
Edit: @jeje: very ugly unreadable Code
Absolutely... here it is with some added comments... I could rename the variables into something more meaningful but life is not endless. There will be simplifications... the question is have you something that works... if so use and move on says I...
Nearly OT: I know from writing text that one can spend longer refining it than actually writing it; I have no doubt that the same applies to code. On Forum my approach is similar to JeJe's - get the task finished, even if by inelegant means.
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 12:44 pm
by Tanjamuse
Do I have to run java only from this site:
https://www.oracle.com/technetwork/java ... 33155.html
Because I've installed 2 different versions from another site and it still complains that there are no java.
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 12:52 pm
by RoryOF
In OpenOffice, you have (one time only) to open /Tools /Options /OpenOffice /Java, and select a Java on that screen. OO will usually find installed Javas within a few seconds.
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 12:57 pm
by karolus
Its a false bug, you dont need Java to run Basic or Python. click away this dumb messages and the →Makro→Execute- Dialog opens.
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 1:15 pm
by Tanjamuse
Thanks, It looks to be working fine. Can it be changed to make the amount of rows variable? The six rows earlier was just an example.
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 2:15 pm
by Lupp
(Since there is no AOO 6.1, I assume the signature of the OQ should read LibO...)
First of all I have to apologize for not having studied the already suggested solutions thoroughly. I remembered a very similar (but more general) question in the (Alas!) late libreofficeforum.org in 2015.
I made a demo first demontstrating how it can be done based on standard functions only. Then I show how a user function I wrote for my answer to the above mention question (and partly just out of interest) can be used. The last example also applies the TEXTJOIN() function only available in LibO 5.4.4 or higher in the needed version. Users trying it with AOO will only lose one variant.
As often in similar cases I wrote a function in Basic, because I wanted to understand the design instead of looking for a readymade thing. That's my way, and others may prefer different ways.
Everything you may want to vary can be varied. Of course, sheetsize and efficiency may set limits.
See demo:
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 2:39 pm
by karolus
Hallo
Version which produce Combinations from
selected Range in one Column.
Code: Select all
from itertools import combinations
def combine(*_):
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet
sourcerange = doc.CurrentSelection
data = list(zip(*sourcerange.DataArray))[0]
cursor = sheet.createCursorByRange(sourcerange)
for k in range(2,4): # 2 … 3
kombis = tuple((', '.join(kombi),) for kombi in combinations(data, k))
cursor.collapseToSize(1, len(kombis))
cursor.gotoOffset(1,0)
cursor.setDataArray(kombis)
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 4:10 pm
by Tanjamuse
I tried the latest document (combinations2) but I got the error shown in the screenshot below.
- LibreOffice Calc Error.png (8.8 KiB) Viewed 4790 times
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 5:16 pm
by karolus
Yes, obviously you have to select 3 or more cells near each other in Column A before you run the Code.
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 5:33 pm
by Tanjamuse
Thanks, I didn't know I had to select them all. Can I move the button? And how do I add it to another calc?
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 6:34 pm
by Villeroy
Tanjamuse wrote:Thanks, I didn't know I had to select them all. Can I move the button? And how do I add it to another calc?
menu:View>Toolbars>"Form Design" or "Form Controls". Button #2 turns on design mode. In this mode the button is just an ordinary object which can be copied, moved, resized or removed. You may also drop the button and call the macro in other ways.
With that spreadsheet I would proceed as follows:
menu:File>Templates>Save... [save under some name]
Now you can create a new spreadsheet with button and macro via menu:File>New>From Template [Ctrl+Shift+N]
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 7:21 pm
by Tanjamuse
How do I remove the space after the comma between the words?
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 8:07 pm
by Villeroy
Remove the space in ', '.join(kombi)
The string within the single quotes is the delimiter.
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 8:15 pm
by Tanjamuse
I thought I had done that. Is there somewhere I have to save it or something else because it still leaves space between the comma and the next word.
Code: Select all
from itertools import combinations
def combine():
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.Sheets[0] # first Sheet
#sheet = doc.Sheets.getByIndex(0) #for ApacheOpenOffice
sourcerange = sheet.getCellRangeByName("A1:A6")
data = list(zip(*sourcerange.DataArray))[0]
cursor = sheet.createCursorByRange(sourcerange)
for k in range(2,4): # 2 … 3
kombis = tuple((','.join(kombi),) for kombi in combinations(data,k))
cursor.collapseToSize(1,len(kombis))
cursor.gotoOffset(1,0)
cursor.setDataArray(kombis) from itertools import combinations
def combine():
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.Sheets[0] # first Sheet
#sheet = doc.Sheets.getByIndex(0) #for ApacheOpenOffice
sourcerange = sheet.getCellRangeByName("A1:A6")
data = list(zip(*sourcerange.DataArray))[0]
cursor = sheet.createCursorByRange(sourcerange)
for k in range(2,4): # 2 … 3
kombis = tuple((','.join(kombi),) for kombi in combinations(data,k))
cursor.collapseToSize(1,len(kombis))
cursor.gotoOffset(1,0)
cursor.setDataArray(kombis)
Sub Main
End Sub
Sub Main
End Sub
Re: Combine all possible combinations from single column
Posted: Wed Jun 12, 2019 8:30 pm
by Villeroy
The code that is triggered by the button is embedded in the document. The document is a zip file with a Scripts/python/ folder.
If you want to use the code in your macro folder, forget my template suggestion and use that code with any other spreadsheet document.
Re: Combine all possible combinations from single column
Posted: Thu Jun 13, 2019 2:27 am
by karolus
Hallo
for organizing python.Scripts install
APSO
@Button ect.:
→View→Toolbars→Form Controls and switch via Button
«Toggle Designmode»
Re: Solved! Combine all possible combinations from single co
Posted: Thu Jun 13, 2019 7:44 am
by Tanjamuse
Thanks to those who helped.