Forum Discussion
Generate sets of five numbers base on the given numbers from 5 different columns
I color them just to show that the numbers stay in the same column, and still are in ascending order. Columns H to L or just N are a few examples of combinations I did manually to show the res...
I'm going to have to noodle and doodle on this a bit.
Do you really expect every possible combination of five digits, using just one from each column? And how many rows are we needing to accommodate? If numbers can be entered randomly in the various columns, is it permissible to have duplicates in any of the combinations? Etc.
In the meantime, can you tell me (and anybody else looking over our shoulders) what the context is, the "bigger picture," for this request. That, in addition to your re-written instructions, may help in the understanding.
And, for anybody looking over our shoulders, this thread actually is a continuation of this one :
I think he just wants to do Cartesian product five times. eg
col_12 = torow(col1 & transpose(col2))
col_123 = torow(col_12 & col_3)
col_1234 =torow(col_123 & col_4)
col_12345 = torow(col_1234 & col_5)
col_12 = torow(col1 & transpose(col2))
col_123 = torow(col_12 & col_3)
col_1234 =torow(col_123 & col_4)
col_12345 = torow(col_1234 & col_5)
- Hello Rachel, thank you for taking time to help me. I am hoping to take a bootcamp this year and excel is part of it. As of now, I am not sure how to apply this. I did attach a file and I am not sure if this can apply to the sheet. I am going to google the keyword "cartesian" to see what I can learn. Thanks
To anybody and everybody tuning in on this thread, let me refer you back to the original, where rachel has resolved the matter to the satisfaction of Marco365
Do NOT take any time to work toward resolution here.
mathetes If you are interested, I had shared a couple versions of a generalized LAMBDA function a month ago, for generating permutations and combinations:
- https://stackoverflow.com/questions/78218953/finding-unique-combination-sets...
- https://techcommunity.microsoft.com/t5/excel/creating-table-from-2-tables...
The first version would work well in this case, with a slight modification to ignore blanks:
PERMA: =LAMBDA(array1,[array2],[ignore_blanks], IF( ISOMITTED(array2), REDUCE( TOCOL(CHOOSECOLS(array1, 1), ignore_blanks), SEQUENCE(COLUMNS(array1)-1,, 2), LAMBDA(v,n, PERMA(v, TOCOL(CHOOSECOLS(array1, n), ignore_blanks))) ), LET( a, SEQUENCE(ROWS(array1)), b, SEQUENCE(, ROWS(array2)), HSTACK( CHOOSEROWS(array1, TOCOL(IF(b, a))), CHOOSEROWS(array2, TOCOL(IF(a, b))) ) ) ) )Once defined in Name Manager, the PERMA function can then be used to generate the desired output as follows:
=PERMA(A1:E7,, TRUE)See attached...
