excellambdaexcel-formulaoffice365excel-lambda

Understanding Excel lambda

I am trying to understand better how does the recursivity works in Excel using the Lambda function. I didn't manage to wrap my head around how to increment the "value" the function is at.

The idea here is to stack all the unique "Invoices" value within one cell with a "-" as a delimiter as highlighted in blue in the screenshot.

enter image description here

l_uf = LAMBDA(array,criteria,
    UNIQUE(FILTER(CHOOSECOLS(array,2),CHOOSECOLS(array,1)=criteria,"")));

l_ar = lambda(array, criteria,
        let(v, l_uf(array, criteria),
            n, counta(v),
            if(n="","",
            CHOOSEROWS(v, n) & "-" &CHOOSEROWS(v, n-1))));

and then I tried the lambda function l_ar which only retries the 2 last rows without going through the all array... please help

Thank you very much

Solution

  • You could use the following formula :

    enter image description here

    • Formula used in cell E2

    =VSTACK({"Suplier","Invoices"},
DROP(REDUCE("",UNIQUE(B3:B12),
LAMBDA(x,y,VSTACK(x,HSTACK(y,
TEXTJOIN("-",1,UNIQUE(FILTER(C3:C12,B3:B12=y))))))),1))

    In a more basic and understandable way could be

    enter image description here

    • Formula used in cell E11

    =UNIQUE(TOCOL(B:B,1))

    • Formula used in cell F11

    =TEXTJOIN("-",1,UNIQUE(FILTER(C:C,E11=B:B)))

    The above formula obviously needs to be filled down.

    If you are eager to use LAMBDA() and want to spill the whole array, then you could use it in this way as well,

    enter image description here

    • Formula used in cell J2

    =LET(_uniqueSup,UNIQUE(TOCOL(B:B,1)),
HSTACK(_uniqueSup,BYROW(_uniqueSup,LAMBDA(x,TEXTJOIN("-",1,UNIQUE(FILTER(C:C,x=B:B)))))))

    Using LET() and defining each steps as variables:

    enter image description here

    =LET(_data,B2:C12,
_headers,TAKE(_data,1),
_suplier,DROP(_data,1,-1),
_usuplier,UNIQUE(_suplier),
_invoice,DROP(_data,1,1),
_invoicedelimited,BYROW(_usuplier,LAMBDA(u,TEXTJOIN("-",1,UNIQUE(FILTER(_invoice,_suplier=u))))),
_joindata,HSTACK(_usuplier,_invoicedelimited),
VSTACK(_headers,_joindata))

    To use the above within a LAMBDA() which you can use in defined name manager with a friendly name:

    =LAMBDA(array,
LET(_data,array,
_headers,TAKE(_data,1),
_suplier,DROP(_data,1,-1),
_usuplier,UNIQUE(_suplier),
_invoice,DROP(_data,1,1),
_invoicedelimited,BYROW(_usuplier,LAMBDA(u,TEXTJOIN("-",1,
UNIQUE(FILTER(_invoice,_suplier=u))))),
_joindata,HSTACK(_usuplier,_invoicedelimited),
VSTACK(_headers,_joindata)))(B2:C12)