Relevant answer
Relevant answer
Share the file if you can..i will make the changes and send it to you..
Relevant answer
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)
Relevant answer
Justin Spall can you comment the google sheet link in the following comment and show the expected output. It looks like you need SUMPRODUCT() with an ARRAYFORMULA()
Relevant answer
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)
Relevant answer
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.
Relevant answer
=LET(x,UNIQUE(A2:A10),HSTACK(x,MAP(x,LAMBDA(lm,TEXTJOIN(" ",TRUE,FILTER(B2:B10,A2:A10=lm))))))
Relevant answer
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"(",""),")","")
=+REDUCE(A2:A9,{"-","(",")"},LAMBDA(a,aa,SUBSTITUTE(a,aa,"")))
Relevant answer
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)))))))
Relevant answer
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,))
Relevant answer
Raymond Hechanova hope your query is resolved now, please edit the post as #SOLVED
Relevant answer
=MAP($A$4:$A$10,LAMBDA(x,SUM(IFERROR(--TEXTAFTER(FILTER(TEXTSPLIT(x,,"|"),ISNUMBER(SEARCH(C$3,TEXTSPLIT(x,,"|")))),"qty"),0))))
Relevant answer
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)))))
Relevant answer
Hola Adrian primero que nada, esto es desde fórmulas o vba? Si es mediante formula usa =INDIRECTO() Avisa si aplica....
Relevant answer
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))),"")