[Solved] Find all possible combinations of textcells

Discuss the spreadsheet application
Post Reply
schnitt
Posts: 2
Joined: Tue Feb 19, 2013 5:31 pm

[Solved] Find all possible combinations of textcells

Post by schnitt »

Hi everyone,

I'm trying to do the following:

I have a sheet with in total about 120 cells with text, spread over 8 columns. Something like this:

------ a-----------------b-------------- c ---------------d
1---sdgdsg---------dfgfgd---------tretretr------sfdshksgdf
2---uhkdfg--------bhjghjg------vdshjfgdshj
3------------------------------------hjgdjss

Now i would like to generate all the possible combinations of 2 combined cells and preferably paste them in a separate sheet.
So for example 1a+1b; 1a+1c; etc. but also 1b +1a; and 1c+1a

Is there anyone who knows how to do this? (And who has some spare time left.. :D)

Thanks in advance!

Kind regards,

Fred Schnitt
Last edited by schnitt on Wed Feb 20, 2013 12:43 pm, edited 1 time in total.
OpenOffice.org 3.3.0 on Windows 7
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: find all possible combinations of textcells

Post by acknak »

Ok, so 120 items taken two at a time ... that's a bit over 14,000 different combinations--ok, permutations, I suppose.

Do you really want to deal with all that, or is there something important I'm missing?
AOO4/LO5 • Linux • Fedora 23
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: find all possible combinations of textcells

Post by MrProgrammer »

schnitt wrote:I have a sheet with in total about 120 cells with text, spread over 8 columns. Now i would like to generate all the possible combinations of 2 combined cells and preferably paste them in a separate sheet. So for example 1a+1b; 1a+1c; etc. but also 1b +1a; and 1c+1a
  • You have a 2-dimensional array of data so "all combinations" would be a 4-dimensional array. How would you want that arranged on the new sheet? Note that "all combinations" (I think you mean permutations) of 120 cells is 14280 values (120×119).
  • Your sample data is character, but you use "+" in your post. Do you mean concatenation instead of addition? For concatenation, the operator in Calc is "&", not "+".
  • Cells in spreadsheets are A1, B1, A2, B2, … not 1a, 1b, 2a, 2b. I think you should read Ten concepts that every Calc user should know.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
schnitt
Posts: 2
Joined: Tue Feb 19, 2013 5:31 pm

Re: find all possible combinations of textcells

Post by schnitt »

@acknak: i'm gonna deal with all that :(

@MrProgrammer:
The results all in one column would be fine. And you're right, I mean permutations. :? And also concatenation :?

Do you know how to do it?
OpenOffice.org 3.3.0 on Windows 7
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Find all possible combinations of textcells

Post by MrProgrammer »

The attachment has 4 sheets:
  • 15X8: The 120 words.
    120X1: Those words rearranged into a column, using formulas.
    7140X2: The first 500 rows of the combinations of 1 through 120 (generated from formulas) and the two permutations of the corresponding words in the column.
    14280X1: The first 500 rows of those permutations rearranged into a column, using formulas.
Just fill the formulas in the 500th row of the last two sheets down to rows 7140 and 14280 respectively. I stopped at row 500 so that the spreadsheet will be small enough to attach here. These formulas use the functions IF, INT, MOD, ROW, OFFSET, and some simple arithmetic. The Help menu explains all of them.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Attachments
201302191421.ods
(41.63 KiB) Downloaded 731 times
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
mphilip
Posts: 9
Joined: Tue Feb 21, 2017 6:23 pm

Re: [Solved] Find all possible combinations of textcells

Post by mphilip »

MrProgrammer wrote:The attachment has 4 sheets:
  • 15X8: The 120 words.
    120X1: Those words rearranged into a column, using formulas.
    7140X2: The first 500 rows of the combinations of 1 through 120 (generated from formulas) and the two permutations of the corresponding words in the column.
    14280X1: The first 500 rows of those permutations rearranged into a column, using formulas.
Just fill the formulas in the 500th row of the last two sheets down to rows 7140 and 14280 respectively. I stopped at row 500 so that the spreadsheet will be small enough to attach here. These formulas use the functions IF, INT, MOD, ROW, OFFSET, and some simple arithmetic. The Help menu explains all of them.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Hi, Thanks for your post, The attachment was also helpful for me, but am trying to increase the number of columns to 20, and the rows to 40. I tried editing the function but it doesnt work. Please assist me. Thanks a lot
Openoffice 4.1.3 on windows 10
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: [Solved] Find all possible combinations of textcells

Post by jrkrideau »

If I am reading this correctly you have 800 cells in your new data set. This means you should have 800!/(800-2)! permutations. I think this means that you should end up with 510,081,600 permutations. (Can someone check my math---I have not done anything much with combinations and permutations in years).

If nothing else, you should be running into a memory problem.
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [Solved] Find all possible combinations of textcells

Post by keme »

jrkrideau wrote:If I am reading this correctly you have 800 cells in your new data set. This means you should have 800!/(800-2)! permutations.
...
Correct. That would be 800*799=639200, if I'm not mistaken...
Dividing by (800-2)! eliminates all factors in 800! up to 798, which leaves 799 and 800.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
mphilip
Posts: 9
Joined: Tue Feb 21, 2017 6:23 pm

Re: [Solved] Find all possible combinations of textcells

Post by mphilip »

Yes, 800 cells. 20 in a row.
and 40 in a column.
what i really need to achieve is: I am looking for the common permutations in all rows.

thanks
Openoffice 4.1.3 on windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Find all possible combinations of textcells

Post by Lupp »

If I understand correctly the OQ (4.5 years ago!) was about all the concatenations of any two elements in one row, both orders if different,
list these concatenated pairs in a column,
and append the lists for additional rows. ...

As nearly always with questions of the kind the problem was not prescisely described, ...

Before I try an answer I would want to be sure what the actual problem is. Referring to an old, probably ambiguous problem may not be precise enough.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
mphilip
Posts: 9
Joined: Tue Feb 21, 2017 6:23 pm

Re: [Solved] Find all possible combinations of textcells

Post by mphilip »

Thanks for your response. find attache a sample file with 800 cells, 20 columns and 40 rows. the permutations of each line are shown at the extreme right columns.

thanks
Attachments
SAMPLE PERM 800CELLS-20ROW-40COL.ods
(17.82 KiB) Downloaded 232 times
Openoffice 4.1.3 on windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Find all possible combinations of textcells

Post by Lupp »

See attached example.
I personally would prefer to do this specific kind of task by user code: More direct, better to maintain/enhance/adapt.
The omission of repetitions e.g. may be complicated the formula way.
Attachments
aoo59779_1.ods
(124.97 KiB) Downloaded 267 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
mphilip
Posts: 9
Joined: Tue Feb 21, 2017 6:23 pm

Re: [Solved] Find all possible combinations of textcells

Post by mphilip »

Hi Lupp, thanks very much, this nailed it. My very best regards to you and yours.
Openoffice 4.1.3 on windows 10
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: [Solved] Find all possible combinations of textcells

Post by jrkrideau »

keme wrote:
jrkrideau wrote:If I am reading this correctly you have 800 cells in your new data set. This means you should have 800!/(800-2)! permutations.
...
Correct. That would be 800*799=639200, if I'm not mistaken...
Dividing by (800-2)! eliminates all factors in 800! up to 798, which leaves 799 and 800.
Right. Just proves I cannot subtract!.
LibreOffice 7.3.7. 2; Ubuntu 22.04
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: [Solved] Find all possible combinations of textcells

Post by jrkrideau »

Lupp wrote:See attached example.
I personally would prefer to do this specific kind of task by user code: More direct, better to maintain/enhance/adapt.
The omission of repetitions e.g. may be complicated the formula way.
Ah what exactly are you doing? I do not understand what file at all. I was originally under the impression that mphilip wanted all possible permutations but I don't see any permutations

I'm completely lost (again).
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Find all possible combinations of textcells

Post by Lupp »

My question in return (worded as a guess) before I tried an answer/ solution was:
Lupp wrote:If I understand correctly the OQ (4.5 years ago!) was about all the concatenations of any two elements in one row, both orders if different,
list these concatenated pairs in a column,
and append the lists for additional rows. ...
Seems I guessed correctly, at least in the sense of the user who came back to this question four and a half years later.

In long what I tried to say: The aim was to get the Cartesian product of a set of something given in a range of contiguous cells in a single row.
Let M={A,B,C} e.g. be that set then we might write M x M = {(A,A),(A,B),(A,C),(B,A),(B,B),(B,C),(C,A),(C,B),(C,C)}. This is "Set Speak".
The Cardinality of a Cartesian product is the product of the cardinalities of the constituting sets.
Reduced consumption of characters would lead to AA AB AC BA and so on: concatenations or some kind of "g-adic numbers".

If we start with {A,B,C} again, any of the ordered pairs listed above can be called a "permutation of two out of the three components of the original triple with repetion allowed" (It's drawing from the bag: "Statistic Speak"). Therefore you may calculate the 3*3 resulting the product way also as the "number of permutations of 2 out of 3 ..." which is returned by =PERMUTATIONA(3; 2) if taking the same element twice is allowed.

Its a simple thing allowing complicated wording so far. It is not so extremely simple if n and k (generailzations of the 3 and 2 above) are, say 30 and 7. And the rest of the row would not quite be able to keep the 21870000000 drawings. Nonetheless PERMUTATIONA(30; 7) is still simply 30^7.
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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Find all possible combinations of textcells

Post by Villeroy »

Lupp wrote:In long what I tried to say: The aim was to get the Cartesian product of a set of something given in a range of contiguous cells in a single row.
which is trivial to do with a database after you transposed the rows into one column of a normalized table.
Attachments
permutations.odb
(28.4 KiB) Downloaded 177 times
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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Find all possible combinations of textcells

Post by Lupp »

We only can solve trivial problems anyway. (At least this is a saying about mathematicians appreciated by R.P. Feynman.)

The one problem of second order for anyone of us is to find out in what way the problem of first order might turn out to be trivial for himself.
Another problem is to understand the solutions someone else judges to be trivial.

Cartesian products are trivial in many ways fortunately. However ...
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: [Solved] Find all possible combinations of textcells

Post by jrkrideau »

Lupp wrote:My question in return (worded as a guess) before I tried an answer/ solution was:
Lupp wrote:If I understand correctly the OQ (4.5 years ago!) was about all the concatenations of any two elements in one row, both orders if different,
list these concatenated pairs in a column,
and append the lists for additional rows. ...
Seems I guessed correctly, at least in the sense of the user who came back to this question four and a half years later.

In long what I tried to say: The aim was to get the Cartesian product of a set of something given in a range of contiguous cells in a single row.
Let M={A,B,C} e.g. be that set then we might write M x M = {(A,A),(A,B),(A,C),(B,A),(B,B),(B,C),(C,A),(C,B),(C,C)}. This is "Set Speak".
The Cardinality of a Cartesian product is the product of the cardinalities of the constituting sets.
Reduced consumption of characters would lead to AA AB AC BA and so on: concatenations or some kind of "g-adic numbers".

If we start with {A,B,C} again, any of the ordered pairs listed above can be called a "permutation of two out of the three components of the original triple with repetion allowed" (It's drawing from the bag: "Statistic Speak"). Therefore you may calculate the 3*3 resulting the product way also as the "number of permutations of 2 out of 3 ..." which is returned by =PERMUTATIONA(3; 2) if taking the same element twice is allowed.

Its a simple thing allowing complicated wording so far. It is not so extremely simple if n and k (generailzations of the 3 and 2 above) are, say 30 and 7. And the rest of the row would not quite be able to keep the 21870000000 drawings. Nonetheless PERMUTATIONA(30; 7) is still simply 30^7.
So in essence the OP did not want the actual permutations.

Okay, I was reading the original request as wanting the actual permutations
For example if we have a set of {a, b, c) then all possible combinations would be:
[1]]
[1] "a" "b" "c"

[[2]]
[1] "a" "c" "b"

[[3]]
[1] "c" "a" "b"

[[4]]
[1] "c" "b" "a"

[[5]]
[1] "b" "c" "a"

[[6]]
[1] "b" "a" "c"
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Find all possible combinations of textcells

Post by Lupp »

I feel rather sure now that my guess
Lupp wrote:If I understand correctly the OQ (4.5 years ago!) was about all the concatenations of any two elements in one row, both orders if different,
was correct so far.
Another issue is that the user reviving the old thread added in his second post as of 2017-07-13 16:28 (time zone unknown to me):
mphilip wrote:...what i really need to achieve is: I am looking for the common permutations in all rows.
and todate nobody (including me) took in account this explication. Regarding it now I would the more decisively advise to find the results with a program written in a general-purpose programming language.
I am still not completely sure if the target are the "common" pairs literally occuring "in all the rows" (most likely none) or those pairs occurring in at least two of them - and if identifiers for the respective rows should be included. Free programming can do all this easily.
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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Find all possible combinations of textcells

Post by Villeroy »

Lupp wrote: Cartesian products are trivial in many ways fortunately. However ...
...when using the right language. This is why I condsider SQL "more trivial" than spreadsheet formulas when it comes to cartesian products.
SELECT A.*, B.* FROM "Table" AS A, "Table" AS B
fetches the same table twice and returns the cartesian product of both tables. In this example with 800 values I split the table into 40 sets which gives

SELECT A.*, B.* FROM "Table" AS A, "Table" AS B WHERE A.SET_ID = B.SET_ID
which produces one cartesian product of 20x20 rows for each of the 40 sets. That is 16,000 rows returned by "Query1" in my database.
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
Post Reply