[Solved] Combine all combinations from single column

Creating a macro - Writing a Script - Using the API

[Solved] Combine all combinations from single column

Postby Tanjamuse » Wed Jun 12, 2019 9:25 am

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   Expand viewCollapse view
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
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Postby JeJe » Wed Jun 12, 2019 11:23 am

See attached document. (you'll need to check if it does give results you want)
Attachments
test.ods
(11.22 KiB) Downloaded 33 times
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 590
Joined: Wed Mar 09, 2016 2:40 pm

Re: Combine all possible combinations from single column

Postby karolus » Wed Jun 12, 2019 11:28 am

Hallo

pretty simple with python:

Code: Select all   Expand viewCollapse view
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 - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 852
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Postby Tanjamuse » Wed Jun 12, 2019 11:41 am

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
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Postby JeJe » Wed Jun 12, 2019 12:16 pm

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   Expand viewCollapse view
   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




Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 590
Joined: Wed Mar 09, 2016 2:40 pm

Re: Combine all possible combinations from single column

Postby karolus » Wed Jun 12, 2019 12:34 pm

Hallo

The python-Code is embedded in attached Document
combinations.ods
(10.28 KiB) Downloaded 39 times
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 852
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Postby RoryOF » Wed Jun 12, 2019 12:39 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29599
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Combine all possible combinations from single column

Postby Tanjamuse » Wed Jun 12, 2019 12:44 pm

Do I have to run java only from this site: https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.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
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Postby RoryOF » Wed Jun 12, 2019 12:52 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29599
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Combine all possible combinations from single column

Postby karolus » Wed Jun 12, 2019 12:57 pm

Tanjamuse wrote:Do I have to run java only from this site: https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.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 - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 852
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Postby Tanjamuse » Wed Jun 12, 2019 1:15 pm

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
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Postby Lupp » Wed Jun 12, 2019 2:15 pm

(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 36 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2538
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Combine all possible combinations from single column

Postby karolus » Wed Jun 12, 2019 2:39 pm

Hallo

Version which produce Combinations from selected Range in one Column.

Code: Select all   Expand viewCollapse view
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(datak))            
        
cursor.collapseToSize(1len(kombis))
        
cursor.gotoOffset(1,0)
        
cursor.setDataArray(kombis)
 
Attachments
combinations2.ods
(12.14 KiB) Downloaded 42 times
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 852
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Postby Tanjamuse » Wed Jun 12, 2019 4:10 pm

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 1033 times
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Postby karolus » Wed Jun 12, 2019 5:16 pm

Yes, obviously you have to select 3 or more cells near each other in Column A before you run the Code.
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 852
Joined: Sat Jul 02, 2011 9:47 am

Re: Combine all possible combinations from single column

Postby Tanjamuse » Wed Jun 12, 2019 5:33 pm

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
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Postby Villeroy » Wed Jun 12, 2019 6:34 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27236
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combine all possible combinations from single column

Postby Tanjamuse » Wed Jun 12, 2019 7:21 pm

How do I remove the space after the comma between the words?
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Postby Villeroy » Wed Jun 12, 2019 8:07 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27236
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combine all possible combinations from single column

Postby Tanjamuse » Wed Jun 12, 2019 8:15 pm

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   Expand viewCollapse view
    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
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am

Re: Combine all possible combinations from single column

Postby Villeroy » Wed Jun 12, 2019 8:30 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27236
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combine all possible combinations from single column

Postby karolus » Thu Jun 13, 2019 2:27 am

Hallo

for organizing python.Scripts install APSO

@Button ect.:
→View→Toolbars→Form Controls and switch via Button «Toggle Designmode»
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 852
Joined: Sat Jul 02, 2011 9:47 am

Re: Solved! Combine all possible combinations from single co

Postby Tanjamuse » Thu Jun 13, 2019 7:44 am

Thanks to those who helped.
OpenOffice Version: 6.1.2.1 (x64) on Windows 10
Tanjamuse
 
Posts: 9
Joined: Wed Jun 12, 2019 9:01 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests