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

Brass Contributor

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 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

7 Replies

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

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 :

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

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)

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

@mathetes Thank you for all your hard work.

I think bringing the old post could bring confusion to others. I tried to be as clear and visual as I can on this post in order to not bring confusion.

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

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

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

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.

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

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

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...

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

"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.