APIVOT

[Xlambda]

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

=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	sample data, courtesy of Mike Girvin			ExcelIsFun				=APIVOT(Rev,2,4,6,)		vf=0	sum					Sum of Net Revenue	Column Labels
2	Date	Sales Rep	Units	Product	Region	Net Revenue		(2\4) 6 vf=0	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total		Row Labels	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
3	01-01-20	Tynia Malone	500	Quad	MidWest	12560.88		Chantel Mims	7659.57	8379.33	28169.71	29746.59	13486.96	87442.16		Chantel Mims	7659.57	8379.33	28169.71	29746.6	13487	87442.16
4	01-01-20	Kiki Sho	38	Yanaki	MidWest	902.79		Hien Pham	7785.00	7809.94	22079.83	9952.88	19538.68	67166.33		Hien Pham	7785	7809.94	22079.83	9952.88	19538.7	67166.33
5	01-01-20	Hien Pham	500	Yanaki	MidWest	8035.63		Janis Figueroa	161.70	1104.42	18189.42	5683.15	3039.75	28178.44		Janis Figueroa	161.7	1104.42	18189.42	5683.15	3039.75	28178.44
6	01-01-20	Chantel Mims	500	Yanaki	West	8035.63		Kiki Sho	9137.04	6161.93	2809.60	5981.80	10598.57	34688.94		Kiki Sho	9137.04	6161.93	2809.6	5981.8	10598.6	34688.94
7	01-01-20	Tynia Malone	39	Quad	East	1481.47		Tynia Malone	948.47	339.50	38904.91	2434.63	14517.63	57145.14		Tynia Malone	948.47	339.5	38904.91	2434.63	14517.6	57145.14
8	01-01-20	Kiki Sho	48	Sunset	MidWest	934.2		Grand Total	25691.78	23795.12	110153.5	53799.05	61181.59	274621.01		Grand Total	25691.78	23795.12	110153.47	53799.1	61181.6	274621.01
9	01-01-20	Hien Pham	13	Quad	Canada	580.97
10	02-01-20	Tynia Malone	27	Yanaki	NorthWest	641.45		=APIVOT(Rev,2,4,6,1)		vf=1	count
11	02-01-20	Hien Pham	13	Sunset	MidWest	337.35		(2\4) 6 vf=1	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
12	02-01-20	Chantel Mims	36	Sunset	MidWest	732.87		Chantel Mims	7	5	6	7	6	31
13	02-01-20	Tynia Malone	44	Quad	South	1596.61		Hien Pham	2	4	5	6	7	24
14	03-01-20	Hien Pham	100	Crested Beaut	MidWest	1816.5		Janis Figueroa	1	2	4	2	1	10
15	03-01-20	Chantel Mims	7	Yanaki	Mexico	188.65		Kiki Sho	5	2	3	4	5	19
16	03-01-20	Hien Pham	5	Yanaki	Mexico	139.75		Tynia Malone	1	1	7	7	3	19
17	03-01-20	Chantel Mims	37	Quad	Mexico	1374.05		Grand Total	16	14	25	26	22	103
18	03-01-20	Janis Figueroa	525	Quad	Mexico	13188.92
19	03-01-20	Chantel Mims	450	Sunset	NorthWest	6714.56		=APIVOT(Rev,2,4,6,2)		vf=2	min					Min of Net Revenue	Column Labels
20	04-01-20	Hien Pham	45	Sunset	Canada	916.09		(2\4) 6 vf=2	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total		Row Labels	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
21	04-01-20	Tynia Malone	3	Sunset	West	74.85		Chantel Mims	215.6	209.7	312.83	639.67	26.95	26.95		Chantel Mims	215.6	209.7	312.83	639.67	26.95	26.95
22	04-01-20	Chantel Mims	48	Crested Beaut	West	934.2		Hien Pham	1816.5	33.95	218.45	24.95	55.9	24.95		Hien Pham	1816.5	33.95	218.45	24.95	55.9	24.95
23	04-01-20	Chantel Mims	100	Crested Beaut	MidWest	1746.5		Janis Figueroa	161.7	34.95	85.38	174.65	3039.75	34.95		Janis Figueroa	161.7	34.95	85.38	174.65	3039.75	34.95
24	04-01-20	Hien Pham	2	Yanaki	South	55.9		Kiki Sho	249.5	305.55	305.83	311.4	57.9	57.9		Kiki Sho	249.5	305.55	305.83	311.4	57.9	57.9
25	04-01-20	Kiki Sho	25	Quad	Mexico	907.16		Tynia Malone	948.47	339.5	298.83	74.85	641.45	74.85		Tynia Malone	948.47	339.5	298.83	74.85	641.45	74.85
26	04-01-20	Chantel Mims	8	Crested Beaut	South	215.6		Grand Total	161.7	33.95	85.38	24.95	26.95	24.95		Grand Total	161.7	33.95	85.38	24.95	26.95	24.95
27	04-01-20	Kiki Sho	34	Crested Beaut	MidWest	778.86
28	04-01-20	Chantel Mims	275	Yanaki	West	4419.59		=APIVOT(Rev,2,4,6,3)		vf=3	max
29	05-01-20	Chantel Mims	200	Quad	East	5976.6		(2\4) 6 vf=3	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
30	05-01-20	Hien Pham	600	Yanaki	East	9297.75		Chantel Mims	2724.75	6291.25	14114.38	7531.78	8035.63	14114.38
31	05-01-20	Janis Figueroa	36	Majestic Beaut	MidWest	1069.47		Hien Pham	5968.5	6291.25	13816.96	7574.19	9297.75	13816.96
32	05-01-20	Chantel Mims	28	Quad	MidWest	1039.82		Janis Figueroa	161.7	1069.47	13188.92	5508.5	3039.75	13188.92
33	05-01-20	Kiki Sho	525	Yanaki	MidWest	8437.41		Kiki Sho	5021.19	5856.38	1596.61	4191.25	8437.41	8437.41
34	05-01-20	Chantel Mims	6	Majestic Beaut	Canada	209.7		Tynia Malone	948.47	339.5	12560.88	934.2	7633.84	12560.88
35	05-01-20	Kiki Sho	9	Majestic Beaut	West	305.55		Grand Total	5968.5	6291.25	14114.38	7574.19	9297.75	14114.38
36	05-01-20	Hien Pham	550	Quad	MidWest	13816.96
37	06-01-20	Kiki Sho	250	Sunset	East	4191.25		=APIVOT(Rev,2,4,3,)								Sum of Units2	Column Labels
38	06-01-20	Tynia Malone	7	Quad	Canada	298.83		(2\4) 3 vf=0	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total		Row Labels	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
39	06-01-20	Chantel Mims	150	Crested Beaut	Canada	2724.75		Chantel Mims	401	321	1022	2005	817	4566		Chantel Mims	401	321	1022	2005	817	4566
40	06-01-20	Tynia Malone	43	Crested Beaut	MidWest	948.47		Hien Pham	500	298	856	660	1189	3503		Hien Pham	500	298	856	660	1189	3503
41	07-01-20	Kiki Sho	21	Sunset	NorthWest	544.95		Janis Figueroa	6	37	677	407	150	1277		Janis Figueroa	6	37	677	407	150	1277
42	07-01-20	Hien Pham	13	Quad	West	554.97		Kiki Sho	560	309	76	331	614	1890		Kiki Sho	560	309	76	331	614	1890
43	07-01-20	Tynia Malone	24	Sunset	West	529.38		Tynia Malone	43	10	1471	111	877	2512		Tynia Malone	43	10	1471	111	877	2512
44	07-01-20	Tynia Malone	15	Sunset	Mexico	389.25		Grand Total	1510	975	4102	3514	3647	13748		Grand Total	1510	975	4102	3514	3647	13748
45	08-01-20	Kiki Sho	2	Yanaki	MidWest	57.9
46	08-01-20	Tynia Malone	4	Sunset	NorthWest	99.8		=APIVOT(Rev,4,5,3,)										Sum of Units	Column Labels
47	08-01-20	Kiki Sho	42	Yanaki	East	997.82		(4\5) 3 vf=0	Canada	East	Mexico	MidWest	NorthWest	South	West	Grand Total		Row Labels	Canada	East	Mexico	MidWest	NorthWest	South	West	Grand Total
48	08-01-20	Chantel Mims	500	Sunset	East	7460.63		Crested Beaut	150	169	411	283	0	408	89	1510		Crested Beaut	150	169	411	283		408	89	1510
49	08-01-20	Hien Pham	38	Majestic Beaut	East	1161.19		Majestic Beaut	15	38	10	336	57	259	260	975		Majestic Beaut	15	38	10	336	57	259	260	975
50	08-01-20	Tynia Malone	48	Sunset	Mexico	934.2		Quad	170	241	638	1078	275	1255	445	4102		Quad	170	241	638	1078	275	1255	445	4102
51	08-01-20	Chantel Mims	29	Sunset	South	639.67		Sunset	55	802	664	497	482	29	985	3514		Sunset	55	802	664	497	482	29	985	3514
52	09-01-20	Hien Pham	45	Yanaki	Canada	1069.09		Yanaki	45	824	21	1065	539	377	776	3647		Yanaki	45	824	21	1065	539	377	776	3647
53	09-01-20	Tynia Malone	10	Sunset	Canada	239.5		Grand Total	435	2074	1744	3259	1353	2328	2555	13748		Grand Total	435	2074	1744	3259	1353	2328	2555	13748
54	09-01-20	Kiki Sho	7	Quad	West	305.83
55	09-01-20	Janis Figueroa	6	Crested Beaut	MidWest	161.7		=APIVOT(Rev,4,5,6,)										Sum of Net Revenue	Column Labels
56	09-01-20	Chantel Mims	1	Yanaki	West	26.95		(4\5) 6 vf=0	Canada	East	Mexico	MidWest	NorthWest	South	West	Grand Total		Row Labels	Canada	East	Mexico	MidWest	NorthWest	South	West	Grand Total
57	09-01-20	Tynia Malone	475	Yanaki	NorthWest	7633.84		Crested Beaut	2724.75	3153.69	6338.65	5452.03	0	6184.1	1838.56	25691.78		Crested Beaut	2724.75	3153.69	6338.65	5452.03		6184.1	1838.56	25691.78
58	09-01-20	Chantel Mims	33	Crested Beaut	Mexico	699.85		Majestic Beaut	524.25	1161.19	339.5	6925.85	1598.78	6614.8	6630.75	23795.12		Majestic Beaut	524.25	1161.19	339.5	6925.85	1598.78	6614.8	6630.75	23795.12
59	09-01-20	Janis Figueroa	75	Quad	Canada	2457.56		Quad	5794.92	7543.45	17379.57	27417.66	6908.48	33327.47	11781.9	110153.47		Quad	5794.92	7543.45	17379.6	27417.66	6908.48	33327.5	11781.9	110153.47
60	09-01-20	Chantel Mims	50	Majestic Beaut	NorthWest	1348.13		Sunset	1155.59	12777.58	9997.94	7512.92	7533.96	639.67	14181.4	53799.05		Sunset	1155.59	12777.6	9997.94	7512.92	7533.96	639.67	14181.4	53799.05
61	10-01-20	Janis Figueroa	7	Sunset	NorthWest	174.65		Yanaki	1069.09	14095.56	579.95	17433.73	9222.85	6298.24	12482.2	61181.59		Yanaki	1069.09	14095.6	579.95	17433.73	9222.85	6298.24	12482.2	61181.59
62	10-01-20	Chantel Mims	575	Quad	South	14114.38		Grand Total	11268.6	38731.47	34635.61	64742.19	25264.07	53064.28	46914.8	274621.01		Grand Total	11268.6	38731.5	34635.6	64742.19	25264.07	53064.3	46914.8	274621.01
63	10-01-20	Janis Figueroa	150	Yanaki	East	3039.75
64	11-01-20	Kiki Sho	300	Majestic Beaut	MidWest	5856.38
65	11-01-20	Janis Figueroa	400	Sunset	MidWest	5508.5		dynamic APIVOT interface using		ASELECT	(proves that APIVOT works with dynamic arrays,no need of tables, no refresh)
66	11-01-20	Chantel Mims	32	Yanaki	East	760.24		date 1	date 2	row start	row end
67	11-01-20	Chantel Mims	28	Crested Beaut	Mexico	617.61		03-01-20	05-01-20	12	27
68	11-01-20	Kiki Sho	41	Crested Beaut	West	904.36						=APIVOT(H70#,1,2,3,)
69	11-01-20	Chantel Mims	425	Sunset	West	5852.78		=ASELECT(Rev,J67,K67,,,{2,4,6})				(1\2) 3 vf=0	Crested Beaut	Quad	Sunset	Yanaki	Grand Total
70	12-01-20	Chantel Mims	6	Majestic Beaut	NorthWest	215.7		Hien Pham	Crested Beaut	1816.5		Chantel Mims	2896.3	7350.65	6714.56	4608.24	21569.75
71	12-01-20	Hien Pham	1	Majestic Beaut	West	33.95		Chantel Mims	Yanaki	188.65		Hien Pham	1816.5	0	916.09	195.65	2928.24
72	12-01-20	Chantel Mims	40	Sunset	East	814.3		Hien Pham	Yanaki	139.75		Janis Figueroa	0	13188.92	0	0	13188.92
73	12-01-20	Hien Pham	28	Yanaki	NorthWest	689.01		Chantel Mims	Quad	1374.05		Kiki Sho	778.86	907.16	0	0	1686.02
74	12-01-20	Chantel Mims	34	Crested Beaut	East	721.06		Janis Figueroa	Quad	13188.92		Tynia Malone	0	0	74.85	0	74.85
75	12-01-20	Hien Pham	550	Sunset	Mexico	7574.19		Chantel Mims	Sunset	6714.56		Grand Total	5491.66	21446.73	7705.5	4803.89	39447.78
76	13-01-20	Chantel Mims	7	Quad	Mexico	312.83		Hien Pham	Sunset	916.09
77	13-01-20	Hien Pham	9	Yanaki	Mexico	251.55		Tynia Malone	Sunset	74.85
78	13-01-20	Kiki Sho	10	Crested Beaut	East	249.5		Chantel Mims	Crested Beaut	934.2		top left corner cell format
79	13-01-20	Chantel Mims	250	Majestic Beaut	South	6291.25		Chantel Mims	Crested Beaut	1746.5		(rows\columns) values value field=
80	13-01-20	Hien Pham	9	Majestic Beaut	South	323.55		Hien Pham	Yanaki	55.9		kept it in this shape because if we pivot regular arrays, they do not have headers
81	13-01-20	Tynia Malone	375	Yanaki	South	6242.34		Kiki Sho	Quad	907.16
82	14-01-20	Tynia Malone	425	Quad	South	10921.12		Chantel Mims	Crested Beaut	215.6
83	14-01-20	Hien Pham	275	Quad	NorthWest	6908.48		Kiki Sho	Crested Beaut	778.86
84	14-01-20	Kiki Sho	44	Quad	Mexico	1596.61		Chantel Mims	Yanaki	4419.59
85	14-01-20	Janis Figueroa	1	Majestic Beaut	NorthWest	34.95		Chantel Mims	Quad	5976.6
86	14-01-20	Kiki Sho	125	Crested Beaut	East	2183.13
APIVOT post

Cell Formulas
Range	Formula
H1,H69,L68,H55,H46,H37,H28,H19,H10	H1	=FORMULATEXT(H2)
H2:N8	H2	=APIVOT(Rev,2,4,6,)
H11:N17	H11	=APIVOT(Rev,2,4,6,1)
H20:N26	H20	=APIVOT(Rev,2,4,6,2)
H29:N35	H29	=APIVOT(Rev,2,4,6,3)
H38:N44	H38	=APIVOT(Rev,2,4,3,)
H47:P53	H47	=APIVOT(Rev,4,5,3,)
H56:P62	H56	=APIVOT(Rev,4,5,6,)
J67	J67	=XMATCH(H67,Rev[Date],1)
K67	K67	=XMATCH(I67,Rev[Date],-1)
L69:Q75	L69	=APIVOT(H70#,1,2,3,)
H70:J85	H70	=ASELECT(Rev,J67,K67,,,{2,4,6})
Dynamic array formulas.

 

[GeertD]

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

 

[Xlambda]

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.

 

[Xlambda]

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.

 

[GeertD]

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.

 

[Xlambda]

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.?

 

[Xlambda]

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

=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Note: Used arrays instead of table, unsorted
2						cs,omitted => sums					cs,omitted => sums
3		sample (no dups)				=PIV(B5:B19,C5:C19,D5:D19,,"Student/Quiz")					=PIV(C5:C19,B5:B19,D5:D19,,"Quiz/Student")
4		Names	Quiz	Quize Scores		Student/Quiz	Quiz 1	Quiz 2	Quiz 3		Quiz/Student	Chantel	Devonte	June	Sioux	Timmy
5		Timmy	Quiz 2	4		Chantel	9	10	4		Quiz 1	9	5	5	2	10
6		June	Quiz 3	2		Devonte	5	3	6		Quiz 2	10	3	4	10	4
7		Chantel	Quiz 2	10		June	5	4	2		Quiz 3	4	6	2	9	8
8		June	Quiz 2	4		Sioux	2	10	9
9		Sioux	Quiz 1	2		Timmy	10	4	8
10		Devonte	Quiz 1	5
11		Chantel	Quiz 3	4		cs,1 => counts					cs,1 => counts
12		Devonte	Quiz 3	6		=PIV(B5:B19,C5:C19,D5:D19,1,"Student/Quiz")					=PIV(C5:C19,B5:B19,D5:D19,1,"Quiz/Student")
13		Sioux	Quiz 3	9		Student/Quiz	Quiz 1	Quiz 2	Quiz 3		Quiz/Student	Chantel	Devonte	June	Sioux	Timmy
14		Devonte	Quiz 2	3		Chantel	1	1	1		Quiz 1	1	1	1	1	1
15		Timmy	Quiz 1	10		Devonte	1	1	1		Quiz 2	1	1	1	1	1
16		Sioux	Quiz 2	10		June	1	1	1		Quiz 3	1	1	1	1	1
17		Chantel	Quiz 1	9		Sioux	1	1	1
18		Timmy	Quiz 3	8		Timmy	1	1	1
19		June	Quiz 1	5
20						All 1's => there are no dups
21
22						cs,omitted => sums					cs,omitted => sums
23		sample (dups)				=PIV(B25:B40,C25:C40,D25:D40,,"Student/Quiz")					=PIV(C25:C40,B25:B40,D25:D40,,"Quiz/Student")
24		Names	Quiz	Quize Scores		Student/Quiz	Quiz 1	Quiz 2	Quiz 3		Quiz/Student	Chantel	Devonte	June	Sioux	Timmy
25		Timmy	Quiz 2	4		Chantel	9	10	4		Quiz 1	9	5	1005	2	10
26		June	Quiz 3	2		Devonte	5	3	6		Quiz 2	10	3	4	10	4
27		Chantel	Quiz 2	10		June	1005	4	2		Quiz 3	4	6	2	9	8
28		June	Quiz 2	4		Sioux	2	10	9
29		Sioux	Quiz 1	2		Timmy	10	4	8
30		Devonte	Quiz 1	5
31		Chantel	Quiz 3	4		cs,1 => counts					cs,1 => counts
32		Devonte	Quiz 3	6		=PIV(B25:B40,C25:C40,D25:D40,1,"Student/Quiz")					=PIV(C25:C40,B25:B40,D25:D40,1,"Quiz/Student")
33		Sioux	Quiz 3	9		Student/Quiz	Quiz 1	Quiz 2	Quiz 3		Quiz/Student	Chantel	Devonte	June	Sioux	Timmy
34		Devonte	Quiz 2	3		Chantel	1	1	1		Quiz 1	1	1	2	1	1
35		Timmy	Quiz 1	10		Devonte	1	1	1		Quiz 2	1	1	1	1	1
36		Sioux	Quiz 2	10		June	2	1	1		Quiz 3	1	1	1	1	1
37		Chantel	Quiz 1	9		Sioux	1	1	1
38		Timmy	Quiz 3	8		Timmy	1	1	1
39		June	Quiz 1	5
40		June	Quiz 1	1000
41
42		added a record to have also dups
43
Sheet1

Cell Formulas
Range	Formula
F3,K3,F32,K32,F23,K23,F12,K12	F3	=FORMULATEXT(F4)
F4:I9	F4	=PIV(B5:B19,C5:C19,D5:D19,,"Student/Quiz")
K4:P7	K4	=PIV(C5:C19,B5:B19,D5:D19,,"Quiz/Student")
F13:I18	F13	=PIV(B5:B19,C5:C19,D5:D19,1,"Student/Quiz")
K13:P16	K13	=PIV(C5:C19,B5:B19,D5:D19,1,"Quiz/Student")
F24:I29	F24	=PIV(B25:B40,C25:C40,D25:D40,,"Student/Quiz")
K24:P27	K24	=PIV(C25:C40,B25:B40,D25:D40,,"Quiz/Student")
F33:I38	F33	=PIV(B25:B40,C25:C40,D25:D40,1,"Student/Quiz")
K33:P36	K33	=PIV(C25:C40,B25:B40,D25:D40,1,"Quiz/Student")
Dynamic array formulas.

 

[Xlambda]

PIV alternative, with MAKEARRAY instead of MAP

=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1							PIV Concepts MAP,MAKEARRAY
2		r	c	v
3		Names	Quiz	Quize Scores			=SORT(UNIQUE(B4:B19))
4		Timmy	Quiz 2	4			↓↓↓↓	=TOROW(SORT(UNIQUE(C4:C19)))
5		June	Quiz 3	2			rows\clms	Quiz 1	Quiz 2	Quiz 3	q
6		Chantel	Quiz 2	10			Chantel
7		June	Quiz 2	4			Devonte
8		Sioux	Quiz 1	2			June
9		Devonte	Quiz 1	5			Sioux
10		Chantel	Quiz 3	4		u	Timmy
11		Devonte	Quiz 3	6
12		Sioux	Quiz 3	9			MAP
13		Devonte	Quiz 2	3			If we use 2 arrays arguments with MAP is ideal to "equalize" them.
14		Timmy	Quiz 1	10			=REPT(G6#,SEQUENCE(,3)^0)				=REPT(H5#,SEQUENCE(5)^0)
15		Sioux	Quiz 2	10			Chantel	Chantel	Chantel		Quiz 1	Quiz 2	Quiz 3
16		Chantel	Quiz 1	9			Devonte	Devonte	Devonte		Quiz 1	Quiz 2	Quiz 3
17		Timmy	Quiz 3	8			June	June	June		Quiz 1	Quiz 2	Quiz 3
18		June	Quiz 1	5			Sioux	Sioux	Sioux		Quiz 1	Quiz 2	Quiz 3
19		June	Quiz 1	1000			Timmy	Timmy	Timmy		Quiz 1	Quiz 2	Quiz 3
20
21							to count						to 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)))
23							1	1	1				9	10	4
24							1	1	1				5	3	6
25							2	1	1				1005	4	2
26							1	1	1				2	10	9
27							1	1	1				10	4	8
28
29							MAKEARRAY
30							to count
31					=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19))))
32							1	1	1
33							1	1	1				to sum
34							2	1	1		=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19)*D4:D19)))
35							1	1	1				9	10	4
36							1	1	1				5	3	6
37													1005	4	2
38													2	10	9
39													10	4	8
40
PIV Concept

Cell Formulas
Range	Formula
G3	G3	=FORMULATEXT(G6)
H4,K14,G14	H4	=FORMULATEXT(H5)
H5:J5	H5	=TOROW(SORT(UNIQUE(C4:C19)))
G6:G10	G6	=SORT(UNIQUE(B4:B19))
G15:I19	G15	=REPT(G6#,SEQUENCE(,3)^0)
K15:M19	K15	=REPT(H5#,SEQUENCE(5)^0)
E22,K34,E31	E22	=FORMULATEXT(G23)
L22	L22	=FORMULATEXT(M23)
G23:I27	G23	=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19))))
M23:O27	M23	=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19)*D4:D19)))
G32:I36	G32	=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19))))
M35:O39	M35	=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19)*D4:D19)))
Dynamic array formulas.

 

[realflexyourdata]

Love this stuff. I wonder how difficult it would be to allow nested column and row headers.

 

[Xlambda]

Love this stuff. I wonder how difficult it would be to allow nested column and row headers.

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