AUNSTACK

AUNSTACK(a,[h])
a
array
[h]
integer,hight; 0 or omitted h=1,<0,h=abs(h)

new!! Unstacks an array for a certain height. !!NEW!! MAKEARRAY

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AUNSTACK new!! Array Unstack, unstacks an array for a certain height. !!NEW!! MAKEARRAY
Excel Formula:
=LAMBDA(a,[h],
    LET(x,ROWS(a),y,COLUMNS(a),k,MEDIAN(1,ABS(h),x),n,ROUNDUP(x/k,0),
       IFERROR(MAKEARRAY(k,n*y,LAMBDA(r,c,INDEX(IF(a="","",a),k*QUOTIENT(c-1,y)+r,MOD(c-1,y)+1))),"")
    )
)
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1sample 1h,3sample 2h,5
2123=AUNSTACK(A2:C13,3)123=AUNSTACK(R2:T13,5)
345612310111219202128304561231618313233
4789456131415222324313233789456192021343536
51011127891618252627343536101112789222324
6131415131415101112252627
71618if h<0, h=abs(h)16181314152830
8192021=AUNSTACK(A2:C13,-3)192021
92223241231011121920212830222324
10252627456131415222324313233252627
1128#N/A30789161825262734353628#N/A30
12313233313233
13343536343536
14
15if h,0 or omitted h=1
16or equivalent with AUNSTACK(a)=TRANSPOSE(AFLAT(a,1))
17=AUNSTACK(A2:C13)
1812345678910111213141516181920212223242526272830313233343536
19
20if abs(h)>=rws(a), h=rows(a), array will remain intact,(only errors will be replaced with null strings)
21=AUNSTACK(A2:C13,-20)
22123
23456
24789
25101112
26131415
271618
28192021
29222324
30252627
312830
32313233
33343536
34
new AUNSTACK post 1
Cell Formulas
RangeFormula
E2,A21,A17,E8,V2E2=FORMULATEXT(E3)
E3:P5E3=AUNSTACK(A2:C13,3)
V3:AD7V3=AUNSTACK(R2:T13,5)
E9:P11E9=AUNSTACK(A2:C13,-3)
B11,S11B11=NA()
A18:AJ18A18=AUNSTACK(A2:C13)
A22:C33A22=AUNSTACK(A2:C13,-20)
Dynamic array formulas.
 
Upvote 0
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNO
1sample 1Task 1: transform sample 1 in a proper data setor
208-08-21=APP2V({"Date","Doc","Sales"},C4#)=AOVERLAP(C4#,{"Date","Doc","Sales"},-1)
3Invoice 1=ASTACK(AUNSTACK(A2:A19),3)DateDocSalesDateDocSales
43008-08-21Invoice 13008-08-21Invoice 13008-08-21Invoice 130
510-08-2110-08-21Invoice 21510-08-21Invoice 21510-08-21Invoice 215
6Invoice 215-08-21Invoice 32315-08-21Invoice 32315-08-21Invoice 323
71518-08-21Invoice 41618-08-21Invoice 41618-08-21Invoice 416
815-08-2121-08-21Invoice 51821-08-21Invoice 51821-08-21Invoice 518
9Invoice 324-08-21Invoice 62824-08-21Invoice 62824-08-21Invoice 628
1023
1118-08-21sample 2
12Invoice 408-08-2110-08-2115-08-2118-08-2121-08-2124-08-21
1316Invoice 1Invoice 2Invoice 3Invoice 4Invoice 5Invoice 6
1421-08-21301523161828
15Invoice 5
1618Task 2: Transform sample 2 in a proper data set
1724-08-21=TRANSPOSE(C12:H14)
18Invoice 608-08-21Invoice 130functions minisheet
192810-08-21Invoice 215ASTACK
2015-08-21Invoice 323AUNSTACK
2118-08-21Invoice 416APP2V
2221-08-21Invoice 518AOVERLAP
2324-08-21Invoice 628
24
stk-ustk 1
Cell Formulas
RangeFormula
G2,K2,C17,C3G2=FORMULATEXT(G3)
G3:I9G3=APP2V({"Date","Doc","Sales"},C4#)
K3:M9K3=AOVERLAP(C4#,{"Date","Doc","Sales"},-1)
C4:E9C4=ASTACK(AUNSTACK(A2:A19),3)
C18:E23C18=TRANSPOSE(C12:H14)
Dynamic array formulas.
 
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNO
1sample 1Task 1: Transform sample 1 in a proper data set for invoices that already have data
208-08-2119-08-21step 1: proper data setstep 2: refining
3Invoice 1Invoice 7=ASTACK(AUNSTACK(ASTACK(A2:B22)),3)=SORT(AHCLEAN(D4#))
4302108-08-21Invoice 13008-08-21Invoice 130
510-08-2120-08-2110-08-21Invoice 21510-08-21Invoice 215
6Invoice 2Invoice 812-08-21Invoice 32312-08-21Invoice 323
7151213-08-21Invoice 41613-08-21Invoice 416
812-08-2121-08-2115-08-21Invoice 51815-08-21Invoice 518
9Invoice 3Invoice 918-08-21Invoice 616-08-21Invoice 1329
102319-08-21Invoice 721
1113-08-2121-08-2119-08-21Invoice 72120-08-21Invoice 812
12Invoice 4Invoice 1020-08-21Invoice 81221-08-21Invoice 1014
13161421-08-21Invoice 923-08-21Invoice 1119
1415-08-2123-08-2121-08-21Invoice 101424-08-21Invoice 1220
15Invoice 5Invoice 1123-08-21Invoice 1119
16181924-08-21Invoice 1220
1718-08-2124-08-2116-08-21Invoice 1329
18Invoice 6Invoice 12
1920
2016-08-21
21Invoice 13
2229
23Task 2: Transform sample 2 in a proper data set, other method, single cell
24sample 2=TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3))
2508-08-2119-08-2108-08-21Invoice 130
26Invoice 1Invoice 710-08-21Invoice 215
27302112-08-21Invoice 323
2810-08-2120-08-2113-08-21Invoice 416
29Invoice 2Invoice 815-08-21Invoice 518DateDocSales
30151218-08-21Invoice 63308-08-21Invoice 130
3112-08-2121-08-2110-08-21Invoice 215
32Invoice 3Invoice 914-08-21Invoice 143012-08-21Invoice 323
33233213-08-21Invoice 416
3413-08-2121-08-2119-08-21Invoice 72114-08-21Invoice 1430
35Invoice 4Invoice 1020-08-21Invoice 81215-08-21Invoice 518
36161421-08-21Invoice 93216-08-21Invoice 1329
3715-08-2123-08-2121-08-21Invoice 101418-08-21Invoice 633
38Invoice 5Invoice 1123-08-21Invoice 111919-08-21Invoice 721
39181924-08-21Invoice 122020-08-21Invoice 812
4018-08-2124-08-2116-08-21Invoice 132921-08-21Invoice 932
41Invoice 6Invoice 1221-08-21Invoice 1014
42332021-08-21Invoice 152921-08-21Invoice 1529
4316-08-2123-08-21Invoice 1119
44Invoice 1324-08-21Invoice 1220
4529I29:=AVINSERT(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3)))),{"Date","Doc","Sales"})
4614-08-21
47Invoice 14functions minisheet
4830ASTACK
4921-08-21AUNSTACK
50Invoice 15AHCLEAN
5129AVINSERT
52
stk-ustk 2
Cell Formulas
RangeFormula
D3,I3,D24D3=FORMULATEXT(D4)
D4:F17D4=ASTACK(AUNSTACK(ASTACK(A2:B22)),3)
I4:K14I4=SORT(AHCLEAN(D4#))
D25:F42D25=TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3))
I29:K44I29=AVINSERT(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3)))),{"Date","Doc","Sales"})
D45D45=FORMULATEXT(I29)
Dynamic array formulas.
 
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOP
1sample
208-08-2110-08-2112-08-2113-08-2115-08-2118-08-2114-08-21
3Invoice 1Invoice 2Invoice 3Invoice 4Invoice 5Invoice 6Invoice 14
430152316183330
519-08-2120-08-2121-08-2121-08-2123-08-2124-08-2116-08-2121-08-21
6Invoice 7Invoice 8Invoice 9Invoice 10Invoice 11Invoice 12Invoice 13Invoice 15
72112321419202929
8
9Task 1: Transform sample data in a proper data set, single cellTask 2: Update report with new data
10=SORT(AFILL(TRANSPOSE(AUNSTACK(A2:I7,3)),ASTACK(F13:H15)))
11=AOVERLAP(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(A2:I7,3)))),{"Date","Doc","Sales"},-1)08-08-21Invoice 130
12DateDocSales09-08-21Invoice 1622
1308-08-21Invoice 130New data:09-08-2116-08-2125-08-2110-08-21Invoice 215
1410-08-21Invoice 215Invoice 16Invoice 17Invoice 1812-08-21Invoice 323
1512-08-21Invoice 32322273313-08-21Invoice 416
1613-08-21Invoice 41614-08-21Invoice 1430
1714-08-21Invoice 143015-08-21Invoice 518
1815-08-21Invoice 518functions minisheet16-08-21Invoice 1727
1916-08-21Invoice 1329ASTACK16-08-21Invoice 1329
2018-08-21Invoice 633AUNSTACK18-08-21Invoice 633
2119-08-21Invoice 721AHCLEAN19-08-21Invoice 721
2220-08-21Invoice 812AOVERLAP20-08-21Invoice 812
2321-08-21Invoice 932AFILL21-08-21Invoice 932
2421-08-21Invoice 101421-08-21Invoice 1014
2521-08-21Invoice 152921-08-21Invoice 1529
2623-08-21Invoice 111923-08-21Invoice 1119
2724-08-21Invoice 122024-08-21Invoice 1220
2825-08-21Invoice 1833
29
stk-ustk 3
Cell Formulas
RangeFormula
J10,A11J10=FORMULATEXT(J11)
J11:L28J11=SORT(AFILL(TRANSPOSE(AUNSTACK(A2:I7,3)),ASTACK(F13:H15)))
A12:C27A12=AOVERLAP(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(A2:I7,3)))),{"Date","Doc","Sales"},-1)
Dynamic array formulas.
 
The combo ASTACK/ANSTACK is created to solve any stacking/unstacking problem with ease, as long there is a structure pattern 1D or 2D.
Note: Posted same example at ASTACK, but here is solved in a different way, with same outcome.
Formula used in ASTACK post: =SORT(TRANSPOSE(AUNSTACK(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6)) calls AFLAT and AHCLEAN
Formula used here : =SORT(TRANSPOSE(AUNSTACK(ASTACK(AVCLEAN(AUNSTACK(B10:G30,3))),6)),SEQUENCE(,6)) calls AVCLEAN
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQ
1I will try to complicate the example as much as possible with a 2D data structure.
2Imagine that we recive data in this form factor, like a 2D card with this fields:
3
4DateCompanyCompany names: from A to D
5Invoice nr.Sales pers.A1 or B3 represent sales persons of company A , respectivly B
6ProductAmounta1 or d4 represent products of respective companies
7
8And we receive data in a crazy way like an unfinished puzzle:Task: Transform in a proper sorted data set
9Note: What is important, card is 3 by 2 and has 3*2=6 elements
1018-08-21A
11Inv.01A3Single cell formula:
12a623=SORT(TRANSPOSE(AUNSTACK(ASTACK(AVCLEAN(AUNSTACK(B10:G30,3))),6)),SEQUENCE(,6))
1325-08-21C03-08-21D03-08-21Inv.46d1DD312
14Inv.23C2Inv.46D306-08-21Inv.58b9BB429
15c432d11206-08-21Inv.72a8AA329
1615-08-21B15-08-21Inv.18b5BB219
17Inv.18B215-08-21Inv.33d8DD728
18b51917-08-21Inv.09c3CC547
1917-08-21A17-08-21C17-08-21Inv.12a1AA715
20Inv.12A7Inv.09C518-08-21Inv.01a6AA323
21a115c34725-08-21Inv.23c4CC232
2215-08-21D
23Inv.33D7As we see, is so easy to check that data is consistent.
24d828
2506-08-21BTo add the headers
26Inv.58B4=AFILL(APP2V("",F46#),B4:C6)
27b929DateCompanyInvoice nr.Sales pers.ProductAmount
2806-08-21A03-08-21DInv.46D3d112
29Inv.72A306-08-21AInv.72A3a829
30a82906-08-21BInv.58B4b929
3115-08-21BInv.18B2b519
32functions15-08-21DInv.33D7d828
33ASTACK17-08-21AInv.12A7a115
34AUNSTACK17-08-21CInv.09C5c347
35AVCLEAN18-08-21AInv.01A3a623
36APP2V25-08-21CInv.23C2c432
37AFILL
38
Sheet4
Cell Formulas
RangeFormula
I12,I26I12=FORMULATEXT(I13)
I13:N21I13=SORT(TRANSPOSE(AUNSTACK(ASTACK(AVCLEAN(AUNSTACK(B10:G30,3))),6)),SEQUENCE(,6))
I27:N36I27=AFILL(APP2V("",F46#),B4:C6)
Dynamic array formulas.
 
Would AUNSTACK feasibly upgrade from using MakeArray? It seems very slow on large data sets.
 
Sorry for replying so late. With the upcoming Easter holiday, finally, I am having some spare time to catch up.
Indeed, all lambda helper functions, as iterative algorithms, are more or less,.."slow".
Back to 2021, this function was in the phase of enthusiastic experimental mastering of lambda helper functions My iterative algorithmic awareness came later here ( ASCAN post 44-46 ).when I compared 3 of my functions, 2 iterative methods ("digital"), 1 non iterative ("analogic") and one that tboulden uses for his THUNKIT and SCANBYROWS designs that use makearray and came out to be really really slow.
That's why, using the experience we have accumulated with these functions so far, I think it is time for us to contribute, to write and adopt a Lambda Performance Act (LPA). This should include general rules and recommendations of modern lambda "programming".
A glimpse of my LPA's draft golden rules:
- Use an iterative algorithm design (lambda helper functions) ONLY when there is no other non-iterative variant possible.
- All functions (especially the ones that use new lambda helper functions) should be accompanied by a Lambda Performance Certificate (LPC).
This should contain, like any "responsible" algorithm design, info about: identifying the iterative variable (nr.rows or nr.unique values/vector…) ; the complexity of the algorithm ( Big O notation, like O(nr.rows^2) or O(3*nr.clms) ) ; speed estimation for different sample data sets sizes. Like 30s/1Mrows or instant/<=10k colums or fair/100k elements when fair means less then 30s calc time...etc ; error management level, data types spectrum etc.. (To be continued...)

To cut my revolutionary long ideas short and get back to the function, following the 1st golden rule,for AUNSTACK we can have a quite simple
non-iterative, or "analogic" or "in bulk" lighting fast alternative. Positive that this function will comply any speed performance requirements 😉
(no MAKEARRAY) same arguments, name and functionality: AUNSTACK(ar,[h])

Excel Formula:
=LAMBDA(ar, [h],
    LET(
        a, IF(ar = "", "", ar),
        x, ROWS(a),
        y, COLUMNS(a),
        k, MEDIAN(1, h, x),
        s, SEQUENCE(, ROUNDUP(x / k, 0) * y),
        q, QUOTIENT(s - 1, y),
        m, MOD(s - 1, y) + 1,
        IFERROR(INDEX(a, SEQUENCE(k) + k * q, m), "")
    )
)
Book1.xlsx
ABCDEFGHIJKLMNOPQRST
1h,10 (height or new nr. of rows)
2ar=AUNSTACK(B3#,10)
3123412344142434481828384
4567856784546474885868788
5910111291011124950515289909192
613141516131415165354555693949596
7171819201718192057585960979899100
8212223242122232461626364101102103104
9252627282526272865666768105106107108
10293031322930313269707172
11333435363334353673747576
12373839403738394077787980
1341424344
1445464748Note: As we see, AUNSTACK has nothing in common with transpose
1549505152
1653545556
1757585960
1861626364
1965666768
2069707172
2173747576
2277787980
2381828384
2485868788
2589909192
2693949596
27979899100
28101102103104
29105106107108
30
Sheet1
Cell Formulas
RangeFormula
H2H2=FORMULATEXT(H3)
B3:E29B3=SEQUENCE(27,4)
H3:S12H3=AUNSTACK(B3#,10)
Dynamic array formulas.
 
Check this out, latest YT of PQ's master Goodly, and our lambda's alternatives. 😉
First thing first, the ASTACK version of latest "analogic" non iterative concept used for AUNSTACK.
ASTACK(ar,[w]) (w: width)
Excel Formula:
=LAMBDA(ar, [w],
    LET(
        a, IF(ar = "", "", ar),
        x, ROWS(a),
        y, COLUMNS(a),
        k, MEDIAN(1, w, y),
        s, SEQUENCE(ROUNDUP(y / k, 0) * x),
        q, QUOTIENT(s - 1, x),
        m, MOD(s - 1, x) + 1,
        IFERROR(INDEX(a, m, SEQUENCE(, k) + k * q), "")
    )
)
Book1.xlsx
ABCDEFGH
1
22 ways of solving
3=ASTACK(TRANSPOSE(Table1[Data]),4)
4DataCecilia ChapmanNew York42(257) 563-7401
5Cecilia ChapmanIris WatsonHong Kong33(372) 587-2335
6New YorkJoanne MDelhi39(786) 713-8616
742Peter HighesDelhi39(786) 713-8616
8(257) 563-7401ChandeepGurgaon36(786) 713-8616
9Iris Watson
10Hong Kong=TRANSPOSE(AUNSTACK(Table1[Data],4))
1133Cecilia ChapmanNew York42(257) 563-7401
12(372) 587-2335Iris WatsonHong Kong33(372) 587-2335
13Joanne MJoanne MDelhi39(786) 713-8616
14DelhiPeter HighesDelhi39(786) 713-8616
1539ChandeepGurgaon36(786) 713-8616
16(786) 713-8616
17Peter Highes
18Delhi
1939
20(786) 713-8616
21Chandeep
22Gurgaon
2336
24(786) 713-8616
25
26
Sheet2
Cell Formulas
RangeFormula
D3,D10D3=FORMULATEXT(D4)
D4:G8D4=ASTACK(TRANSPOSE(Table1[Data]),4)
D11:G15D11=TRANSPOSE(AUNSTACK(Table1[Data],4))
Dynamic array formulas.


Another example for ASTACK

Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1
2ar
31234567891011121314151617181920212223
42425262728293031323334353637383940414243444546
54748495051525354555657585960616263646566676869
67071727374757677787980818283848586878889909192
793949596979899100101102103104105106107108109110111112113114115
8116117118119120121122123124125126127128129130131132133134135136137138
9139140141142143144145146147148149150151152153154155156157158159160161
10
11w,7
12=ASTACK(B3#,7)
131234567
1424252627282930
1547484950515253
1670717273747576
1793949596979899
18116117118119120121122
19139140141142143144145
20891011121314
2131323334353637
2254555657585960
2377787980818283
24100101102103104105106
25123124125126127128129
26146147148149150151152
2715161718192021
2838394041424344
2961626364656667
3084858687888990
31107108109110111112113
32130131132133134135136
33153154155156157158159
342223
354546
366869
379192
38114115
39137138
40160161
41
Sheet3
Cell Formulas
RangeFormula
B3:X9B3=SEQUENCE(7,23)
B12B12=FORMULATEXT(B13)
B13:H40B13=ASTACK(B3#,7)
Dynamic array formulas.
 
Part II - Unstack a Million Uneven Rows to Columns in Power Query
Solving this with a simple formula or a lambda if we want.
advantages: max nr. of fields (5) calculated implicitly, no refresh, calc time instant (for 910k rows PQ needed 2min 10sec, seems that any calculation implying any kind of iterations takes time, compared with non-iterative dynamic "in bulk" instant calculations)
VUNSTACK(v) Vector Unstack v: column vector
Excel Formula:
=LAMBDA(v,
    LET(
        a, (v = "") * SEQUENCE(ROWS(v)),
        b, FILTER(a, a),
        c, VSTACK(0, DROP(b, -1)),
        d, b - c - 1,
        e, SEQUENCE(ROWS(b)) ^ 0 + SEQUENCE(, MAX(d)) - 1,
        IF(e <= d, INDEX(v, e + c), "")
    )
)
Unstack-Uneven-Rows-in-Columns PQ.xlsx
ABCDEFGHIJKLM
1
2
3Data=VUNSTACK(LargeData[Data])rows=ROWS(LargeData[Data])
4Cecilia ChapmanCecilia ChapmanNew York42(257) 563-7401table910419
5New YorkIris WatsonHong Kong=ROWS(D4#)
642Joanne MDelhi39(786) 713-8616result197917
7(257) 563-7401Peter HighesDelhi39
8ChandeepDubai36999Red
9Iris WatsonCecilia ChapmanNew York42(257) 563-7401
10Hong KongIris WatsonHong Kong
11Joanne MDelhi39(786) 713-8616
12Joanne MPeter HighesDelhi39
13DelhiChandeepDubai36999Red
1439Cecilia ChapmanNew York42(257) 563-7401
15(786) 713-8616Iris WatsonHong Kong
16Joanne MDelhi39(786) 713-8616
17Peter HighesPeter HighesDelhi39
18DelhiChandeepDubai36999Red
1939Cecilia ChapmanNew York42(257) 563-7401
20Iris WatsonHong Kong
21ChandeepJoanne MDelhi39(786) 713-8616
22DubaiPeter HighesDelhi39
2336ChandeepDubai36999Red
24999Cecilia ChapmanNew York42(257) 563-7401
25RedIris WatsonHong Kong
26Joanne MDelhi39(786) 713-8616
27Cecilia ChapmanPeter HighesDelhi39
28New YorkChandeepDubai36999Red
2942Cecilia ChapmanNew York42(257) 563-7401
30(257) 563-7401Iris WatsonHong Kong
31Joanne MDelhi39(786) 713-8616
32Iris WatsonPeter HighesDelhi39
33Hong KongChandeepDubai36999Red
34Cecilia ChapmanNew York42(257) 563-7401
35Joanne MIris WatsonHong Kong
36DelhiJoanne MDelhi39(786) 713-8616
3739Peter HighesDelhi39
38(786) 713-8616ChandeepDubai36999Red
39Cecilia ChapmanNew York42(257) 563-7401
40Peter HighesIris WatsonHong Kong
41DelhiJoanne MDelhi39(786) 713-8616
4239Peter HighesDelhi39
43ChandeepDubai36999Red
44ChandeepCecilia ChapmanNew York42(257) 563-7401
45DubaiIris WatsonHong Kong
4636Joanne MDelhi39(786) 713-8616
47999Peter HighesDelhi39
48RedChandeepDubai36999Red
49Cecilia ChapmanNew York42(257) 563-7401
50Cecilia ChapmanIris WatsonHong Kong
51New YorkJoanne MDelhi39(786) 713-8616
5242Peter HighesDelhi39
53(257) 563-7401ChandeepDubai36999Red
54Cecilia ChapmanNew York42(257) 563-7401
55Iris WatsonIris WatsonHong Kong
56Hong KongJoanne MDelhi39(786) 713-8616
57Peter HighesDelhi39
58Joanne MChandeepDubai36999Red
59DelhiCecilia ChapmanNew York42(257) 563-7401
6039Iris WatsonHong Kong
61(786) 713-8616Joanne MDelhi39(786) 713-8616
62Peter HighesDelhi39
63Peter HighesChandeepDubai36999Red
64DelhiCecilia ChapmanNew York42(257) 563-7401
6539Iris WatsonHong Kong
66Joanne MDelhi39(786) 713-8616
67ChandeepPeter HighesDelhi39
68DubaiChandeepDubai36999Red
6936Cecilia ChapmanNew York42(257) 563-7401
70999Iris WatsonHong Kong
71RedJoanne MDelhi39(786) 713-8616
72Peter HighesDelhi39
73Cecilia ChapmanChandeepDubai36999Red
74New YorkCecilia ChapmanNew York42(257) 563-7401
7542Iris WatsonHong Kong
76(257) 563-7401Joanne MDelhi39(786) 713-8616
77Peter HighesDelhi39
78Iris WatsonChandeepDubai36999Red
79Hong KongCecilia ChapmanNew York42(257) 563-7401
80Iris WatsonHong Kong
81Joanne MJoanne MDelhi39(786) 713-8616
82DelhiPeter HighesDelhi39
8339ChandeepDubai36999Red
84(786) 713-8616Cecilia ChapmanNew York42(257) 563-7401
85Iris WatsonHong Kong
86Peter HighesJoanne MDelhi39(786) 713-8616
87DelhiPeter HighesDelhi39
8839ChandeepDubai36999Red
89Cecilia ChapmanNew York42(257) 563-7401
90ChandeepIris WatsonHong Kong
91DubaiJoanne MDelhi39(786) 713-8616
9236Peter HighesDelhi39
93999ChandeepDubai36999Red
94RedCecilia ChapmanNew York42(257) 563-7401
95Iris WatsonHong Kong
96Cecilia ChapmanJoanne MDelhi39(786) 713-8616
97New YorkPeter HighesDelhi39
9842ChandeepDubai36999Red
99(257) 563-7401Cecilia ChapmanNew York42(257) 563-7401
100Iris WatsonHong Kong
101Iris WatsonJoanne MDelhi39(786) 713-8616
102Hong KongPeter HighesDelhi39
103ChandeepDubai36999Red
Sheet1
Cell Formulas
RangeFormula
D3,K5,K3D3=FORMULATEXT(D4)
D4:H197920D4=VUNSTACK(LargeData[Data])
K4K4=ROWS(LargeData[Data])
K6K6=ROWS(D4#)
Dynamic array formulas.
 
How easy is to solve a task with the proper lambda tools?
Check this out Minda's latest YT:

Excel Formula:
=LET(
    q, C3:N7,
    b, LAMBDA(x, BYROW(x, LAMBDA(x, SUM(x)))),
    HSTACK(AUNSTACK(b(ASTACK(q, 3)), 5), b(q))
)
or without ASTACK, AUNSTACK
Excel Formula:
=LET(
    q, C3:N7,
    b, LAMBDA(x, BYROW(x, LAMBDA(x, SUM(x)))),
    HSTACK(TRANSPOSE(WRAPCOLS(b(WRAPROWS(TOCOL(q), 3)), 4)), b(q))
)
or , for fun:
Excel Formula:
=LET(
    q, C3:N7,
    b, LAMBDA(x, BYROW(x, LAMBDA(x, SUM(x)))),
    DROP(REDUCE(0, {1, 4, 7, 10, 0}, LAMBDA(v, i, HSTACK(v, IF(i, b(CHOOSECOLS(q, SEQUENCE(3, , i))), b(q))))), , 1)
)
summarise_mths_into_qtrs.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2Jan-13Feb-13Mar-13Apr-13May-13Jun-13Jul-13Aug-13Sep-13Oct-13Nov-13Dec-13
3Things 1954494281883
4Things 293263110346104
5Things 3298957559559
6Things 41091836583761
7Things 51517497310673
8
9
10C12=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),HSTACK(AUNSTACK(b(ASTACK(q,3)),5),b(q)))
11Quarter 1Quarter 2Quarter 3Quarter 4Total
12Things 11817111965
13Things 21410172061
14Things 31921191978
15Things 42017161467
16Things 5720201663
17
18
19=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),HSTACK(TRANSPOSE(WRAPCOLS(b(WRAPROWS(TOCOL(q),3)),4)),b(q)))
201817111965
211410172061
221921191978
232017161467
24720201663
25
26=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),DROP(REDUCE(0,{1,4,7,10,0},LAMBDA(v,i,HSTACK(v,IF(i,b(CHOOSECOLS(q,SEQUENCE(3,,i))),b(q))))),,1))
271817111965
281410172061
291921191978
302017161467
31720201663
32
Sheet2
Cell Formulas
RangeFormula
C10C10=FORMULATEXT(C12)
C12:G16C12=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),HSTACK(AUNSTACK(b(ASTACK(q,3)),5),b(q)))
C19,C26C19=FORMULATEXT(C20)
C20:G24C20=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),HSTACK(TRANSPOSE(WRAPCOLS(b(WRAPROWS(TOCOL(q),3)),4)),b(q)))
C27:G31C27=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),DROP(REDUCE(0,{1,4,7,10,0},LAMBDA(v,i,HSTACK(v,IF(i,b(CHOOSECOLS(q,SEQUENCE(3,,i))),b(q))))),,1))
Dynamic array formulas.
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top