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.

 

[mgirvin]

As the great @Aladin Akyurek always says: Let's robust-ify!!!!

 

[Xlambda]

TV Text to values function and concept
TV(a) a: any array of text

=LAMBDA(a,IF(IFERROR(EXACT(--a,a),0),--a,IF(EXACT(a,TRUE),TRUE,IF(EXACT(a, FALSE),FALSE,a))))

EMT 1875 pivby trick.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Concept: TV function (Text to Values) (simplest form, have one that gives you the options to select what data types to keep or convert, working also with type 128 data)
3
4		After a TEXTSPLIT operation or after applying CLEAN or after an ARRAYTOTEXT operation to a single element, everything will turn into text.
5		A function that will convert back the text values to their initial format comes handy. But it has to be accurate.
6											trick I've being using always, most of the scenarios
7						sometimes CLEAN can save lives, imagine we need it					to turn back text numbers to numbers and keep text as text
8
9						=CLEAN(C10:C22)		now everything is text			=IFERROR(--F10#,F10#)
10	*	text as valid nr.	004356			004356		=AND(ISTEXT(F10#))			4356
11	*	text as valid nr.	214,315.23			214,315.23		TRUE			214315.23
12		text digits pattern	234-546-8790			234-546-8790					234-546-8790		formats not as initial
13		logical	TRUE			TRUE		Note: When dates in			TRUE
14		logical	FALSE			FALSE		façade date format			FALSE
15		text looks like logical	True			True		CLEAN will turn them to nr.			True
16		text looks like logical	false			false					false
17		nr as number	34			34		09-03-25			34
18		nr. As number	-23			-23					-23
19		text	text			text		=CLEAN(H17)			text
20	*	date as text, EU valid date format	09-Mar-2025			09-Mar-2025		45725			45725
21	*	date as text, US valid date format	Mar-09-2025			Mar-09-2025					Mar-09-2025
22		date as text, no valid date format	Sun Mar-9-25			Sun Mar-9-25					Sun Mar-9-25
23
24	*	"valid" meaning =>a double minus -- op will turn them into numbers
25
26						To keep the format was before accurate we use TV
27
28						=TV(F10#)			concept explanation:
29						004356
30						214,315.23			we apply dbl minus -- only to numbers that are exact numbers in text format
31						234-546-8790
32						TRUE						=EXACT(J33,I33)
33						FALSE			0032	32		FALSE	=> will remain text
34						True			32	32		TRUE	=> will apply the -- to turn into nr.
35						false						=EXACT(J34,I34)
36						34			same with TRUE, FALSE
37						-23						=EXACT(I38,FALSE)
38						text			FALSE			TRUE	=> will keep as logical FALSE
39						09-Mar-2025			True			FALSE	=> will remain text
40						Mar-09-2025						=EXACT(I39,TRUE)
41						Sun Mar-9-25
42
43						all formats as initial format before CLEAN
44
Sheet3

Cell Formulas
Range	Formula
K9,L37,L32,F28,H19,H10,F9	K9	=FORMULATEXT(K10)
F10:F22	F10	=CLEAN(C10:C22)
K10:K22	K10	=IFERROR(--F10#,F10#)
H11	H11	=AND(ISTEXT(F10#))
H20	H20	=CLEAN(H17)
F29:F41	F29	=TV(F10#)
J33:J34	J33	=--I33:I34
L33:L34	L33	=EXACT(J33,I33)
L35,L40	L35	=FORMULATEXT(L34)
L38	L38	=EXACT(I38,FALSE)
L39	L39	=EXACT(I39,TRUE)
Dynamic array formulas.