[Solved] Find all possible combinations of textcells
[Solved] Find all possible combinations of textcells
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.. )
Thanks in advance!
Kind regards,
Fred Schnitt
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.. )
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
Re: find all possible combinations of textcells
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?
Do you really want to deal with all that, or is there something important I'm missing?
AOO4/LO5 • Linux • Fedora 23
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: find all possible combinations of textcells
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).
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).
Re: find all possible combinations of textcells
@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?
@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
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: [Solved] Find all possible combinations of textcells
The attachment has 4 sheets:
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.
- 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.
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).
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).
Re: [Solved] Find all possible combinations of textcells
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 lotMrProgrammer wrote:The attachment has 4 sheets: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.
- 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.
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.
Openoffice 4.1.3 on windows 10
Re: [Solved] Find all possible combinations of textcells
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.
If nothing else, you should be running into a memory problem.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: [Solved] Find all possible combinations of textcells
Correct. That would be 800*799=639200, if I'm not mistaken...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.
...
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
Re: [Solved] Find all possible combinations of textcells
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
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
Re: [Solved] Find all possible combinations of textcells
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.
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
---
Lupp from München
Re: [Solved] Find all possible combinations of textcells
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
thanks
- Attachments
-
- SAMPLE PERM 800CELLS-20ROW-40COL.ods
- (17.82 KiB) Downloaded 232 times
Openoffice 4.1.3 on windows 10
Re: [Solved] Find all possible combinations of textcells
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.
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
---
Lupp from München
Re: [Solved] Find all possible combinations of textcells
Hi Lupp, thanks very much, this nailed it. My very best regards to you and yours.
Openoffice 4.1.3 on windows 10
Re: [Solved] Find all possible combinations of textcells
Right. Just proves I cannot subtract!.keme wrote:Correct. That would be 800*799=639200, if I'm not mistaken...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.
...
Dividing by (800-2)! eliminates all factors in 800! up to 798, which leaves 799 and 800.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: [Solved] Find all possible combinations of textcells
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 permutationsLupp 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.
I'm completely lost (again).
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: [Solved] Find all possible combinations of textcells
My question in return (worded as a guess) before I tried an answer/ solution was:
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.
Seems I guessed correctly, at least in the sense of the user who came back to this question four and a half years later.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. ...
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
---
Lupp from München
Re: [Solved] Find all possible combinations of textcells
which is trivial to do with a database after you transposed the rows into one column of a normalized table.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.
- 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Find all possible combinations of textcells
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 ...
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
---
Lupp from München
Re: [Solved] Find all possible combinations of textcells
So in essence the OP did not want the actual permutations.Lupp wrote:My question in return (worded as a guess) before I tried an answer/ solution was:Seems I guessed correctly, at least in the sense of the user who came back to this question four and a half years later.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. ...
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.
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
Re: [Solved] Find all possible combinations of textcells
I feel rather sure now that my guess
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):
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.
was correct so far.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,
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):
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.mphilip wrote:...what i really need to achieve is: I am looking for the common permutations in all rows.
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
---
Lupp from München
Re: [Solved] Find all possible combinations of textcells
...when using the right language. This is why I condsider SQL "more trivial" than spreadsheet formulas when it comes to cartesian products.Lupp wrote: Cartesian products are trivial in many ways fortunately. However ...
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice