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.

 

[st001]

This is a "Cartesian Product" - very useful in lots of applications.
I think Xlambda did something similar a while ago (see below), but your function looks a little shorter.... but I don't know the ins and outs and fine intricacies.
Good work though!

ACOMBINE = LAMBDA(a, cl,
    LET(
        y, INDEX(a, SEQUENCE(ROWS(a)), IF(AND(cl), cl, SEQUENCE(, COLUMNS(a)))),
        u, AUNIQUE(y, -1),
        v, TRANSPOSE(AUNQSRT(TRANSPOSE(u), 1)),
        r, ROWS(v),
        c, COLUMNS(v),
        s, SEQUENCE(, c),
        x, MOD(ROUNDUP(SEQUENCE(r ^ c) / r ^ ABS(s - c), 0) - 1, r) * c + s,
        ACLEAN(AXLOOKUP(x, IF(v = "", 0, SEQUENCE(r, c)), v, , , ), 1)
    )
);

 

[bines53]

Hi @ gifariz

I just started learning about the Lambda function, can you please give a solution, without the Let function?

Thank you!

 

[gifariz]

This is a "Cartesian Product" - very useful in lots of applications.
I think Xlambda did something similar a while ago (see below), but your function looks a little shorter.... but I don't know the ins and outs and fine intricacies.
Good work though!

I see, thank you for the terminology. Let me see if I can add it in my post's description, I can't do it in my phone.

Yeah right, I missed to include "combine" keywords when I was searching if this function had been made. It seems xlambda's formula uses many of his own functions. Maybe his has more general use and error handler.

And thanks for showing that writing formula in coding style looks much better.

 

[gifariz]

Hi @ gifariz

I just started learning about the Lambda function, can you please give a solution, without the Let function?

Thank you!

Hi. Here is without LET

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

Not sure if it works, I didn't test it.
Note that without LET, it will perform COUNTA so many times thus will be slower for large data.

 

[bines53]

Hi gifariz,

I have another request, I like the structure of the formula, if possible the formula, also without Lambda, only with Makearray, thank you very much!

 

[bines53]

Hi gifariz,

The formula really doesn't work without Let.
In fact, what you are saying is that a simpler formula with more function of counta, is less effective than a more complex formula that has the function Let.

 

[Xlambda]

This is a "Cartesian Product" - very useful in lots of applications.
I think Xlambda did something similar a while ago (see below), but your function looks a little shorter.... but I don't know the ins and outs and fine intricacies.

Thanks for mentioning me !! ACOMBINE deals with columns of vectors that have dups , the functions "flow" at that time was the integration with recursive ASUMIFS and ACOUNTIFS, functions that can handle arrays calculations., complicated..
Anyhow, you both missed ACOMB. No problem at all. Nobody has to read anything. We need fresh ideas. This is the function that led to the entire ARRANGEMENTS study,
And ACOMB does not call any other custom-made functions or any of the new lambda helper functions that can slow things down, is quite fast and efficient for up to 1M rows of combined data, calculates patterns array in "bulk", no iterative algorithms triggered, and can deal with an array of column vectors (more than 2), combining all their elements.

 

[Xlambda]

Not sure if it works, I didn't test it.
Note that without LET, it will perform COUNTA so many times thus will be slower for large data.

This is cool, because you realized that the function will be slow for large data, but the reason for that is MAKEARRAY itself, with or without LET.
Your function is designed for 2 vectors only, (not 2D arrays) and for that you do not need MAKEARRAY at all.
New lambda helper functions are kind of iterative algorithms that should be used only when there is no other option. (if you care about efficiency)
An alternative, no MAKEARRAY, for 2 vectors:
COMB2V(va,vb) Combine 2 Vectors

=LAMBDA(va,vb,LET(r,COUNTA(va),c,COUNTA(vb),s,SEQUENCE(r*c),q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,IF({1,0},INDEX(va,q),INDEX(vb,m))))

The Big O using your function for 2 vectors, n elements each will be O(2n^2) => O(n^2) vs O() for COMB2V, will be O(2)
Simple example: define v=sequence(1000) Calc time for MAKECOMBO(v,v) is over 30 seconds, calc time for COMB2V(v,v) or ACOMB(HSTACK(v,v)) => instant (less than 1s)
Hope that helps, and sorry for my comments, I felt like I was somehow "invited". Keep creating functions. Does not matter if a function was previously done or not.

 

[bines53]

Hi Xlambda,

Sorry for my English, not my native language, I don't know if you noticed, in the Makearray function, in the last part, you can use (x,y,x) or (x,y,y), and together (x,y,x&y), =Tocol(MAKEARRAY(rang(A),rang( B),LAMBDA(x,y,x&y))),or =Tocol(MAKEARRAY(rang(A),rang( B),LAMBDA(x,y,x))),,1) and so.