Relevant answer
Công thức cho L7: Kết thúc nhấn Ctrl+Shift+Enter =MAX(IF(Table1[NGÀY]>=$L$4,INDEX(Table1[[SMC]:[HDB]],,MATCH([@[Mã CK]],Table1[[#Headers],[SMC]:[HDB]],0))))
Bôi đen cột đó, nhấn Ctrl+D, thay vì kéo xuống như bt
Relevant answer
Hi Biplab, the filter function will fetch results from BOM but it wont insert new rows and also the number of lines are more in process orders tab. Will power Query or as you mentioned Advance filter copy can help?
Relevant answer
EJ Uyuyoy Malinao one other way of resolving this is
• Formula used in cell B6
=INDEX(B$2:B$4,AGGREGATE(15,6,(ROW($A$2:$A$4)-ROW($A$2)+1)/(($A6=$A$2:$A$4)*($B$2:$D$4<>"")),COLUMN(A1)),1)
And Fill Right, Formula works Excel 2010+ onwards!
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
Luc Le Noir one other way could be
• Formula used in cell K2
=VSTACK({"Customer","Street"},UNIQUE(HSTACK(G3:G12,XLOOKUP(G3:G12,B3:B14,C3:C14))))
Relevant answer
Luc Le Noir hope your query is resolved now, please edit the post as #SOLVED.
Mark Solution as "SOLVED"
Relevant answer
=LET(x,UNIQUE(A2:A10),HSTACK(x,MAP(x,LAMBDA(lm,TEXTJOIN(" ",TRUE,FILTER(B2:B10,A2:A10=lm))))))
Relevant answer
Terrence Tandoh please refer image below for a solution, using Power Query
Feel Free to download the workbook from here:
https://1drv.ms/x/s!AlEkksoPTc2cikJL3kUOXrEGrfZ5?e=xkbZ5m
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
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))),"")
Relevant answer
Also, =HSTACK(TAKE(HSTACK(XLOOKUP(SEQUENCE(SUM(P2:P7)),VSTACK(1,SCAN(1,$P$2:$P$7,LAMBDA(a,c,a+c))),VSTACK(D2:D7,""),,-1),TOCOL(E2:O7,0),SORTBY(HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1),TOCOL(E10:O15,0)),HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1)),1)),,2),TAKE(HSTACK(XLOOKUP(SEQUENCE(SUM(P2:P7)),VSTACK(1,SCAN(1,$P$2:$P$7,LAMBDA(a,c,a+c))),VSTACK(D2:D7,""),,-1),TOCOL(E2:O7,0),SORTBY(HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1),TOCOL(E10:O15,0)),HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1)),1)),,-1))
Relevant answer
Melissa Hewitt Try this.. =Lambda(c,HSTACK(FILTER('FD Food Log'!$B$5:$C,'FD Food Log'!$F$5:$F = "Proteins"), MAP(FILTER('FD Food Log'!$D$5:$D,'FD Food Log'!$F$5:$F = "Proteins"),Indirect("H4:H"&c+3),Indirect("I4:I"&c+3), LAMBDA(x,y,z, If(y=True, x-z, x))), FILTER('FD Food Log'!$E$5:$E,'FD Food Log'!$F$5:$F = "Proteins"), FILTER('FD Food Log'!$O$5:$Q,'FD Food Log'!$F$5:$F = "Proteins")))(Counta(FILTER('FD Food Log'!$D$5:$D,'FD Food Log'!$F$5:$F = "Proteins")))
Relevant answer
if u're using excel 365, try.. =LET(q,UNIQUE(qualifiercolumn),HSTACK(VSTACK("Part Number",UNIQUE(partnumbercolumn)),VSTACK(TRANSPOSE(q),WRAPROWS(qualifiervaluecolumn,ROWS(q)))))
Relevant answer
Σακελλαριου Κωστας you can use a formula like as below
• Formula used in cell G2
=UNIQUE(HSTACK(TableOne[@],TableTwo[@]),1)
Relevant answer
Clare Williams also on which basis do you want to make the calculations for Average Win and Loss, I meant to say, which columns do you want us to consider here.