how do make List's and then get all possible combinations from 2 or 3 list's containing 3 or 4 items

Formular for making list’s to obtain all non repeating combination (ie List 1 has A,B, C List 2 has D,E,F,G and list 3 has I, J) thanks

Hello Opaque, thank you for your time, “List” abc in separate cells in column A, list def in separate cells in column B and so on, result of combination in single cell (ie) a d g or if its easier in each combination in a single row but multiple columns.
Ideally, i am looking for 10 Lists with a combinations of 5 items ( I believe a total combination of 252 possibilities.)
Once again thank you for your help and time in helping a novice.

My combinatorics tells me there are 3 * 4 * 2 = 24 (3-tuples) combinations if I draw one from list 1 (3 elements A,B,C) , one from list 2 (4 elements D,E,F,G) and one from list 3 (2 elements I,J). Allowing permutations of each 3-tuple adds another factor 6 so we have 24 * 6 = 144 combinations → obviously I still seem not to understand the task.

Hello Opaque, Say I am looking for 5 lottery no’s from 10 sets of 3 to 4 numbers (ie list 1 set 1 a,b,c in column A then in column B list 2 set 2 eyc etc till you have 10 columns with 10 list and 10 sets.
A B C cell M = possible combination of 5 from 10
a d i
b e j
c f m g
imagine that’s the work sheet and all possible combinations come out in column M row 1 of 3 ie a,d,i
Column M row 2 a,d,j and so on.
Thanks again

I give up here … sorry, can’t help.

It will make things clearer if you indicate, with two limited lists, what the desired output is.
In the meantime python offers various function to make the product, combinations and permutations of lists. F.i.

itertools import combinations # or import permutations
seq = combinations ( [1 ,3,‘A’,‘B’ ], 2 )
for item in seq :
print ( item , end =" ")

will give you (1, 3) (1, ‘A’) (1, ‘B’) (3, ‘A’) (3, ‘B’) (‘A’, ‘B’)
For that result it would be rewarding to invest in python python website. Is LO-calc the right application for your task.

Hello Opaque,no problem thanks for your time.
Thanks Parsely, will look into Python

A relational database combines row sets. Most simple example: [SQL] JOINing 2 row sets (View topic) • Apache OpenOffice Community Forum

…or (if you are still afraid of Base and SQL and therefore want to use Calc and its formulas) try the "Combinatorics formulas" extension.

Anything is easier than a macro driven spreadsheet, even a Perl program or a shell script.

You know that. I know that too. Some more know. The rest are afraid. For them, the word “database” is shrouded in the halo of High Science. Who made the effort to intimidate? Mega-corporation?

Good question. I think, spreadsheet users run into the “instant reward trap”. You open this application for the first time, see the empty grid, enter data and instantly you get an idea what to do with such a grid where you can enter anything anywhere. It takes a lot of time and effort until you recognize that 80% of the application serves a completely different purpose. It is all about arithmetic. A desk calculator on steroids. During the 90ies, the Microsoft Excel developers added some features to meet the expectations of the vast majority of “intuitive users” (more text functions, pivot tables, filters, sorting, everything you find in Calc’s Data menu). But even these features assume that the user stores a normalized list on the grid. Intuitive userrs don’t do that. They store data in cross tables and/or across multiple sheets. Even if you and me build a simple ad-hoc database draft on spreadsheets, we have database normalization rules in mind and the concept of separating data, processing and representation as much as possible. Intuitive users mix data and formulas in cross tables and apply hard formatting in place. Then they ask how to query the red cells in column X on sheet Y.

1 Like