APIVOT

=APIVOT(a,rw,cl,vl,vf)
a
array
rw
integer, column index that holds corespondent PT row values
cl
integer, column index that holds corespondent PT column values
vl
integer, integer, column index that holds corespondent PT values
vf
integer, value field, 0 or ignored for sum, 1 for count, 2 for min, 3 for max
array pivot, reproduces basic functionality of Pivot Table, with a single cell lambda, for any array, regular, dynamic, table, no refresh needed
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
APIVOT array pivot, reproduces basic functionality of Pivot Table, with a single cell lambda, for any array, regular, dynamic, table. calls AAGGREGATE , APPEND2V , APPEND2H
Basic functionality means: as in PT interface we can select rows values, column values, values, value fields, only sum, count, min and max
- disadvantages: was not designed to compete with mighty PT, does not have grouping functionality as PT does, though, could be added but the formulas can get discouraging huge.
- advantages: no table format needed, no refresh needed, so if we filter a large table for some complex criteria in a simple dynamic array, this lambda can be handy.
Other functions on minisheet ASELECT
Note: Sample data, courtesy of Mike Girvin ExcelIsFun
Excel Formula: 
=LAMBDA(a,rw,cl,vl,vf,
    LET(g,"Grand Total",fn,IF(vf=0,0,vf),q,SEQUENCE(,COLUMNS(a)),c,INDEX(a,,cl),r,INDEX(a,,rw),v,INDEX(a,,vl),uc,SORT(UNIQUE(c)),ur,SORT(UNIQUE(r)),tc,TRANSPOSE(uc),f,IF(AND(ISNUMBER(v)),fn,1),
       ar,SWITCH(f,0,SUMIFS(v,r,ur,c,tc),2,MINIFS(v,r,ur,c,tc),3,MAXIFS(v,r,ur,c,tc),1,COUNTIFS(r,ur,c,tc)),fg,SWITCH(f,2,0,3,1,4,7,5),
       rc,AAGGREGATE(ar,fg,),br,TRANSPOSE(AAGGREGATE(TRANSPOSE(ar),fg,)),
       w,ROWS(ar)+1,u,COLUMNS(ar)+2,sr,SEQUENCE(w),sc,SEQUENCE(,u)-1,lc,IF(sr=w,g,INDEX(ur,sr)),tr,SWITCH(sc,0,"("&rw&"\"&cl&")"&" "&vl&" vf="&fn,u-1,g,INDEX(tc,sc)),
       aa,APPEND2V(APPEND2H(ar,rc,),br,),ab,IF(aa="",SWITCH(f,2,MIN(br,rc),3,MAX(br,rc),SUM(rc)),aa),ac,APPEND2V(tr,APPEND2H(lc,ab,),),
       IFS(ISNA(XMATCH(fn,{0,1,2,3})),"check function nr.",NOT(AND(ISNUMBER(XMATCH(CHOOSE({1,2,3},rw,cl,vl),q)))),"check selection",TRUE,ac)
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1sample data, courtesy of Mike GirvinExcelIsFun=APIVOT(Rev,2,4,6,)vf=0sumSum of Net RevenueColumn Labels
2DateSales RepUnitsProductRegionNet Revenue(2\4) 6 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total
301-01-20Tynia Malone500QuadMidWest12560.88Chantel Mims7659.578379.3328169.7129746.5913486.9687442.16Chantel Mims7659.578379.3328169.7129746.61348787442.16
401-01-20Kiki Sho38YanakiMidWest902.79Hien Pham7785.007809.9422079.839952.8819538.6867166.33Hien Pham77857809.9422079.839952.8819538.767166.33
501-01-20Hien Pham500YanakiMidWest8035.63Janis Figueroa161.701104.4218189.425683.153039.7528178.44Janis Figueroa161.71104.4218189.425683.153039.7528178.44
601-01-20Chantel Mims500YanakiWest8035.63Kiki Sho9137.046161.932809.605981.8010598.5734688.94Kiki Sho9137.046161.932809.65981.810598.634688.94
701-01-20Tynia Malone39QuadEast1481.47Tynia Malone948.47339.5038904.912434.6314517.6357145.14Tynia Malone948.47339.538904.912434.6314517.657145.14
801-01-20Kiki Sho48SunsetMidWest934.2Grand Total25691.7823795.12110153.553799.0561181.59274621.01Grand Total25691.7823795.12110153.4753799.161181.6274621.01
901-01-20Hien Pham13QuadCanada580.97
1002-01-20Tynia Malone27YanakiNorthWest641.45=APIVOT(Rev,2,4,6,1)vf=1count
1102-01-20Hien Pham13SunsetMidWest337.35(2\4) 6 vf=1Crested BeautMajestic BeautQuadSunsetYanakiGrand Total
1202-01-20Chantel Mims36SunsetMidWest732.87Chantel Mims7567631
1302-01-20Tynia Malone44QuadSouth1596.61Hien Pham2456724
1403-01-20Hien Pham100Crested BeautMidWest1816.5Janis Figueroa1242110
1503-01-20Chantel Mims7YanakiMexico188.65Kiki Sho5234519
1603-01-20Hien Pham5YanakiMexico139.75Tynia Malone1177319
1703-01-20Chantel Mims37QuadMexico1374.05Grand Total1614252622103
1803-01-20Janis Figueroa525QuadMexico13188.92
1903-01-20Chantel Mims450SunsetNorthWest6714.56=APIVOT(Rev,2,4,6,2)vf=2minMin of Net RevenueColumn Labels
2004-01-20Hien Pham45SunsetCanada916.09(2\4) 6 vf=2Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total
2104-01-20Tynia Malone3SunsetWest74.85Chantel Mims215.6209.7312.83639.6726.9526.95Chantel Mims215.6209.7312.83639.6726.9526.95
2204-01-20Chantel Mims48Crested BeautWest934.2Hien Pham1816.533.95218.4524.9555.924.95Hien Pham1816.533.95218.4524.9555.924.95
2304-01-20Chantel Mims100Crested BeautMidWest1746.5Janis Figueroa161.734.9585.38174.653039.7534.95Janis Figueroa161.734.9585.38174.653039.7534.95
2404-01-20Hien Pham2YanakiSouth55.9Kiki Sho249.5305.55305.83311.457.957.9Kiki Sho249.5305.55305.83311.457.957.9
2504-01-20Kiki Sho25QuadMexico907.16Tynia Malone948.47339.5298.8374.85641.4574.85Tynia Malone948.47339.5298.8374.85641.4574.85
2604-01-20Chantel Mims8Crested BeautSouth215.6Grand Total161.733.9585.3824.9526.9524.95Grand Total161.733.9585.3824.9526.9524.95
2704-01-20Kiki Sho34Crested BeautMidWest778.86
2804-01-20Chantel Mims275YanakiWest4419.59=APIVOT(Rev,2,4,6,3)vf=3max
2905-01-20Chantel Mims200QuadEast5976.6(2\4) 6 vf=3Crested BeautMajestic BeautQuadSunsetYanakiGrand Total
3005-01-20Hien Pham600YanakiEast9297.75Chantel Mims2724.756291.2514114.387531.788035.6314114.38
3105-01-20Janis Figueroa36Majestic BeautMidWest1069.47Hien Pham5968.56291.2513816.967574.199297.7513816.96
3205-01-20Chantel Mims28QuadMidWest1039.82Janis Figueroa161.71069.4713188.925508.53039.7513188.92
3305-01-20Kiki Sho525YanakiMidWest8437.41Kiki Sho5021.195856.381596.614191.258437.418437.41
3405-01-20Chantel Mims6Majestic BeautCanada209.7Tynia Malone948.47339.512560.88934.27633.8412560.88
3505-01-20Kiki Sho9Majestic BeautWest305.55Grand Total5968.56291.2514114.387574.199297.7514114.38
3605-01-20Hien Pham550QuadMidWest13816.96
3706-01-20Kiki Sho250SunsetEast4191.25=APIVOT(Rev,2,4,3,)Sum of Units2Column Labels
3806-01-20Tynia Malone7QuadCanada298.83(2\4) 3 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total
3906-01-20Chantel Mims150Crested BeautCanada2724.75Chantel Mims401321102220058174566Chantel Mims401321102220058174566
4006-01-20Tynia Malone43Crested BeautMidWest948.47Hien Pham50029885666011893503Hien Pham50029885666011893503
4107-01-20Kiki Sho21SunsetNorthWest544.95Janis Figueroa6376774071501277Janis Figueroa6376774071501277
4207-01-20Hien Pham13QuadWest554.97Kiki Sho560309763316141890Kiki Sho560309763316141890
4307-01-20Tynia Malone24SunsetWest529.38Tynia Malone431014711118772512Tynia Malone431014711118772512
4407-01-20Tynia Malone15SunsetMexico389.25Grand Total151097541023514364713748Grand Total151097541023514364713748
4508-01-20Kiki Sho2YanakiMidWest57.9
4608-01-20Tynia Malone4SunsetNorthWest99.8=APIVOT(Rev,4,5,3,)Sum of UnitsColumn Labels
4708-01-20Kiki Sho42YanakiEast997.82(4\5) 3 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total
4808-01-20Chantel Mims500SunsetEast7460.63Crested Beaut1501694112830408891510Crested Beaut150169411283408891510
4908-01-20Hien Pham38Majestic BeautEast1161.19Majestic Beaut15381033657259260975Majestic Beaut15381033657259260975
5008-01-20Tynia Malone48SunsetMexico934.2Quad170241638107827512554454102Quad170241638107827512554454102
5108-01-20Chantel Mims29SunsetSouth639.67Sunset55802664497482299853514Sunset55802664497482299853514
5209-01-20Hien Pham45YanakiCanada1069.09Yanaki458242110655393777763647Yanaki458242110655393777763647
5309-01-20Tynia Malone10SunsetCanada239.5Grand Total43520741744325913532328255513748Grand Total43520741744325913532328255513748
5409-01-20Kiki Sho7QuadWest305.83
5509-01-20Janis Figueroa6Crested BeautMidWest161.7=APIVOT(Rev,4,5,6,)Sum of Net RevenueColumn Labels
5609-01-20Chantel Mims1YanakiWest26.95(4\5) 6 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total
5709-01-20Tynia Malone475YanakiNorthWest7633.84Crested Beaut2724.753153.696338.655452.0306184.11838.5625691.78Crested Beaut2724.753153.696338.655452.036184.11838.5625691.78
5809-01-20Chantel Mims33Crested BeautMexico699.85Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.12Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.12
5909-01-20Janis Figueroa75QuadCanada2457.56Quad5794.927543.4517379.5727417.666908.4833327.4711781.9110153.47Quad5794.927543.4517379.627417.666908.4833327.511781.9110153.47
6009-01-20Chantel Mims50Majestic BeautNorthWest1348.13Sunset1155.5912777.589997.947512.927533.96639.6714181.453799.05Sunset1155.5912777.69997.947512.927533.96639.6714181.453799.05
6110-01-20Janis Figueroa7SunsetNorthWest174.65Yanaki1069.0914095.56579.9517433.739222.856298.2412482.261181.59Yanaki1069.0914095.6579.9517433.739222.856298.2412482.261181.59
6210-01-20Chantel Mims575QuadSouth14114.38Grand Total11268.638731.4734635.6164742.1925264.0753064.2846914.8274621.01Grand Total11268.638731.534635.664742.1925264.0753064.346914.8274621.01
6310-01-20Janis Figueroa150YanakiEast3039.75
6411-01-20Kiki Sho300Majestic BeautMidWest5856.38
6511-01-20Janis Figueroa400SunsetMidWest5508.5dynamic APIVOT interface usingASELECT(proves that APIVOT works with dynamic arrays,no need of tables, no refresh)
6611-01-20Chantel Mims32YanakiEast760.24date 1date 2row startrow end
6711-01-20Chantel Mims28Crested BeautMexico617.6103-01-2005-01-201227
6811-01-20Kiki Sho41Crested BeautWest904.36=APIVOT(H70#,1,2,3,)
6911-01-20Chantel Mims425SunsetWest5852.78=ASELECT(Rev,J67,K67,,,{2,4,6})(1\2) 3 vf=0Crested BeautQuadSunsetYanakiGrand Total
7012-01-20Chantel Mims6Majestic BeautNorthWest215.7Hien PhamCrested Beaut1816.5Chantel Mims2896.37350.656714.564608.2421569.75
7112-01-20Hien Pham1Majestic BeautWest33.95Chantel MimsYanaki188.65Hien Pham1816.50916.09195.652928.24
7212-01-20Chantel Mims40SunsetEast814.3Hien PhamYanaki139.75Janis Figueroa013188.920013188.92
7312-01-20Hien Pham28YanakiNorthWest689.01Chantel MimsQuad1374.05Kiki Sho778.86907.16001686.02
7412-01-20Chantel Mims34Crested BeautEast721.06Janis FigueroaQuad13188.92Tynia Malone0074.85074.85
7512-01-20Hien Pham550SunsetMexico7574.19Chantel MimsSunset6714.56Grand Total5491.6621446.737705.54803.8939447.78
7613-01-20Chantel Mims7QuadMexico312.83Hien PhamSunset916.09
7713-01-20Hien Pham9YanakiMexico251.55Tynia MaloneSunset74.85
7813-01-20Kiki Sho10Crested BeautEast249.5Chantel MimsCrested Beaut934.2top left corner cell format
7913-01-20Chantel Mims250Majestic BeautSouth6291.25Chantel MimsCrested Beaut1746.5(rows\columns) values value field=
8013-01-20Hien Pham9Majestic BeautSouth323.55Hien PhamYanaki55.9kept it in this shape because if we pivot regular arrays, they do not have headers
8113-01-20Tynia Malone375YanakiSouth6242.34Kiki ShoQuad907.16
8214-01-20Tynia Malone425QuadSouth10921.12Chantel MimsCrested Beaut215.6
8314-01-20Hien Pham275QuadNorthWest6908.48Kiki ShoCrested Beaut778.86
8414-01-20Kiki Sho44QuadMexico1596.61Chantel MimsYanaki4419.59
8514-01-20Janis Figueroa1Majestic BeautNorthWest34.95Chantel MimsQuad5976.6
8614-01-20Kiki Sho125Crested BeautEast2183.13
APIVOT post
Cell Formulas
RangeFormula
H1,H69,L68,H55,H46,H37,H28,H19,H10H1=FORMULATEXT(H2)
H2:N8H2=APIVOT(Rev,2,4,6,)
H11:N17H11=APIVOT(Rev,2,4,6,1)
H20:N26H20=APIVOT(Rev,2,4,6,2)
H29:N35H29=APIVOT(Rev,2,4,6,3)
H38:N44H38=APIVOT(Rev,2,4,3,)
H47:P53H47=APIVOT(Rev,4,5,3,)
H56:P62H56=APIVOT(Rev,4,5,6,)
J67J67=XMATCH(H67,Rev[Date],1)
K67K67=XMATCH(I67,Rev[Date],-1)
L69:Q75L69=APIVOT(H70#,1,2,3,)
H70:J85H70=ASELECT(Rev,J67,K67,,,{2,4,6})
Dynamic array formulas.
 
Upvote 0
Yes! That's one of the goals! You beat me to it. :)
I'll try to complete my Lambda function library in this regard, where I will try to avoid recursion, rather than search it out.
That way people can see two schools of thought. :)
You've got a great set of functions, BTW! It takes me considerable time to figure out how they work...
 
Haha, you are too kind! I see no competition here, no prizes. ? We share and we learn from each other.
On the other hand, me, I will always go for recursive solutions . I am fascinated by the paradox that holds, it is complicated because is too simple.
Iterative power will come, it's the future. ?
Somehow , the famous quote of Richard Feynman comes to my mind, has a similar flavor:
"If you think you understand quantum mechanics, you don't understand quantum mechanics.
 
If you were concerned of recursive functions called in APIVOT formula , is no case, because they come into action after the pivot data gets calculated.
The table I have used the formula for, had initially around 2500 rows and everything works fine. ?✌
Unless you pivot array will have 200x200 dimension , which I really doubt it. Any recursive components are safe with already extracted data.
 
Don't worry, I'm not 'concerned' about recursive formulas at all: they are very useful and appear in many fields of interest (e.g. try chaos theory, but to name one).
Furthermore, I'm very confident your formulas work fine and are well equipped for the task they're designed for. (Yes, we all understand the limitations of stack-depth, and all that.)
We also know (you said it quite often) that you are really into recursive formulas: I respect and appreciate that. Not too many people choose that route since it is not the easiest to understand.
Me OTOH, in a similar fashion, I'm into (and quite fascinated by) vectorization, ever since I 'discovered' it in the late 1980's.
So, you can imagine my enthusiasm when I learned about the new –I mean: current– Excel calculation engine and Dynamic Arrays: it's got vectorization written all over it!! :)
And that is why I choose that route: it is my fascination, just like recursion is yours.
Furthermore: for each of us to pursue our own fascination, together we cover more ground for all to discover and learn about.
PS: the 'competition' element I put in here above is not a real competition as such, but more of a stimulus for all of us to build up this Lambda Library, and fill it with many useful, beautiful and powerful functions for everyone to benefit from. Yes, I know: I'm naïve like that. ;) :cool:
 
Vectorization is cool !!!✌?
Only time will tell what will happen. My humble opinion: in a couple of years AI will take over and probably, our formulas will become obsolete. Till then, let's live the illusion that we do something useful.?
 
Finally, today, got the 15-Mar-22 Excel insiders update, the new 14 functions. VSTACK, HSTACK, etc..
A very simple pivot table function, inspired by Mike's latest YT:
WRAPCOL & SORTBY Array Functions to Pivot A Table. "Robustify" your formulas! Excel Magic Trick 1784
Incredible how functions can evolve along with proper powerful new function updates. No need to call any of my custom-made functions. All the old functions will need updating with the new functions.
PIV(r,c,v,[cs],[t]) calls only Excel new build in functions like, MAP, TOROW, VSTACK, HSTACK
r: rows array ; c: columns array : v: values array (1D vertical arrays same dimension)
[cs]: count/sum argument, if omitted or 0, sums, if <>0 or 1, counts
[t]: text header top left cell

Excel Formula: 
=LAMBDA(r,c,v,[cs],[t],
    LET(u,SORT(UNIQUE(r)),q,TOROW(SORT(UNIQUE(c))),w,ROWS(u),l,COLUMNS(q),
        m,MAP(REPT(u,SEQUENCE(,l)^0),REPT(q,SEQUENCE(w)^0),LAMBDA(a,b,SUM((a=r)*(b=c)*IF(cs,1,v)))),
        VSTACK(HSTACK(IF(ISOMITTED(t),"",t),q),HSTACK(u,m))
    )
)
EMT1784-1785.xlsm
ABCDEFGHIJKLMNOPQ
1Note: Used arrays instead of table, unsorted
2cs,omitted => sumscs,omitted => sums
3sample (no dups)=PIV(B5:B19,C5:C19,D5:D19,,"Student/Quiz")=PIV(C5:C19,B5:B19,D5:D19,,"Quiz/Student")
4NamesQuizQuize ScoresStudent/QuizQuiz 1Quiz 2Quiz 3Quiz/StudentChantelDevonteJuneSiouxTimmy
5TimmyQuiz 24Chantel9104Quiz 1955210
6JuneQuiz 32Devonte536Quiz 21034104
7ChantelQuiz 210June542Quiz 346298
8JuneQuiz 24Sioux2109
9SiouxQuiz 12Timmy1048
10DevonteQuiz 15
11ChantelQuiz 34cs,1 => countscs,1 => counts
12DevonteQuiz 36=PIV(B5:B19,C5:C19,D5:D19,1,"Student/Quiz")=PIV(C5:C19,B5:B19,D5:D19,1,"Quiz/Student")
13SiouxQuiz 39Student/QuizQuiz 1Quiz 2Quiz 3Quiz/StudentChantelDevonteJuneSiouxTimmy
14DevonteQuiz 23Chantel111Quiz 111111
15TimmyQuiz 110Devonte111Quiz 211111
16SiouxQuiz 210June111Quiz 311111
17ChantelQuiz 19Sioux111
18TimmyQuiz 38Timmy111
19JuneQuiz 15
20All 1's => there are no dups
21
22cs,omitted => sumscs,omitted => sums
23sample (dups)=PIV(B25:B40,C25:C40,D25:D40,,"Student/Quiz")=PIV(C25:C40,B25:B40,D25:D40,,"Quiz/Student")
24NamesQuizQuize ScoresStudent/QuizQuiz 1Quiz 2Quiz 3Quiz/StudentChantelDevonteJuneSiouxTimmy
25TimmyQuiz 24Chantel9104Quiz 1951005210
26JuneQuiz 32Devonte536Quiz 21034104
27ChantelQuiz 210June100542Quiz 346298
28JuneQuiz 24Sioux2109
29SiouxQuiz 12Timmy1048
30DevonteQuiz 15
31ChantelQuiz 34cs,1 => countscs,1 => counts
32DevonteQuiz 36=PIV(B25:B40,C25:C40,D25:D40,1,"Student/Quiz")=PIV(C25:C40,B25:B40,D25:D40,1,"Quiz/Student")
33SiouxQuiz 39Student/QuizQuiz 1Quiz 2Quiz 3Quiz/StudentChantelDevonteJuneSiouxTimmy
34DevonteQuiz 23Chantel111Quiz 111211
35TimmyQuiz 110Devonte111Quiz 211111
36SiouxQuiz 210June211Quiz 311111
37ChantelQuiz 19Sioux111
38TimmyQuiz 38Timmy111
39JuneQuiz 15
40JuneQuiz 11000
41
42added a record to have also dups
43
Sheet1
Cell Formulas
RangeFormula
F3,K3,F32,K32,F23,K23,F12,K12F3=FORMULATEXT(F4)
F4:I9F4=PIV(B5:B19,C5:C19,D5:D19,,"Student/Quiz")
K4:P7K4=PIV(C5:C19,B5:B19,D5:D19,,"Quiz/Student")
F13:I18F13=PIV(B5:B19,C5:C19,D5:D19,1,"Student/Quiz")
K13:P16K13=PIV(C5:C19,B5:B19,D5:D19,1,"Quiz/Student")
F24:I29F24=PIV(B25:B40,C25:C40,D25:D40,,"Student/Quiz")
K24:P27K24=PIV(C25:C40,B25:B40,D25:D40,,"Quiz/Student")
F33:I38F33=PIV(B25:B40,C25:C40,D25:D40,1,"Student/Quiz")
K33:P36K33=PIV(C25:C40,B25:B40,D25:D40,1,"Quiz/Student")
Dynamic array formulas.
 
PIV alternative, with MAKEARRAY instead of MAP
Excel Formula: 
=LAMBDA(r,c,v,[cs],[t],
    LET(u,SORT(UNIQUE(r)),q,TOROW(SORT(UNIQUE(c))),w,ROWS(u),l,COLUMNS(q),
           m,MAKEARRAY(w,l,LAMBDA(a,b,SUM((INDEX(u,a)=r)*(INDEX(q,b)=c)*IF(cs,1,v)))),
          VSTACK(HSTACK(IF(ISOMITTED(t),"",t),q),HSTACK(u,m))
        )
)
APIVOT.xlsx
ABCDEFGHIJKLMNOPQRS
1PIV Concepts MAP,MAKEARRAY
2rcv
3NamesQuizQuize Scores=SORT(UNIQUE(B4:B19))
4TimmyQuiz 24↓↓↓↓=TOROW(SORT(UNIQUE(C4:C19)))
5JuneQuiz 32rows\clmsQuiz 1Quiz 2Quiz 3q
6ChantelQuiz 210Chantel
7JuneQuiz 24Devonte
8SiouxQuiz 12June
9DevonteQuiz 15Sioux
10ChantelQuiz 34uTimmy
11DevonteQuiz 36
12SiouxQuiz 39MAP
13DevonteQuiz 23If we use 2 arrays arguments with MAP is ideal to "equalize" them.
14TimmyQuiz 110=REPT(G6#,SEQUENCE(,3)^0)=REPT(H5#,SEQUENCE(5)^0)
15SiouxQuiz 210ChantelChantelChantelQuiz 1Quiz 2Quiz 3
16ChantelQuiz 19DevonteDevonteDevonteQuiz 1Quiz 2Quiz 3
17TimmyQuiz 38JuneJuneJuneQuiz 1Quiz 2Quiz 3
18JuneQuiz 15SiouxSiouxSiouxQuiz 1Quiz 2Quiz 3
19JuneQuiz 11000TimmyTimmyTimmyQuiz 1Quiz 2Quiz 3
20
21to countto sum
22=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19))))=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19)*D4:D19)))
231119104
24111536
25211100542
261112109
271111048
28
29MAKEARRAY
30to count
31=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19))))
32111
33111to sum
34211=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19)*D4:D19)))
351119104
36111536
37100542
382109
391048
40
PIV Concept
Cell Formulas
RangeFormula
G3G3=FORMULATEXT(G6)
H4,K14,G14H4=FORMULATEXT(H5)
H5:J5H5=TOROW(SORT(UNIQUE(C4:C19)))
G6:G10G6=SORT(UNIQUE(B4:B19))
G15:I19G15=REPT(G6#,SEQUENCE(,3)^0)
K15:M19K15=REPT(H5#,SEQUENCE(5)^0)
E22,K34,E31E22=FORMULATEXT(G23)
L22L22=FORMULATEXT(M23)
G23:I27G23=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19))))
M23:O27M23=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19)*D4:D19)))
G32:I36G32=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19))))
M35:O39M35=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19)*D4:D19)))
Dynamic array formulas.
 
Love this stuff. I wonder how difficult it would be to allow nested column and row headers. :unsure:
 
realflexyourdata said:
Love this stuff. I wonder how difficult it would be to allow nested column and row headers. :unsure:
Click to expand...
Thanks a lot! These days, Excel has so many versatile tools that nothing is too difficult anymore. Pivot Tables functionality can be reproduced by formulas or custom functions almost entirely.
The question is why to do that? A common answer will be: no refresh needed. We can write functions not only to overcome this drawback, our PT alike lambdas can be designed to work with all spectrum of functions, even with functions that return more than a single result. Hopefully, at the end of the week I will post some. Thanks again. Your feedback is very much appreciated!!
 
You must log in or register to reply here.

Forum statistics

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

Share this page

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