LAMBDA Function

Relevant answer

Trinhxuanduong 1 year ago
  • Hỗ Trợ Vay Vốn Ngân Hàng Kết Bạn Zalo: 0359.078.354 để được tư vấn làm thủ tục
  • Thủ tục vay tín chấp nhanh gọn giải ngân trong ngày
  • Khoản Vay: 20-500 triệu
  • Lãi Xuất Cực Kì Ưu Đãi (0,8%)
  • Kỳ Hạn Vay: 12 - 18 - 24 - 36 tháng.
  • Giải Ngân Nhanh Gọn Trong 2 Tiếng Nếu Đủ Điều Kiện
  • Không Thẩm Định + Không Cần Chứng Minh Thu Nhập + Vay qua ngân hàng toàn quốc làm Việc Cả T7 Và CN.
  • Thông tin bảo mật khách hàng - Uy Tín -Nhận Và Thanh Toán ATM Nhắn tin ZALO hoặc Call ngay để được tư vấn (2)
0 Likes

Relevant answer

Mayukh Bhattacharya 1 year ago

Jackson Mark here are three approaches; you can use either of the one. With VSTACK() & LAMBDA() it is not required for your query, however if you have larger range then you can find the solution with dynamic spill array in the link below I have already posted this.

https://stackoverflow.com/questions/74181973/is-there-a-non-vba-excel-spilling-formula-to-create-and-process-arrays-of-arrays

• Formula used in cell B4

=EXPAND(B2,B1,,B2)

Or,

• Formula used in cell B8

=XLOOKUP(SEQUENCE(B1),SCAN(0,B1,LAMBDA(a,b,a+b)),B2,,1)

Or,

• Formula used in cell B12

=TEXTSPLIT(REPT(B2&"|",B1),,"|",1)

3 Likes

Relevant answer

Mayukh Bhattacharya 1 year ago

Thomas Dejong also you could use the following formulas where you don't have to drag down or add another formula for the length, this works with MS365. It is one single dynamic array formula it #SPILLS!

• Formula used in cell B2

=DROP(REDUCE("",A2:A8,LAMBDA(x,y,VSTACK(x,LET(a,TEXTJOIN("-",,TEXTSPLIT(y,{"kp ","-kp "})),b,TEXTAFTER(a,"-")-TEXTBEFORE(a,"-"),HSTACK(a,b))))),1)

3 Likes

Relevant answer

Brad Smyrk 1 year ago

alternative, use Text to Columns and select "Delimited" then "Next", then select the delimiters you want to split by, for example if you want to retain the "." but split by spaces, select space as shown.

3 Likes

Relevant answer

NaBeel GonDal 1 year ago

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"(",""),")","")

=+REDUCE(A2:A9,{"-","(",")"},LAMBDA(a,aa,SUBSTITUTE(a,aa,"")))

1 Like

Relevant answer

Maddy B 1 year ago

try this out in Cell B3 of you Master Data sheet:

=makearray(counta(A3:A),counta(B2:M2),lambda(r,c,ifna(sum(filter('Action Monitoring'!E:E,'Action Monitoring'!C:C=index(A3:A,r),'Action Monitoring'!G:G=index(B2:M2,,c),regexmatch('Action Monitoring'!D:D,textjoin("|",1,'Data Validation'!A3:A13)))))))

0 Likes

Relevant answer

Scott Lu 1 year ago

I2=IFERROR(SMALL(D:D,1+SUMPRODUCT(ISNUMBER(MATCH($D$2:$D$20,$I$1:I1,))*1)),"") J2=IF(OR($I2="",COUNTIF($D:$D,$I2)<COLUMN(A1)),"",OFFSET($E$1,MATCH($I2,$D$2:$D$300,)+COLUMN(A1)-1,))

2 Likes

Relevant answer

Mayukh Bhattacharya 1 year ago

Geir J Rasmussen can you show us a screenshot where is not returning the counts for the even number with same formula except when its even we ae using =0, as it is working on my end, if you think its wrong can you elaborate more on it

• Formula used in cell I17

=SUM((MOD(B2:E16,2)=0)+0)

Or,

• Formula used in cell J17

=SUM(MAP(B2:E16,LAMBDA(m,N(ISEVEN(m)))))

0 Likes

Relevant answer

Samir Shaker 1 year ago

For below <50 =IFERROR(ARRAYTOTEXT(FILTER(C$3:C$11,($B$3:$B$11=$F4)*($D$3:$D$11<50))),"")

For Above >50 =IFERROR(ARRAYTOTEXT(FILTER(C$3:C$11,($B$3:$B$11=$F4)*($D$3:$D$11>50))),"")

2 Likes

Hot network questions