[Solved] Combine all combinations from single column

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

[Solved] Combine all combinations from single column

Post 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
Last edited by Tanjamuse on Thu Jun 13, 2019 7:43 am, edited 1 time in total.
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Combine all possible combinations from single column

Post by JeJe »

See attached document. (you'll need to check if it does give results you want)
Attachments
test.ods
(11.22 KiB) Downloaded 199 times
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Post 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 ;-)
Last edited by karolus on Wed Jun 12, 2019 11:46 am, edited 1 time in total.
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Post 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?
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Combine all possible combinations from single column

Post 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




Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Post by karolus »

Hallo

The python-Code is embedded in attached Document
combinations.ods
(10.28 KiB) Downloaded 220 times
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Combine all possible combinations from single column

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Post 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.
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Combine all possible combinations from single column

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Post by karolus »

Tanjamuse wrote: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.
Its a false bug, you dont need Java to run Basic or Python. click away this dumb messages and the →Makro→Execute- Dialog opens.
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Post 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.
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Combine all possible combinations from single column

Post 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:
Attachments
aoo98326generatingCombinations_1.ods
(78.31 KiB) Downloaded 176 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Post 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)
 
Attachments
combinations2.ods
(12.14 KiB) Downloaded 192 times
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Post by Tanjamuse »

I tried the latest document (combinations2) but I got the error shown in the screenshot below.
LibreOffice Calc Error.png
LibreOffice Calc Error.png (8.8 KiB) Viewed 4725 times
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Post by karolus »

Yes, obviously you have to select 3 or more cells near each other in Column A before you run the Code.
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Post 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?
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combine all possible combinations from single column

Post 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]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Post by Tanjamuse »

How do I remove the space after the comma between the words?
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combine all possible combinations from single column

Post by Villeroy »

Remove the space in ', '.join(kombi)
The string within the single quotes is the delimiter.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Post 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
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combine all possible combinations from single column

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Post by karolus »

Hallo

for organizing python.Scripts install APSO

@Button ect.:
→View→Toolbars→Form Controls and switch via Button «Toggle Designmode»
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Tanjamuse
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Solved! Combine all possible combinations from single co

Post by Tanjamuse »

Thanks to those who helped.
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
Post Reply