MAKECOMBO

[gifariz]

MAKECOMBO

Function: Returns combination of two arrays

=LAMBDA(array1,array2,LET(a,array1,b,array2,La,COUNTA(a),Lb,COUNTA(b),MAKEARRAY(La*Lb,2,LAMBDA(r,c,IF(c=1,INDEX(a,INT((r-1)/Lb)+1),INDEX(b,MOD(r-1,Lb)+1))))))

Here is example (attached image)

This is my first lambda function posted here. Kindly please give your suggestions. Thanks.
I use "make" word in the function name to avoid confusion with Combination only that usually refer to number of combinations of two groups in Mathematics.

 

[bines53]

Hi Xlambda,

Wow !!, you can test without the function mid?

 

[Xlambda]

Yes, COMB2V.
To improve yours, as it is, there are 2 ways, using textjoin to append, but then, instead of MID we need a custom made function like ATEXTSPLIT capable of splitting into arrays, or, to use TEXTSPLIT with a nested MAKEARRAY that will make the whole assembly extremely slow. Sorry

 

[Xlambda]

Hi Xlambda,

Wow !!, you can test without the function mid?

I really must go now. Has been a pleasure !! Take care !!

 

[bines53]

Thank you, I'm starting to believe only in you

 

[bines53]

Hi Xlambda,

=INDEX(a1:b4,HSTACK(TOCOL(COUNTIF(b1:b4,b1:b4)+SEQUENCE(,COUNTA(a1:a3),0),,1),TOCOL(COUNTIF(a1:a3,a1:a3)+SEQUENCE(,COUNTA(b1:b4),0))),{1,2})

This time there is no problem with characters, I think the formula is quite fast, maybe there will be a problem with the function countif, it is worth examining the performance.

 

[gifariz]

I do not know if "slow" is the right, fair word for it. I think I found a better way to describe it.
Imagine the elevators in a super tall skyscraper building. They are all the same make, they use the same infrastructure of rails, mechanics, and electronics.
Some of them, the regular ones, can be programed to access any floor, and some of them can be programmed to acces every other 20 floors, the express ones,
When they are moving, they are equally fast.
If the only task is to get to the 20th floor, take the express, if you take the regular you risk stopping on each floor even if no information is getting in, no information is getting out.
Waste of resources, looks and feels slow.
At the end of the day, the regular ones (lambda helpers), when really needed, can carry more complex information for complex tasks, outperforming the express ones.

Speed test results are very contextual. It is not complicated to construct your own tests. The only thing you have to consider is to compare binary with binary, exact with exact.
INDEX-MATCH binary can be lot faster than XLOOKUP exact. Golden rule: Whenever you can sort always use binary lookups of any function you like.

Personal thing, I felt sorry for the quotient function that nobody uses it. MOD and QUOTIENT are brothers, they have same parents (identical arguments). In my functions/formulas they will never be separated.

As I said before, you do not have to find or follow anything. This new Excel ecosystem is new to all of us. It is too early to develop any stereotypes. Have an idea you believe in, go for it.
Think of it as a library. You do not need to read any books to write your own novel.

Regarding the function, I am planning to explain better the concept behind ACOMB and all combinatorics calculations, on future posts, but probably not here.
There is a simple formula/function that is the main core of solving any combinatorics problem, permutations, combinations, combinations of n vectors...etc
fn(n,c)=LAMBDA(n,c,MOD(ROUNDUP(SEQUENCE(n^c)/n^(c-SEQUENCE(,c)),0)-1,n)+1)

Thank you for your explanation. These fundamentals are very impactful for my future spreadsheet developments.
Looking forward to your explanation of the formula for combinatorial problems and mathematics behind it. It looks important and expandable to so many practical uses.

 

[bines53]

Hi gifariz,

your formula without lambda and Let,

=MAKEARRAY(conta(A)*counta(B),2,LAMBDA( r,c,IF(c=1,INDEX(a1:a3,INT(r-1)/counta( B)+1),INDEX(b1:b4,MOD(r-1,counta(B))+1)))).

You were the inspiration to try and understand the function makearray, I appreciate that it has a lot of depth.

My correction to the previous formula,
=TOCOL(MAKEARRAY(counta(a),counta(b),LAMBDA(x,y,INDEX(a1:a3,x)&";"&INDEX(b1:b4,y)))).
The solution in one column.

 

[Xlambda]

Thank you for your explanation. These fundamentals are very impactful for my future spreadsheet developments.
Looking forward to your explanation of the formula for combinatorial problems and mathematics behind it. It looks important and expandable to so many practical uses.

You are very welcome!! Check this out ACOMB