ASTACK

ASTACK(a,[w])
a
array
[w]
integer, width; if 0 or omitted w=1; <0,w=abs(w)

new!! Stacks an array for a certain width

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
ASTACK new!! Array Stack, stacks an array for a certain width. Calls AUNSTACK
Excel Formula:
=LAMBDA(a,[w],TRANSPOSE(AUNSTACK(TRANSPOSE(a),w)))
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTU
1sample 1if w,0 or omitted w=1
2123456789101112or equivalent ASTACK(a)=AFLAT(TRANSPOSE(a),1)
31314161718192021222324=ASTACK(A2:L4)
425262728293031#N/A333435361
513
6w,3sample 225
7=ASTACK(A2:L4,3)1234567891011122
8123131416171819202122232414
9131425262728293031#N/A3334353626
102526273
11456w,5if w<0,w,-5 w=abs(w)=5
12161718=ASTACK(E7:P9,5)=ASTACK(E7:P9,-5)27
1328293012345123454
14789131416171314161716
151920212526272829252627282928
1631336789106789105
171011121819202122181920212217
18222324303133343031333429
19343536111211126
202324232418
213536353630
227
23if abs(w)>=clms(a), w=clms(a), array will remain intact,(only errors will be replaced with null strings)19
24=ASTACK(A2:L4,13)31
251234567891011128
26131416171819202122232420
272526272829303133343536
289
2921
3033
3110
3222
3334
3411
3523
3635
3712
3824
3936
40
new ASTACK post 1
Cell Formulas
RangeFormula
R3,A24,K12,E12,A7R3=FORMULATEXT(R4)
H4,L9H4=NA()
R4:R39R4=ASTACK(A2:L4)
A8:C19A8=ASTACK(A2:L4,3)
E13:I21E13=ASTACK(E7:P9,5)
K13:O21K13=ASTACK(E7:P9,-5)
A25:L27A25=ASTACK(A2:L4,13)
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.
 
Used same examples since both functions are used.
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.
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(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6))
1325-08-21C03-08-21D03-08-21DInv.46D3d112
14Inv.23C2Inv.46D306-08-21AInv.72A3a829
15c432d11206-08-21BInv.58B4b929
1615-08-21B15-08-21BInv.18B2b519
17Inv.18B215-08-21DInv.33D7d828
18b51917-08-21AInv.12A7a115
1917-08-21A17-08-21C17-08-21CInv.09C5c347
20Inv.12A7Inv.09C518-08-21AInv.01A3a623
21a115c34725-08-21CInv.23C2c432
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("",I13#),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
3215-08-21DInv.33D7d828
33functions17-08-21AInv.12A7a115
34ASTACK17-08-21CInv.09C5c347
35AUNSTACK18-08-21AInv.01A3a623
36AFLAT25-08-21CInv.23C2c432
37AHCLEAN
38APP2V
39AFILL
40
Sheet4
Cell Formulas
RangeFormula
I12,I26I12=FORMULATEXT(I13)
I13:N21I13=SORT(TRANSPOSE(AUNSTACK(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6))
I27:N36I27=AFILL(APP2V("",I13#),B4:C6)
Dynamic array formulas.
 
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Quite unlikely to receive data as in previous post, but if we do, the combo will do the trick.If not for subtotals versatility, most common scenarios
2Combo was designed also, for subtotals constructions. CheckASUBTOTof unstacking and stacking do not even require dedicated functions,
3This is how subtotals concept works in a basic example, step by step:
4Task: Insert subtotals values every 3 rowssample data that has 4 records with 3 fields each
54. astack and cleanInv 01unstacking
611. aunstack=AHCLEAN(ASTACK(C19#))04-09-21=XLOOKUP(SEQUENCE(4,3),SEQUENCE(4*3),N5:N16)
73=AUNSTACK(A6:A16,3)123Inv 0104-09-2123
84156835. append Grand TotalInv 02Inv 0205-09-2117
9538494=APP2V(H7#,SUM(A6:A16))05-09-21Inv 0305-09-2117
1084768117Inv 0406-09-2143
11753Inv 03or
1268405-09-21=ARESIZE(N5:N16,4,3)
1342. totals7817Inv 0104-09-2123
146=BYCOL(C8#,LAMBDA(a,SUM(a)))205Inv 04Inv 0205-09-2117
15882016176806-09-21Inv 0305-09-2117
1694743Inv 0406-09-2143
173. appending620
18=APP2V(C8#,C15#)166and for stacking=AFLAT(P7#)
19156884Inv 01
2038499604-09-21
21476171623
2282016178Inv 02
23905-09-21
24Note: At step 2. other functions can be used1717
25MIN,MAX, AVERAGE..etc.61Inv 03
2605-09-21
27functions17
28AHCLEANInv 04
29APP2V06-09-21
30ARESIZE43
31
stk-ustk 6
Cell Formulas
RangeFormula
H6,Q18,C18,C14,P12,J9,C7H6=FORMULATEXT(H7)
O6O6=FORMULATEXT(P7)
H7:H21H7=AHCLEAN(ASTACK(C19#))
P7:R10P7=XLOOKUP(SEQUENCE(4,3),SEQUENCE(4*3),N5:N16)
C8:F10C8=AUNSTACK(A6:A16,3)
J10:J25J10=APP2V(H7#,SUM(A6:A16))
P13:R16P13=ARESIZE(N5:N16,4,3)
C15:F15C15=BYCOL(C8#,LAMBDA(a,SUM(a)))
C19:F22C19=APP2V(C8#,C15#)
Q19:Q30Q19=AFLAT(P7#)
Dynamic array formulas.
 

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

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