Understanding Excel lambda

Question

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.

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

I think too much unwanted verbose functions. And it would not return the as expected. — Mayukh Bhattacharya, Apr 05 '23 at 00:42
That formula which given to you will never return the data as concatenated for the invoices. based on suplier — Mayukh Bhattacharya, Apr 05 '23 at 00:46

Mayukh Bhattacharya · Accepted Answer · 2023-04-18T13:29:30.287

You could use the following formula :

• 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

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

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

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

Re "*to make the above recursive just wrap within a `LAMBDA`*", are you sure that makes it *recursive*? A *recursive* `LAMBDA` is one which is self-referential and which iterates/loops. Wrapping a formula within a `LAMBDA` does not by definition make it recursive. — Jos Woolley, Apr 05 '23 at 04:30

VBasic2008 · Answer 2 · 2023-04-08T02:03:22.610

Creating a Lambda Function

The Formula

=LET(Data,A1:B10,Delimiter,"-",
    d,DROP(Data,1),di,TAKE(d,,-1),
    du,TAKE(d,,1),u,UNIQUE(du),
    ui,BYROW(u,LAMBDA(r,
        TEXTJOIN(Delimiter,,FILTER(di,du=r,"")))),
VSTACK(TAKE(Data,1),HSTACK(u,ui)))

The LAMBDA Formula

You usually don't use this format.

=LAMBDA(Data,Delimiter,LET(
    d,DROP(Data,1),di,TAKE(d,,-1),
    du,TAKE(d,,1),u,UNIQUE(du),
    ui,BYROW(u,LAMBDA(r,
        TEXTJOIN(Delimiter,,FILTER(di,du=r,"")))),
VSTACK(TAKE(Data,1),HSTACK(u,ui))))(A1:B10,"-")

The LAMBDA Function

This one you will put in the name manager and call it e.g. JoinInv and use it with
```
=JoinInv(A1:B10,"-")
```

=LAMBDA(Data,Delimiter,LET(
    d,DROP(Data,1),di,TAKE(d,,-1),
    du,TAKE(d,,1),u,UNIQUE(du),
    ui,BYROW(u,LAMBDA(r,
        TEXTJOIN(Delimiter,,FILTER(di,du=r,"")))),
VSTACK(TAKE(Data,1),HSTACK(u,ui))))

The Variables

d - A2:B10 - data without headers
di - B2:B10 - the invoice data
du - A2:B10 - the supplier data
u - D3:D4 - a single-column array of the unique suppliers
ui - E3:E4 - the invoices joined into cells (rows) of a column
TAKE(Data,1) - A1:B1 - the headers

The flow

DROP and TAKE are used to reference the various parts of the range.
UNIQUE is used to get unique suppliers.
In the BYROW Lambda helper function, the invoice data is filtered by the suppliers' data column values being equal to the unique value in the current row and it is TEXTJOIN-ed to get a single string (per row). In the end, we get the joined invoices in as many rows as there are unique suppliers.
HSTACK is used to stack the joined invoices to the unique suppliers, while VSTACK is used to stack this resulting column duo to the headers.

score 0 · Answer 3 · answered Apr 13 '23 at 07:07

0

Here is my output..

=LET(data,A3:A11,result,B3:B11,unq,UNIQUE(data),VSTACK(A2:B2,HSTACK(unq,MAP(unq,LAMBDA(val,TEXTJOIN(",",,IF(val=data,result,"")))))))

answered Apr 13 '23 at 07:07

Manoj

411
1
8

Understanding Excel lambda

3 Answers3

Creating a Lambda Function