Generate sets of five numbers base on the given numbers from 5 different columns

Question

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 results I am looking for.

Columns A to E, the given numbers, should generate all the possible combinations as you see in H to L or just N.

I do not want to mix the columns like in P1

Let me know if you have any questions.

I attach the excel file

Thank you for your help

rachel · Answer

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)

mathetes · Answer

Marco365&nbsp;rachel&nbsp;&nbsp;To anybody and everybody tuning in on this thread, let me refer you back to the original, where&nbsp;rachel&nbsp;has resolved the matter to the satisfaction of&nbsp;Marco365&nbsp;&nbsp;Do NOT take any time to work toward resolution here.

djclements · Answer

mathetes&nbsp;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...&nbsp;https://techcommunity.microsoft.com/t5/excel/creating-table-from-2-tables...&nbsp;The first version would work well in this case, with a slight modification to ignore blanks:&nbsp;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)))
            )
        )
    )
)&nbsp;Once defined in Name Manager, the PERMA function can then be used to generate the desired output as follows:&nbsp;=PERMA(A1:E7,, TRUE)&nbsp;See attached...

mathetes · Answer

djclements&nbsp;&nbsp;"If you are interested..."&nbsp;It IS interesting, the capabilities of Excel are more extensive than any one of us can conceive, I suspect.&nbsp;In this case, this particular capability, while theoretically interesting, is one that I have a difficult time imagining ever actually encountering a practical application. I'm filing it away in my outstanding solutions folder--thank you!--but mostly as a curiosity.&nbsp;

mathetes · Answer

Marco365&nbsp;&nbsp;I'm going to have to noodle and doodle on this a bit.&nbsp;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.&nbsp;&nbsp;In the meantime, can you tell me (and anybody else looking over our shoulders) what the context is,&nbsp;the "bigger picture,"&nbsp;for this request. That, in addition to your re-written instructions, may help in the understanding.&nbsp;And, for anybody looking over our shoulders, this thread actually is a continuation of this one&nbsp;:&nbsp;

Forum Discussion

Generate sets of five numbers base on the given numbers from 5 different columns

7 Replies

Resources