HSTACK Function

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

Mayukh Bhattacharya 1 year ago

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

3 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

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

Relevant answer

Samir Shaker 1 year ago

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

1 Like

Relevant answer

Fiktur Fox 1 year ago

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

0 Likes

Hot network questions