Forum Discussion

Marco365's avatar
Marco365
Brass Contributor
Apr 29, 2024

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...
excel
Formulas and Functions
rachel's avatar
rachel
Iron Contributor
Apr 30, 2024
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)
Marco365's avatar
Marco365
Brass Contributor
Apr 30, 2024
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
    • djclements's avatar
      djclements
      Bronze Contributor
      Apr 30, 2024

      mathetes If you are interested, I had shared a couple versions of a generalized LAMBDA function a month ago, for generating permutations and combinations:

      1. https://stackoverflow.com/questions/78218953/finding-unique-combination-sets... 
      2. 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...

      • mathetes's avatar
        mathetes
        Silver Contributor
        Apr 30, 2024

        djclements 

         

        "If you are interested..."

         

        It IS interesting, the capabilities of Excel are more extensive than any one of us can conceive, I suspect.

         

        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. 

Resources