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.

 

[Xlambda]

I tried before to post max Order amount/Salesperson for each month and year, but because the result was too wide for the mini-sheet (large names) I didn't.
I came up with the idea to split the names and to display only the surnames with ATEXTSPLIT and works on both directions, as rows and column values. TEXTSPLIT does not work because it cannot split arrays.
For amounts, to make them shorter, used INT.

LHPIVOT.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
1	sample table 600 rows
2	Salesperson	Order ID	Order date	Order amount				Ordered by Salesperson's surnames
3	Jack Potter	3284	04-01-21	4686.244				Max Order amount/Salesperson for each month and year (integers)
4	Bob Caldwell	3285	04-01-21	17702.13				=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),S[Order amount],LAMBDA(x,INT(MAX(x))))
5	Bob Caldwell	3286	05-01-21	8563.469				PT	Caldwell	Chowdhry	Code	Grosse	Hinton	Hoffer	Hollinworth	Johnson	Lamble	Onslow	Potter	Shand	Smith	Trefl	Woodcock	GT
6	Taj Shand	3287	06-01-21	20481.33				21-(01)-Jan	17873		8091		23250	21544	19249	15420	23132	18854	15780	23344	21212	21285	18023	23344
7	Jack Potter	3288	09-01-21	15780.99				21-(02)-Feb	21596	19444	14907	22969	24304	23031	13880	16211	16216	20749	17106	23126	17332	18814	24965	24965
8	Justine Hoffer	3289	11-01-21	7270.795				21-(03)-Mar	22231	9632	19659	20532	12258	14983	11289	18865	23519	24219	12099	19766	21983	23842	24517	24517
9	Sheridan Smith	3290	11-01-21	5840.169				21-(04)-Apr	4589	24585	13984	22632		20359	23855	5900		23041	23927	18435	12571	18338	12983	24585
10	Taj Shand	3291	11-01-21	15376.81				21-(05)-May	12062	19045	23970	14546		15393	5654	22518	9768	4745	11399	18349	10239	19514	18848	23970
11	Archer Lamble	3292	12-01-21	23132.98				21-(06)-Jun	7925	15852	5236	16507	18785		20710	8107	22514	11846	17810	17553	24594	22697	17157	24594
12	Hudson Hollinworth	3293	12-01-21	19249.78				21-(07)-Jul	15008	23903	21986	23942	17984	24720	11175	22152	22332	7810		15745	19459	24162	14656	24720
13	Hudson Onslow	3294	12-01-21	18854.76				21-(08)-Aug	5385	12107	24004	23602	8500	23512	22140	10880	8221	24100	20566	15811	9017		16540	24100
14	Justine Hoffer	3295	12-01-21	21544.81				21-(09)-Sep	22280	23227		23261	21103	17902	23455	22803	20284	23090	8809	19601	22681	10304	22982	23455
15	Taj Shand	3296	13-01-21	23344.87				21-(10)-Oct	5746	13706	20147	19368	23489	19055	23697	11890	10239	20674	23481	21661	13804	24939	22390	24939
16	Don Johnson	3297	15-01-21	15420.93				21-(11)-Nov	15998	24245	13755	8443	19680	24179	20375	21884	23014		22485	24282	24714	21808	14996	24714
17	Amy Trefl	3298	16-01-21	15300.26				21-(12)-Dec	22785	23455	20777	18099	23868	23874	12195	22386	22008	23495	4279	23277	23198	17051	20515	23874
18	Lily Code	3299	18-01-21	8091.069				22-(01)-Jan	15660	21813	22517	22836	17982	24197	23914	17463	19602	22139	23864	23916	23581	24782	22439	24782
19	Hudson Onslow	3300	18-01-21	16146.71				22-(02)-Feb	16128		14644	24999	21777	16537	22391	23445	17285	16635	12823	9119	8400	24097		24999
20	Bob Caldwell	3301	18-01-21	5858.817				GT	22785	24585	24004	24999	24304	24720	23914	23445	23519	24219	23927	24282	24714	24939	24965	24999
21	Kayla Woodcock	3302	19-01-21	18023.42
22	Sheridan Smith	3303	20-01-21	21212.96		Ordered by surnames
23	Hudson Hollinworth	3304	21-01-21	6270.728		Top 2 Orders amount/Sales person all time (integers)
24	Archer Lamble	3305	21-01-21	11684.54		=APIVOT(INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),,S[Order amount],LAMBDA(x,INT(LARGE(x,SEQUENCE(2)))))
25	Sophia Hinton	3306	21-01-21	23250.87		PT	GT
26	Hudson Onslow	3307	21-01-21	5584.412		Caldwell	22785, 22280
27	Hudson Hollinworth	3308	21-01-21	18973.51		Chowdhry	24585, 24245
28	Kayla Woodcock	3309	23-01-21	15544.88		Code	24004, 23970
29	Sophia Hinton	3310	25-01-21	9941.139		Grosse	24999, 23942
30	Lily Code	3311	25-01-21	5418.415		Hinton	24304, 23868
31	Hudson Onslow	3312	26-01-21	16683.98		Hoffer	24720, 24197
32	Amy Trefl	3313	28-01-21	21285.61		Hollinworth	23914, 23855
33	Bob Caldwell	3314	28-01-21	17873.08		Johnson	23445, 22803
34	Amy Trefl	3315	29-01-21	18233.58		Lamble	23519, 23132
35	Amy Trefl	3316	29-01-21	20485.99		Onslow	24219, 24100
36	Archer Lamble	3317	29-01-21	10979.77		Potter	23927, 23864
37	Hudson Hollinworth	3318	29-01-21	16994.46		Shand	24282, 24151
38	Justine Hoffer	3319	30-01-21	9521.601		Smith	24714, 24594
39	Bob Caldwell	3320	30-01-21	9560.26		Trefl	24939, 24782
40	Sheridan Smith	3321	30-01-21	14020.61		Woodcock	24965, 24517
41	Bob Caldwell	3322	30-01-21	13538.7		GT	24999, 24965
42	Bob Caldwell	3323	01-02-21	21596.07
43	Sheridan Smith	3324	01-02-21	8228.796
PIV 11

Cell Formulas
Range	Formula
H4,F24	H4	=FORMULATEXT(H5)
H5:X20	H5	=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),S[Order amount],LAMBDA(x,INT(MAX(x))))
F25:G41	F25	=APIVOT(INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),,S[Order amount],LAMBDA(x,INT(LARGE(x,SEQUENCE(2)))))
Dynamic array formulas.

 

[Xlambda]

Cool challenge, today's ExcelIsFun YT (31May22) : Unique List of Case Sensitive Items? Expandable Range in Spilled Array Formula? EMT 1792
Will see how to solve it with APIVOT, but first , as stand-alone task, 2 solutions.
1st solution: expandable ranges
Formula, single cell G21 :

=LET(a,P[Product],
         m,MAP(SEQUENCE(ROWS(a)),a,LAMBDA(x,y,SUM(--EXACT(INDEX(a,SEQUENCE(x)),y)))),
         f,FILTER(a,m=1),
         b,BYROW(f,LAMBDA(x,SUM(--(EXACT(x,a))))),
         VSTACK({"Product","Count"},HSTACK(f,b))
)

EMT1792.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Product	Sales		Sol 1. Expandable range
3		Carlota	4
4		CARLOTA	9		step 1
5		Carlota	7		=MAP(SEQUENCE(ROWS(P)),P[Product],LAMBDA(x,y,SUM(--EXACT(INDEX(P[Product],SEQUENCE(x)),y))))
6		Sunset	7		↓↓↓
7		SUNSET	6		1		step 2
8		Carlota	4		1		=FILTER(P[Product],E7#=1)
9		CARLOTA	4		2		↓↓↓
10		Aspen	4		1		↓↓↓		step 3
11		Aspen	6		1		↓↓↓		=BYROW(G12#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))
12		ASPEN	1		3		Carlota		3
13		CARLOTA	11		2		CARLOTA		3
14					1		Sunset		1
15					2		SUNSET		1
16					1		Aspen		2
17					3		ASPEN		1
18
19							step 4, single cell
20
21							Product	Count
22							Carlota	3
23							CARLOTA	3
24							Sunset	1
25							SUNSET	1
26							Aspen	2
27							ASPEN	1
28
29
1792

Cell Formulas
Range	Formula
E5	E5	=FORMULATEXT(E7)
E7:E17	E7	=MAP(SEQUENCE(ROWS(P)),P[Product],LAMBDA(x,y,SUM(--EXACT(INDEX(P[Product],SEQUENCE(x)),y))))
G8	G8	=FORMULATEXT(G12)
I11	I11	=FORMULATEXT(I12)
G12:G17	G12	=FILTER(P[Product],E7#=1)
I12:I17	I12	=BYROW(G12#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))
G21:H27	G21	=LET(a,P[Product], m,MAP(SEQUENCE(ROWS(a)),a,LAMBDA(x,y,SUM(--EXACT(INDEX(a,SEQUENCE(x)),y)))), f,FILTER(a,m=1),b,BYROW(f,LAMBDA(x,SUM(--(EXACT(x,a))))), VSTACK({"Product","Count"},HSTACK(f,b)))
Dynamic array formulas.

 

[Xlambda]

2nd solution, non-expandable range, embedded lambda to replace 2 BYROWs.
Formula, single cell B29:

=LET(p,P[Product],
         t,TOROW(p),a,EXACT(p,t),u,--UNIQUE(a),
         b,LAMBDA([i],BYROW(u,LAMBDA(x,LET(y,XLOOKUP(1,x,t),z,SUM(x),IF(i,z,y))))),
         VSTACK({"Product","Count"},HSTACK(b(),b(1)))
)

EMT1792.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	AB
1					Sol 2. non expandable range
2		Product	Sales		step 1
3		Carlota	4		=--EXACT(P[Product],TOROW(P[Product]))
4		CARLOTA	9		1	0	1	0	0	1	0	0	0	0	0
5		Carlota	7		0	1	0	0	0	0	1	0	0	0	1
6		Sunset	7		1	0	1	0	0	1	0	0	0	0	0
7		SUNSET	6		0	0	0	1	0	0	0	0	0	0	0
8		Carlota	4		0	0	0	0	1	0	0	0	0	0	0
9		CARLOTA	4		1	0	1	0	0	1	0	0	0	0	0
10		Aspen	4		0	1	0	0	0	0	1	0	0	0	1
11		Aspen	6		0	0	0	0	0	0	0	1	1	0	0
12		ASPEN	1		0	0	0	0	0	0	0	1	1	0	0
13		CARLOTA	11		0	0	0	0	0	0	0	0	0	1	0
14					0	1	0	0	0	0	1	0	0	0	1		step 3
15																	=BYROW(E18#,LAMBDA(x,XLOOKUP(1,x,TOROW(P[Product]))))
16					step 2												↓↓↓		step 4
17					=UNIQUE(E4#)												↓↓↓		=BYROW(Q18#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))
18					1	0	1	0	0	1	0	0	0	0	0		Carlota		3
19					0	1	0	0	0	0	1	0	0	0	1		CARLOTA		3
20					0	0	0	1	0	0	0	0	0	0	0		Sunset		1
21					0	0	0	0	1	0	0	0	0	0	0		SUNSET		1
22					0	0	0	0	0	0	0	1	1	0	0		Aspen		2
23					0	0	0	0	0	0	0	0	0	1	0		ASPEN		1
24
25																	instead of 2 BYROWS we can use only 1, b([ i ])
26																	=b()
27		single cell															=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))()
28																	↓↓↓		=b(1)
29		Product	Count														↓↓↓		=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))(1)
30		Carlota	3														Carlota		3
31		CARLOTA	3														CARLOTA		3
32		Sunset	1														Sunset		1
33		SUNSET	1														SUNSET		1
34		Aspen	2														Aspen		2
35		ASPEN	1														ASPEN		1
36
1792

Cell Formulas
Range	Formula
E3,S29,S17,E17	E3	=FORMULATEXT(E4)
E4:O14	E4	=--EXACT(P[Product],TOROW(P[Product]))
Q15,Q27	Q15	=FORMULATEXT(Q18)
E18:O23	E18	=UNIQUE(E4#)
Q18:Q23	Q18	=BYROW(E18#,LAMBDA(x,XLOOKUP(1,x,TOROW(P[Product]))))
S18:S23	S18	=BYROW(Q18#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))
B29:C35	B29	=LET(p,P[Product], t,TOROW(p),a,EXACT(p,t),u,--UNIQUE(a), b,LAMBDA([i],BYROW(u,LAMBDA(x,LET(y,XLOOKUP(1,x,t),z,SUM(x),IF(i,z,y))))), VSTACK({"Product","Count"},HSTACK(b(),b(1))) )
Q30:Q35	Q30	=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))()
S30:S35	S30	=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))(1)
Dynamic array formulas.

 

[Xlambda]

Solution with APIVOT
Again, the advantage that APIVOT can handle as arguments array calculations.
We could have changed PIVOT to handle these types of scenarios, but because it is not too common in real life, whenever we need it, we can adapt a formula to every specific case.

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1							appending a "_" at the end of the UPPER ones
2		Product	Sales				=MAP(P[Product],LAMBDA(x,IF(EXACT(XLOOKUP(x,E4#,E4#),x),x,x&"_")))
3		Carlota	4		=UNIQUE(P[Product])
4		CARLOTA	9		Carlota		Carlota		=APIVOT(G4#,,P[Sales],LAMBDA(x,COUNT(x)),"Count")
5		Carlota	7		Sunset		CARLOTA_		Count	GT
6		Sunset	7		Aspen		Carlota		Aspen	2		=APIVOT(G4#,,P[Sales],LAMBDA(x,SUM(x)),"Sum")
7		SUNSET	6				Sunset		ASPEN_	1		Sum	GT
8		Carlota	4				SUNSET_		Carlota	3		Aspen	10
9		CARLOTA	4				Carlota		CARLOTA_	3		ASPEN_	1
10		Aspen	4				CARLOTA_		Sunset	1		Carlota	15
11		Aspen	6				Aspen		SUNSET_	1		CARLOTA_	24
12		ASPEN	1				Aspen		GT	11		Sunset	7
13		CARLOTA	11				ASPEN_					SUNSET_	6
14							CARLOTA_					GT	63
15
16									real life
17									=APIVOT(P[Product],,P[Sales],LAMBDA(x,COUNT(x)),"Count")
18									Count	GT
19									Aspen	3		=APIVOT(P[Product],,P[Sales],LAMBDA(x,SUM(x)),"Sum")
20									Carlota	6		Sum	GT
21									Sunset	2		Aspen	11
22									GT	11		Carlota	39
23												Sunset	13
24												GT	63
25
26									=APIVOT(P[Product],,P[Sales],LAMBDA(x,AVERAGE(x)),"Avrg")
27									Avrg	GT
28									Aspen	3.666667
29									Carlota	6.5
30									Sunset	6.5
31									GT	5.727273
32
PIV 12

Cell Formulas
Range	Formula
G2	G2	=FORMULATEXT(G4)
E3,I26,L19,I17,L6,I4	E3	=FORMULATEXT(E4)
E4:E6	E4	=UNIQUE(P[Product])
G4:G14	G4	=MAP(P[Product],LAMBDA(x,IF(EXACT(XLOOKUP(x,E4#,E4#),x),x,x&"_")))
I5:J12	I5	=APIVOT(G4#,,P[Sales],LAMBDA(x,COUNT(x)),"Count")
L7:M14	L7	=APIVOT(G4#,,P[Sales],LAMBDA(x,SUM(x)),"Sum")
I18:J22	I18	=APIVOT(P[Product],,P[Sales],LAMBDA(x,COUNT(x)),"Count")
L20:M24	L20	=APIVOT(P[Product],,P[Sales],LAMBDA(x,SUM(x)),"Sum")
I27:J31	I27	=APIVOT(P[Product],,P[Sales],LAMBDA(x,AVERAGE(x)),"Avrg")
Dynamic array formulas.

 

[Xlambda]

A quick one, based on Chandoo's today's YT

"Working faster with data using (PQ) Unpivot" vs lambda UNPIVOT (downloadable sample data link in the description)
UNPIVOT(r,c,v,[h],[lc])
r: rows array
c: columns array
v: values array
[h]: headers: if omitted, no headers
[lc]: last column argument: if omitted last column is kept, if 1 or <>0 last column is dropped.

=LAMBDA(r, c, v, [h], [lc],
    LET(
        i, ISOMITTED(r),
        j, ISOMITTED(c),
        w, ROWS(v),
        l, COLUMNS(v),
        s, SEQUENCE(w * l),
        z, TOCOL(IF(v = "", "", v)),
        q, QUOTIENT(s - 1, l) + 1,
        m, MOD(s - 1, l) + 1,
        x, INDEX(IF(r = "", "", r), q, SEQUENCE(, COLUMNS(r))),
        y, INDEX(TRANSPOSE(IF(c = "", "", c)), m, SEQUENCE(, ROWS(c))),
        a, IF(i, IF(j, z, HSTACK(y, z)), IF(j, HSTACK(x, z), HSTACK(x, y, z))),
        b, FILTER(a, z <> ""),
        k, IF(lc, DROP(b, , -1), b),
        t, TRANSPOSE(SCAN("", TRANSPOSE(k), LAMBDA(v, i, IF(i = "", v, i)))),
        IF(AND(h = ""), t, VSTACK(h, t))
    )
)

unpivot-example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		Awesome Chocolates - 2023 Budget Workbook
2
3			Month
4		Person	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23			=UNPIVOT(B5:B29,C4:N4,C5:N29,{"Person","Month","Budget"})
5		Jehu Rudeforth	730	315	465	890	795	550	475	405							Person	Month	Budget
6		Van Tuxwell	1,025	520	550	220	980	325	395	445							Jehu Rudeforth	01-01-23	730
7		Gigi Bohling	220	625	625	290	370	385									Jehu Rudeforth	01-02-23	315
8		Jan Morforth	255	375	840	410	815										Jehu Rudeforth	01-03-23	465
9		Oby Sorrel	700	350	635	625	220	265	385	310	360	450	740	525			Jehu Rudeforth	01-04-23	890
10		Gunar ****shoot	680	285	485	540	520	410	550	865	405	660	265	820			Jehu Rudeforth	01-05-23	795
11		Brien Boise	375	465	260	400	435	535	660								Jehu Rudeforth	01-06-23	550
12		Rafaelita Blaksland	605	350	940	235	655		255								Jehu Rudeforth	01-07-23	475
13		Barr Faughny	635	205	310	575	485	560	470	230	395	280	420	795			Jehu Rudeforth	01-08-23	405
14		Mallorie Waber	305	285	460	255	325	730									Van Tuxwell	01-01-23	1025
15		Karlen McCaffrey	250	370	415	480	570	595									Van Tuxwell	01-02-23	520
16		Marney O'Breen	920	505	490	520	605	335									Van Tuxwell	01-03-23	550
17		Beverie Moffet	585	360	635	360	870	640									Van Tuxwell	01-04-23	220
18		Roddy Speechley	465	315	610	355	525										Van Tuxwell	01-05-23	980
19		Curtice Advani	415	530	335	750	530	405	1,100	460							Van Tuxwell	01-06-23	325
20		Husein Augar	280	330	465	350	340	650	435	320							Van Tuxwell	01-07-23	395
21		Kaine Padly	275	525	430	260	510	335	465	300	205	525	1,320	935			Van Tuxwell	01-08-23	445
22		Dennison Crosswaite	200	400	625	390	420										Gigi Bohling	01-01-23	220
23		Wilone O'Kielt	265	495	625	575	835										Gigi Bohling	01-02-23	625
24		Andria Kimpton	1,040	345	625	285	205	325	220	675							Gigi Bohling	01-03-23	625
25		Kelci Walkden	285	485	1,015	410	375	340	580	390							Gigi Bohling	01-04-23	290
26		Camilla Castle	910	680	305	415	245										Gigi Bohling	01-05-23	370
27		Madelene Upcott	235	575	230	520	610	320	685	725							Gigi Bohling	01-06-23	385
28		Dotty Strutley	300	650	445	435	410										Jan Morforth	01-01-23	255
29		Ches Bonnell	290	430	1,095	305	580										Jan Morforth	01-02-23	375
30																	Jan Morforth	01-03-23	840
31																	Jan Morforth	01-04-23	410
32																	Jan Morforth	01-05-23	815
33																	Oby Sorrel	01-01-23	700
34																	Oby Sorrel	01-02-23	350
35																	Oby Sorrel	01-03-23	635
36																	Oby Sorrel	01-04-23	625
37																	Oby Sorrel	01-05-23	220
38																	Oby Sorrel	01-06-23	265
39																	Oby Sorrel	01-07-23	385
40																	Oby Sorrel	01-08-23	310
41																	Oby Sorrel	01-09-23	360
42																	Oby Sorrel	01-10-23	450
43																	Oby Sorrel	01-11-23	740
44																	Oby Sorrel	01-12-23	525
45																	Gunar ****shoot	01-01-23	680
46																	Gunar ****shoot	01-02-23	285
47																	Gunar ****shoot	01-03-23	485
48																	Gunar ****shoot	01-04-23	540
49																	Gunar ****shoot	01-05-23	520
50																	Gunar ****shoot	01-06-23	410
51																	Gunar ****shoot	01-07-23	550
52																	Gunar ****shoot	01-08-23	865
53																	Gunar ****shoot	01-09-23	405
54																	Gunar ****shoot	01-10-23	660
55																	Gunar ****shoot	01-11-23	265
56																	Gunar ****shoot	01-12-23	820
57																	Brien Boise	01-01-23	375
58																	Brien Boise	01-02-23	465
59																	Brien Boise	01-03-23	260
60																	Brien Boise	01-04-23	400
61																	Brien Boise	01-05-23	435
62																	Brien Boise	01-06-23	535
63																	Brien Boise	01-07-23	660
64																	Rafaelita Blaksland	01-01-23	605
65																	Rafaelita Blaksland	01-02-23	350
66																	Rafaelita Blaksland	01-03-23	940
67																	Rafaelita Blaksland	01-04-23	235
68																	Rafaelita Blaksland	01-05-23	655
69																	Rafaelita Blaksland	01-07-23	255
70																	Barr Faughny	01-01-23	635
71																	Barr Faughny	01-02-23	205
72																	Barr Faughny	01-03-23	310
73																	Barr Faughny	01-04-23	575
74																	Barr Faughny	01-05-23	485
75																	Barr Faughny	01-06-23	560
76																	Barr Faughny	01-07-23	470
77																	Barr Faughny	01-08-23	230
78																	Barr Faughny	01-09-23	395
79																	Barr Faughny	01-10-23	280
80																	Barr Faughny	01-11-23	420
81																	Barr Faughny	01-12-23	795
82																	Mallorie Waber	01-01-23	305
83																	Mallorie Waber	01-02-23	285
84																	Mallorie Waber	01-03-23	460
85																	Mallorie Waber	01-04-23	255
Budget

Cell Formulas
Range	Formula
Q4	Q4	=FORMULATEXT(Q5)
Q5:S187	Q5	=UNPIVOT(B5:B29,C4:N4,C5:N29,{"Person","Month","Budget"})
Dynamic array formulas.

unpivot-example.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
1
2		Person	Country	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23		=UNPIVOT(B3:C32,D2:O2,D3:O32,{"Person","Country","Month","Budget"})
3		Jehu Rudeforth	India	90	105	95	145	100	120	90	85	85	95	85	120		Person	Country	Month	Budget
4			New Zealand	85	145	150	90	180	100	80	170	165	140	150	85		Jehu Rudeforth	India	01-01-23	90
5			Australia	115	125	120	85	80	90	120	125	135	145	95	125		Jehu Rudeforth	India	01-02-23	105
6		Van Tuxwell	India	90	95	115	130	135	130	90	135	100	105	85	115		Jehu Rudeforth	India	01-03-23	95
7			New Zealand	125	90	125	110	80	110	90	85	85	125	95	110		Jehu Rudeforth	India	01-04-23	145
8			Australia	110	95	105	135	120	90	125	95	95	120	110	90		Jehu Rudeforth	India	01-05-23	100
9		Gigi Bohling	India	110	130	130	85	90	120	80	85	115	160	85	140		Jehu Rudeforth	India	01-06-23	120
10			New Zealand	100	95	95	140	145	130	125	155	170	80	140	105		Jehu Rudeforth	India	01-07-23	90
11			Australia	145	120	140	130	90	110	155	115	90	115	115	85		Jehu Rudeforth	India	01-08-23	85
12		Jan Morforth	India	90	105	100	130	80	100	85	95	115	90	125	115		Jehu Rudeforth	India	01-09-23	85
13			New Zealand	145	80	90	110	160	135	115	110	120	105	125	90		Jehu Rudeforth	India	01-10-23	95
14			Australia	195	100	80	90	110	105	150	110	140	160	80	105		Jehu Rudeforth	India	01-11-23	85
15		Oby Sorrel	India	110	140	100	115	100	120	110	95	105	130	115	85		Jehu Rudeforth	India	01-12-23	120
16			New Zealand	110	110	105	130	80	135	95	130	95	105	100	130		Jehu Rudeforth	New Zealand	01-01-23	85
17			Australia	110	90	145	85	95	135	155	100	190	80	115	125		Jehu Rudeforth	New Zealand	01-02-23	145
18		Gunar ****shoot	India	145	105	85	115	85	95	155	95	145	85	110	160		Jehu Rudeforth	New Zealand	01-03-23	150
19			New Zealand	105	90	85	80	140	105	85	130	120	100	145	95		Jehu Rudeforth	New Zealand	01-04-23	90
20			Australia	100	160	95	110	155	85	120	130	90	140	130	130		Jehu Rudeforth	New Zealand	01-05-23	180
21		Brien Boise	India	100	115	85	170	100	130	85	190	90	80	105	135		Jehu Rudeforth	New Zealand	01-06-23	100
22			New Zealand	100	95	100	105	115	85	110	190	90	105	160	90		Jehu Rudeforth	New Zealand	01-07-23	80
23			Australia	125	85	90	125	135	135	105	90	95	175	160	85		Jehu Rudeforth	New Zealand	01-08-23	170
24		Rafaelita Blaksland	India	100	95	120	110	95	155	165	95	115	140	105	110		Jehu Rudeforth	New Zealand	01-09-23	165
25			New Zealand	90	140	115	135	125	120	135	125	80	115	100	95		Jehu Rudeforth	New Zealand	01-10-23	140
26			Australia	110	85	95	85	100	100	125	85	95	105	135	145		Jehu Rudeforth	New Zealand	01-11-23	150
27		Barr Faughny	India	100	100	135	140	170	130	95	105	105	125	85	120		Jehu Rudeforth	New Zealand	01-12-23	85
28			New Zealand	90	100	155	105	105	115	95	80	130	95	85	145		Jehu Rudeforth	Australia	01-01-23	115
29			Australia	150	140	165	115	105	85	90	125	175	90	80	120		Jehu Rudeforth	Australia	01-02-23	125
30		Mallorie Waber	India	100	130	130	90	80	100	120	135	100	125	145	110		Jehu Rudeforth	Australia	01-03-23	120
31			New Zealand	140	95	80	165	100	120	95	115	90	100	110	85		Jehu Rudeforth	Australia	01-04-23	85
32			Australia	140	105	120	80	155	90	100	110	155	170	110	110		Jehu Rudeforth	Australia	01-05-23	80
33																	Jehu Rudeforth	Australia	01-06-23	90
34																	Jehu Rudeforth	Australia	01-07-23	120
35																	Jehu Rudeforth	Australia	01-08-23	125
36																	Jehu Rudeforth	Australia	01-09-23	135
37																	Jehu Rudeforth	Australia	01-10-23	145
38																	Jehu Rudeforth	Australia	01-11-23	95
39																	Jehu Rudeforth	Australia	01-12-23	125
40																	Van Tuxwell	India	01-01-23	90
41																	Van Tuxwell	India	01-02-23	95
42																	Van Tuxwell	India	01-03-23	115
43																	Van Tuxwell	India	01-04-23	130
44																	Van Tuxwell	India	01-05-23	135
45																	Van Tuxwell	India	01-06-23	130
46																	Van Tuxwell	India	01-07-23	90
47																	Van Tuxwell	India	01-08-23	135
48																	Van Tuxwell	India	01-09-23	100
49																	Van Tuxwell	India	01-10-23	105
50																	Van Tuxwell	India	01-11-23	85
51																	Van Tuxwell	India	01-12-23	115
52																	Van Tuxwell	New Zealand	01-01-23	125
53																	Van Tuxwell	New Zealand	01-02-23	90
54																	Van Tuxwell	New Zealand	01-03-23	125
55																	Van Tuxwell	New Zealand	01-04-23	110
56																	Van Tuxwell	New Zealand	01-05-23	80
57																	Van Tuxwell	New Zealand	01-06-23	110
58																	Van Tuxwell	New Zealand	01-07-23	90
59																	Van Tuxwell	New Zealand	01-08-23	85
60																	Van Tuxwell	New Zealand	01-09-23	85
61																	Van Tuxwell	New Zealand	01-10-23	125
62																	Van Tuxwell	New Zealand	01-11-23	95
63																	Van Tuxwell	New Zealand	01-12-23	110
64																	Van Tuxwell	Australia	01-01-23	110
65																	Van Tuxwell	Australia	01-02-23	95
66																	Van Tuxwell	Australia	01-03-23	105
67																	Van Tuxwell	Australia	01-04-23	135
68																	Van Tuxwell	Australia	01-05-23	120
69																	Van Tuxwell	Australia	01-06-23	90
70																	Van Tuxwell	Australia	01-07-23	125
71																	Van Tuxwell	Australia	01-08-23	95
72																	Van Tuxwell	Australia	01-09-23	95
73																	Van Tuxwell	Australia	01-10-23	120
74																	Van Tuxwell	Australia	01-11-23	110
75																	Van Tuxwell	Australia	01-12-23	90
76																	Gigi Bohling	India	01-01-23	110
Unpivot this

Cell Formulas
Range	Formula
Q2	Q2	=FORMULATEXT(Q3)
Q3:T363	Q3	=UNPIVOT(B3:C32,D2:O2,D3:O32,{"Person","Country","Month","Budget"})
Dynamic array formulas.

 

[Xlambda]

Versatility of UNPIVOT, examples:

UNPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		- rows/columns array as vectors
2		When values array has blanks, data is filtered
3											=UNPIVOT(B5:B12,C4:I4,C5:I12,{"Prod","Day","Val."})
4		Product/Day	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7		Prod	Day	Val.
5		Quad	420	944	993	1016	527	1064	596		Quad	Day 1	420
6		Sunset	479	1181	860	1037	617		1230		Quad	Day 2	944
7		Aspen	619		888	814	851	320	1157		Quad	Day 3	993
8		Yanaki	1115	982	274	794	1109	826	1281		Quad	Day 4	1016
9		Bellen	834	875	776		775	377	423		Quad	Day 5	527
10		Carlota	848		1037	555	624	723	329		Quad	Day 6	1064
11		Vrang	1102	442	831	314	662		678		Quad	Day 7	596
12		Sunshine	759	1241	347	649	949	1162	794		Sunset	Day 1	479
13											Sunset	Day 2	1181
14											Sunset	Day 3	860
15											Sunset	Day 4	1037
16											Sunset	Day 5	617
17											Sunset	Day 7	1230
18											Aspen	Day 1	619
19											Aspen	Day 3	888
20											Aspen	Day 4	814
21											Aspen	Day 5	851
22											Aspen	Day 6	320
23											Aspen	Day 7	1157
24											Yanaki	Day 1	1115
25											Yanaki	Day 2	982
26											Yanaki	Day 3	274
27											Yanaki	Day 4	794
28											Yanaki	Day 5	1109
29											Yanaki	Day 6	826
30											Yanaki	Day 7	1281
31											Bellen	Day 1	834
32											Bellen	Day 2	875
33											Bellen	Day 3	776
34											Bellen	Day 5	775
35											Bellen	Day 6	377
36											Bellen	Day 7	423
37											Carlota	Day 1	848
38											Carlota	Day 3	1037
39											Carlota	Day 4	555
40											Carlota	Day 5	624
41											Carlota	Day 6	723
42											Carlota	Day 7	329
43											Vrang	Day 1	1102
44											Vrang	Day 2	442
45											Vrang	Day 3	831
46											Vrang	Day 4	314
47											Vrang	Day 5	662
48											Vrang	Day 7	678
49											Sunshine	Day 1	759
50											Sunshine	Day 2	1241
51											Sunshine	Day 3	347
52											Sunshine	Day 4	649
53											Sunshine	Day 5	949
54											Sunshine	Day 6	1162
55											Sunshine	Day 7	794
56
UNPIVOT 1

Cell Formulas
Range	Formula
K3	K3	=FORMULATEXT(K4)
K4:M55	K4	=UNPIVOT(B5:B12,C4:I4,C5:I12,{"Prod","Day","Val."})
Dynamic array formulas.

UNPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		- rows/columns arrays can be 2D , not only vectors
2		UNPIVOT Versatility
3		- no problem to fill the blanks where is needed( if rows or columns array have blanks)
4		and to exclude the blanks where is needed (values array is filtered of blanks)
5			blank
6						c							r,B8:D31,c,F5:K6,v,F8:K31
7						Ab			Cd				=UNPIVOT(B10:D33,F7:K8,F10:K33)
8						x1	x2	x3	x1	x2	x3		A	a	x	Ab	x1	43
9		r				v							A	a	x	Ab	x3	78
10		A	a	x		43		78	95	15	37		A	a	x	Cd	x1	95
11				y		49		35	81		58		A	a	x	Cd	x2	15
12				z		16	21			16			A	a	x	Cd	x3	37
13			b	x		69	36	52	53		17		A	a	y	Ab	x1	49
14				y		43		53	70	69	82		A	a	y	Ab	x3	35
15				z			63	16	20	53			A	a	y	Cd	x1	81
16			c	x		54	21		95	88	86		A	a	y	Cd	x3	58
17				y		96	24	70	86		95		A	a	z	Ab	x1	16
18				z			22	30	13	96	48		A	a	z	Ab	x2	21
19			d	x		27	38		53	28	29		A	a	z	Ab	x2	16
20				y		63		22	68	25			A	b	x	Ab	x1	69
21				z		30	45	61		55	86		A	b	x	Ab	x2	36
22		B	a	x		43	57	50	81	32	42		A	b	x	Ab	x3	52
23				y		45	32	78	70		77		A	b	x	Cd	x1	53
24				z			67	73		29	94		A	b	x	Cd	x3	17
25			b	x		97		66	24	28	14		A	b	y	Ab	x1	43
26				y		94	22	74	21	28			A	b	y	Ab	x3	53
27				z		90	34		63	58	68		A	b	y	Cd	x1	70
28			c	x		63	62	33		30	91		A	b	y	Cd	x2	69
29				y			48	68	43	47	89		A	b	y	Cd	x3	82
30				z		31	77			52	23		A	b	z	Cd	x2	63
31			d	x		57	70	50	22	50	86		A	b	z	Cd	x3	16
32				y		89		64	58	21	31		A	b	z	Cd	x1	20
33				z		97	38	92		31	80		A	b	z	Cd	x2	53
34													A	c	x	Ab	x1	54
35													A	c	x	Ab	x2	21
36													A	c	x	Cd	x1	95
37													A	c	x	Cd	x2	88
38													A	c	x	Cd	x3	86
39													A	c	y	Ab	x1	96
40													A	c	y	Ab	x2	24
41													A	c	y	Ab	x3	70
42													A	c	y	Cd	x1	86
43													A	c	y	Cd	x3	95
44													A	c	z	Cd	x2	22
45													A	c	z	Cd	x3	30
46													A	c	z	Cd	x1	13
47													A	c	z	Cd	x2	96
48													A	c	z	Cd	x3	48
49													A	d	x	Ab	x1	27
50													A	d	x	Ab	x2	38
51													A	d	x	Cd	x1	53
52													A	d	x	Cd	x2	28
53													A	d	x	Cd	x3	29
54													A	d	y	Ab	x1	63
55													A	d	y	Ab	x3	22
56													A	d	y	Cd	x1	68
57													A	d	y	Cd	x2	25
58													A	d	z	Ab	x1	30
59													A	d	z	Ab	x2	45
60													A	d	z	Ab	x3	61
61													A	d	z	Ab	x2	55
62													A	d	z	Ab	x3	86
63													B	a	x	Ab	x1	43
64													B	a	x	Ab	x2	57
65													B	a	x	Ab	x3	50
66													B	a	x	Cd	x1	81
67													B	a	x	Cd	x2	32
68													B	a	x	Cd	x3	42
69													B	a	y	Ab	x1	45
70													B	a	y	Ab	x2	32
71													B	a	y	Ab	x3	78
72													B	a	y	Cd	x1	70
73													B	a	y	Cd	x3	77
74													B	a	z	Cd	x2	67
75													B	a	z	Cd	x3	73
76													B	a	z	Cd	x2	29
77													B	a	z	Cd	x3	94
78													B	b	x	Ab	x1	97
79													B	b	x	Ab	x3	66
80													B	b	x	Cd	x1	24
81													B	b	x	Cd	x2	28
82													B	b	x	Cd	x3	14
83													B	b	y	Ab	x1	94
84													B	b	y	Ab	x2	22
85													B	b	y	Ab	x3	74
86													B	b	y	Cd	x1	21
87													B	b	y	Cd	x2	28
88													B	b	z	Ab	x1	90
89													B	b	z	Ab	x2	34
90													B	b	z	Cd	x1	63
91													B	b	z	Cd	x2	58
92													B	b	z	Cd	x3	68
93													B	c	x	Ab	x1	63
94													B	c	x	Ab	x2	62
95													B	c	x	Ab	x3	33
96													B	c	x	Ab	x2	30
97													B	c	x	Ab	x3	91
98													B	c	y	Ab	x2	48
99													B	c	y	Ab	x3	68
100													B	c	y	Cd	x1	43
101													B	c	y	Cd	x2	47
102													B	c	y	Cd	x3	89
103													B	c	z	Ab	x1	31
104													B	c	z	Ab	x2	77
105													B	c	z	Ab	x2	52
106													B	c	z	Ab	x3	23
107													B	d	x	Ab	x1	57
108													B	d	x	Ab	x2	70
109													B	d	x	Ab	x3	50
110													B	d	x	Cd	x1	22
111													B	d	x	Cd	x2	50
112													B	d	x	Cd	x3	86
113													B	d	y	Ab	x1	89
114													B	d	y	Ab	x3	64
115													B	d	y	Cd	x1	58
116													B	d	y	Cd	x2	21
117													B	d	y	Cd	x3	31
118													B	d	z	Ab	x1	97
119													B	d	z	Ab	x2	38
120													B	d	z	Ab	x3	92
121													B	d	z	Ab	x2	31
122													B	d	z	Ab	x3	80
123
UNPIVOT 2

Cell Formulas
Range	Formula
M7	M7	=FORMULATEXT(M8)
M8:R122	M8	=UNPIVOT(B10:D33,F7:K8,F10:K33)
Dynamic array formulas.

UNPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		Data scenario without column headers					c,omitted			Data scenario without row headers						r,omitted
3							=UNPIVOT(B4:B6,,C4:E6)									=UNPIVOT(,J4:M4,J5:M9)
4		Team 1	Mel Scheidler	Antione Grinnell	Rey Mcglothlin		Team 1	Mel Scheidler		East	West	South	North			East	23
5		Team 2	Theodore Kriger	Antione Scheidler			Team 1	Antione Grinnell		23	45	67	98			West	45
6		Team 3	Freddie Otten	Houston Ybarbo	Jesus Pasquariello		Team 1	Rey Mcglothlin		32	67	89	18			South	67
7							Team 2	Theodore Kriger		21	43		56			North	98
8							Team 2	Antione Scheidler			53		21			East	32
9							Team 3	Freddie Otten					32			West	67
10							Team 3	Houston Ybarbo								South	89
11							Team 3	Jesus Pasquariello								North	18
12																East	21
13						Data is consistent with teams membership										West	43
14																North	56
15																West	53
16																North	21
17																North	32
18
UNPIVOT 3

Cell Formulas
Range	Formula
G3,P3	G3	=FORMULATEXT(G4)
G4:H11	G4	=UNPIVOT(B4:B6,,C4:E6)
P4:Q17	P4	=UNPIVOT(,J4:M4,J5:M9)
Dynamic array formulas.

 

[Xlambda]

Task: Create a consistent random data set with a list of products based on their available types

UNPIVOT.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		Task: Create a consistent random data set with a list of products based on their available types											lc, last column argument						Consistent Random Data Set
2		- using lc argument =1, to drop last column (not relevant)											for this scenario comes handy using lc=1						=HSTACK(INDEX(N6#,RANDARRAY(30,,1,ROWS(N6#),1),{1,2}),RANDARRAY(30,,100,999,1))
3													if lc=1, last column will be eliminated having no relevance						↓↓↓↓
4		Types of existing products by brand								lc,omitted				lc,1					↓↓↓↓				Single cell formula
5		Brand/Type	phone	foldable	tablet	laptop	PC	TV		=UNPIVOT(B6:B10,C5:H5,C6:H10)				=UNPIVOT(B6:B10,C5:H5,C6:H10,,1)					↓↓↓↓
6		HP	x			x	x			HP	phone	x		HP	phone				Microsoft	foldable	514		Sony	laptop	218
7		Samsung	x	x	x	x		x		HP	laptop	x		HP	laptop				Sony	tablet	957		Apple	phone	250
8		Apple	x		x	x	x			HP	PC	x		HP	PC				Sony	laptop	116		Samsung	TV	798
9		Microsoft		x	x	x	x			Samsung	phone	x		Samsung	phone				Microsoft	tablet	161		Microsoft	tablet	105
10		Sony	x		x	x		x		Samsung	foldable	x		Samsung	foldable				Microsoft	tablet	152		HP	phone	396
11										Samsung	tablet	x		Samsung	tablet				Microsoft	foldable	171		HP	PC	629
12										Samsung	laptop	x		Samsung	laptop				Sony	tablet	531		Microsoft	foldable	399
13										Samsung	TV	x		Samsung	TV				Microsoft	tablet	848		HP	phone	848
14										Apple	phone	x		Apple	phone				Microsoft	PC	138		Apple	laptop	728
15										Apple	tablet	x		Apple	tablet				Samsung	laptop	710		Samsung	laptop	578
16										Apple	laptop	x		Apple	laptop				Microsoft	tablet	872		Microsoft	tablet	814
17										Apple	PC	x		Apple	PC				Samsung	foldable	723		Apple	phone	331
18										Microsoft	foldable	x		Microsoft	foldable				Sony	tablet	787		Microsoft	foldable	427
19										Microsoft	tablet	x		Microsoft	tablet				Apple	phone	155		Apple	phone	223
20										Microsoft	laptop	x		Microsoft	laptop				Samsung	tablet	648		Samsung	tablet	132
21										Microsoft	PC	x		Microsoft	PC				Microsoft	PC	106		Samsung	TV	681
22										Sony	phone	x		Sony	phone				Microsoft	foldable	479		Sony	TV	404
23										Sony	tablet	x		Sony	tablet				Apple	laptop	357		Samsung	TV	529
24										Sony	laptop	x		Sony	laptop				Apple	phone	144		Samsung	phone	491
25										Sony	TV	x		Sony	TV				Apple	tablet	232		Samsung	TV	630
26																			Apple	laptop	957		Samsung	tablet	893
27													As we see data is consistent with their type						Sony	laptop	305		Sony	tablet	798
28													(ex: Sony has no foldable type)						Microsoft	tablet	765		Samsung	tablet	209
29																			Samsung	foldable	570		Samsung	foldable	245
30																			HP	laptop	498		Sony	tablet	468
31																			Microsoft	PC	645		Samsung	tablet	829
32																			Microsoft	PC	982		Apple	PC	376
33																			Apple	PC	261		Microsoft	PC	855
34																			Apple	tablet	823		Samsung	foldable	802
35																			Microsoft	foldable	124		Samsung	laptop	816
36													Single cell formula W6
37													=LET(r,B6:B10,c,C5:H5,v,C6:H10,n,30,t,UNPIVOT(r,c,v,,1),HSTACK(INDEX(t,RANDARRAY(n,,1,ROWS(t),1),{1,2}),RANDARRAY(n,,100,999,1)))
38
UNPIVOT 4

Cell Formulas
Range	Formula
S2	S2	=FORMULATEXT(S6)
J5,N5	J5	=FORMULATEXT(J6)
J6:L25	J6	=UNPIVOT(B6:B10,C5:H5,C6:H10)
N6:O25	N6	=UNPIVOT(B6:B10,C5:H5,C6:H10,,1)
S6:U35	S6	=HSTACK(INDEX(N6#,RANDARRAY(30,,1,ROWS(N6#),1),{1,2}),RANDARRAY(30,,100,999,1))
W6:Y35	W6	=LET(r,B6:B10,c,C5:H5,v,C6:H10,n,30,t,UNPIVOT(r,c,v,,1),HSTACK(INDEX(t,RANDARRAY(n,,1,ROWS(t),1),{1,2}),RANDARRAY(n,,100,999,1)))
M37	M37	=FORMULATEXT(W6)
Dynamic array formulas.

 

[Xlambda]

Improved versatility: the function can deal with entire empty/blank rows of "values" array. For that, SCAN (filling empty cells with data) should happen for rows/columns array, before unpivoting. Same name, same arguments.
UNPIVOT(r,c,v,[h],[lc])
r: rows array
c: columns array
v: values array
[h]: headers: if omitted, no headers
[lc]: last column argument: if omitted last column is kept, if 1 or <>0 last column is dropped.

=LAMBDA(r, c, v, [h], [lc],
    LET(
        i, ISOMITTED(r),
        j, ISOMITTED(c),
        w, ROWS(v),
        l, COLUMNS(v),
        s, SEQUENCE(w * l),
        z, TOCOL(IF(v = "", "", v)),
        q, QUOTIENT(s - 1, l) + 1,
        m, MOD(s - 1, l) + 1,
        t, TRANSPOSE(IF(r = "", "", r)),
        f, LAMBDA(x, SCAN("", x, LAMBDA(v, i, IF(i = "", v, i)))),
        x, INDEX(TRANSPOSE(f(t)), q, SEQUENCE(, COLUMNS(r))),
        y, INDEX(TRANSPOSE(f(c)), m, SEQUENCE(, ROWS(c))),
        a, IF(i, IF(j, z, HSTACK(y, z)), IF(j, HSTACK(x, z), HSTACK(x, y, z))),
        b, FILTER(a, z <> ""),
        k, IF(lc, DROP(b, , -1), b),
        IF(AND(h = ""), k, VSTACK(h, k))
    )
)

Looked for more complicated scenarios and found this one, Leila's YT: (has downloadable workbook in the description)

Excel Unpivot Data with Multiple Headers (Multiple Row levels into Columns with Power Query)​

Needs a single simple preliminary step to filter initial data, which is in a report shape with subtotals included.

Advanced_UnPivot_XelPlus (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Advanced UnPivot Technique
2	Monthly Sales (Actual vs Budget)
3			!!!! Observe rows 60-63, full blank rows of "values" array							filtering data to get rid of subtotals rows
4										=LET(a,A5:H132,b,IF(a="","",a),FILTER(b,NOT(ISNUMBER(SEARCH("total",TAKE(b,,1))))))
5			Actual			Budget						Actual			Budget
6	Customer Name	Article Description	January	February	March	January	February	March		Customer Name	Article Description	01-01-20	01-02-20	01-03-20	01-01-20	01-02-20	01-03-20
7	Aida GmbH	Laptop bag (black)	23,700	55,801	14,160	22,414	62,981	13,682		Aida GmbH	Laptop bag (black)	23700.4	55801.24	14160.02	22414.24	62981.14	13681.65
8		Laptop bag (red)	60,721		25,440	70,101		21,438			Laptop bag (red)	60721.2		25440.35	70100.55		21438.38
9		Men dress shirt (black)	10,425		13,001	9,601		13,916			Men dress shirt (black)	10425.1		13000.51	9600.54		13916.44
10		Men dress shirt (gray)	17,500	25,676	4,300	17,517	29,318	4,038			Men dress shirt (gray)	17500.46	25676.25	4300.4	17516.56	29317.53	4038.06
11		Men dress shirt (white)	4,775		16,076	5,658		13,272			Men dress shirt (white)	4775.19		16075.63	5658.09		13272.13
12		Men shorts (black)	12,997	3,672	13,555	11,702	3,525	13,879			Men shorts (black)	12996.8	3672.4	13555.28	11702.05	3525.2	13878.63
13		Men shorts (gray)	14,527	11,647	10,639	14,986	13,743	8,958			Men shorts (gray)	14526.67	11646.68	10638.8	14986.37	13742.53	8957.77
14		Men type T (Brown) simple	2,548	9,140	5,551	2,653	9,883	6,162			Men type T (Brown) simple	2548.4	9140.2	5551.4	2653	9883.11	6162.22
15		Men type T simple (black)	6,589	2,496	6,613	7,634	2,906	7,692			Men type T simple (black)	6588.55	2496.24	6612.8	7634.02	2906.38	7691.94
16		Men type T simple (white)	4,788	4,548	4,728	4,081	3,895	4,089			Men type T simple (white)	4788.47	4548.41	4728.31	4081.22	3895.22	4088.67
17		Smartphone case diamond	9,875	15,100		9,629	17,559				Smartphone case diamond	9875.07	15100.27		9628.81	17558.58
18		Smartphone case simple	35,042	14,041	27,061	41,480	16,634	24,709			Smartphone case simple	35041.75	14040.8	27061.27	41479.73	16633.65	24709.24
19		Unisex tank top (black)	1,356	4,170	1,518	1,450	3,468	1,761			Unisex tank top (black)	1356.4	4170.29	1518.37	1449.72	3467.74	1760.74
20		Unisex tank top (white)	4,297	5,161	2,328	5,127	5,758	1,980			Unisex tank top (white)	4297.16	5161.2	2328.4	5127.49	5758.4	1980.36
21		Women crop top (black)	6,151	3,810	1,640	6,350	3,439	1,859			Women crop top (black)	6150.82	3810.42	1640.45	6349.73	3438.63	1858.6
22		Women dress (black) long	11,594	30,505		12,366	32,710				Women dress (black) long	11594.4	30504.63		12366.36	32709.71
23		Women dress (red) ****tail	23,374	45,881	31,435	19,135	47,066	32,849			Women dress (red) ****tail	23374.4	45880.54	31434.55	19135.23	47066.07	32848.53
24		Women type T (black) simple	1,920	4,308	9,913	2,298	3,730	9,139			Women type T (black) simple	1920.4	4308.4	9913.04	2297.54	3730.46	9138.58
25		Women type T (white) simple			7,513			7,590			Women type T (white) simple			7512.85			7590.27
26		Women type T simple (red)		4,277	5,642		4,793	6,070			Women type T simple (red)		4277.19	5642.4		4792.73	6070.36
27	Aida GmbH Total		252,182	240,235	201,115	264,181	261,407	193,083		Dellicia	Laptop bag (black)	22200.2	36060.5	23820.37	26461.25	30806.34	20731.9
28	Dellicia	Laptop bag (black)	22,200	36,061	23,820	26,461	30,806	20,732			Laptop bag (red)	26100.4	61801.2		26553.5	70101.92
29		Laptop bag (red)	26,100	61,801		26,554	70,102				Men dress shirt (black)		28101.74	17800.8		28084.18	20203.71
30		Men dress shirt (black)		28,102	17,801		28,084	20,204			Men dress shirt (gray)	18875.65	16325.62	6950.4	18520.92	17452.33	5795.45
31		Men dress shirt (gray)	18,876	16,326	6,950	18,521	17,452	5,795			Men dress shirt (white)	4850.4	10250.4	12225.8	5525.82	11904.49	13223.45
32		Men dress shirt (white)	4,850	10,250	12,226	5,526	11,904	13,223			Men shorts (black)	14382.58	5508.22	25903.61	15224.36	4962.17	29418.93
33		Men shorts (black)	14,383	5,508	25,904	15,224	4,962	29,419			Men shorts (gray)	20395.38	8208.63	15210.8	21130.55	8633.67	17320.94
34		Men shorts (gray)	20,395	8,209	15,211	21,131	8,634	17,321			Men type T (Brown) simple	14340.48	13963.32		14086.11	15434.34
35		Men type T (Brown) simple	14,340	13,963		14,086	15,434				Men type T simple (black)	13008.96			13759.43
36		Men type T simple (black)	13,009			13,759					Men type T simple (white)	4656	3060.4	3552.19	3832.01	3547.08	3973.08
37		Men type T simple (white)	4,656	3,060	3,552	3,832	3,547	3,973			Smartphone case diamond		6275.07	5300.31		6095.45	5069.13
38		Smartphone case diamond		6,275	5,300		6,095	5,069			Smartphone case simple	5120.4	11080.58	6560.33	4952.78	9777.57	7791.57
39		Smartphone case simple	5,120	11,081	6,560	4,953	9,778	7,792			Unisex tank top (black)	1740.4	2298.26		1823.06	1842.42
40		Unisex tank top (black)	1,740	2,298		1,823	1,842				Unisex tank top (white)	1458.4	3558.77	9222.83	1638.38	4261.11	7741.49
41		Unisex tank top (white)	1,458	3,559	9,223	1,638	4,261	7,741			Women crop top (black)	13321.38	7650.99	3750.47	15711.31	6263.44	3885.69
42		Women crop top (black)	13,321	7,651	3,750	15,711	6,263	3,886			Women dress (black) long	37386.5	23436.4	70246.79	42808.09	26002.31	71271.98
43		Women dress (black) long	37,387	23,436	70,247	42,808	26,002	71,272			Women dress (red) ****tail	11346.4	18166.4	61318.73	9240.32	17291.6	64479.42
44		Women dress (red) ****tail	11,346	18,166	61,319	9,240	17,292	64,479			Women type T (black) simple		4080.34	6372.84		3648.83	7021.04
45		Women type T (black) simple		4,080	6,373		3,649	7,021			Women type T (white) simple
46		Women type T (white) simple									Women type T simple (red)	7904.61		2977.32	8968.68		2511.67
47		Women type T simple (red)	7,905		2,977	8,969		2,512		Edson	Laptop bag (black)	15000.48			12796.51
48	Dellicia Total		217,088	259,827	271,214	230,237	266,109	280,439			Laptop bag (red)
49	Edson	Laptop bag (black)	15,000			12,797					Men dress shirt (black)	9375.25	10450.25		7508.11	11525.58
50		Laptop bag (red)									Men dress shirt (gray)	7300.16			8296.79
51		Men dress shirt (black)	9,375	10,450		7,508	11,526				Men dress shirt (white)	10300.36			8967.79
52		Men dress shirt (gray)	7,300			8,297					Men shorts (gray)
53		Men dress shirt (white)	10,300			8,968					Men type T (Brown) simple			2652.4			2312.83
54		Men shorts (gray)									Men type T simple (black)	2472.4			2676.63
55		Men type T (Brown) simple			2,652			2,313			Men type T simple (white)			3912.4			3553.42
56		Men type T simple (black)	2,472			2,677					Smartphone case diamond		10700.01			9572.26
57		Men type T simple (white)			3,912			3,553			Smartphone case simple			4760.28			5485.25
58		Smartphone case diamond		10,700			9,572				Unisex tank top (black)
59		Smartphone case simple			4,760			5,485			Unisex tank top (white)
60		Unisex tank top (black)									Women crop top (black)
61		Unisex tank top (white)									Women dress (black) long		16120.4			14552.11
62		Women crop top (black)									Women dress (red) ****tail			14260.14			13881.44
63		Women dress (black) long		16,120			14,552				Women type T (black) simple		4584.8			5321.15
64		Women dress (red) ****tail			14,260			13,881			Women type T (white) simple			2940.41			3451.49
65		Women type T (black) simple		4,585			5,321				Women type T simple (red)
66		Women type T (white) simple			2,940			3,451		Erma	Laptop bag (black)	30540.8	15840.01		25576.37	15602.39
67		Women type T simple (red)									Laptop bag (red)		80401.27	48301.24		74738.62	43789.23
68	Edson Total		44,449	41,855	28,526	40,246	40,971	28,684			Men dress shirt (black)	8775.34	4700.4	15850.87	10037.18	5066.92	17319.53
69	Erma	Laptop bag (black)	30,541	15,840		25,576	15,602				Men dress shirt (gray)	17400.4	6325.4	15650.5	16524.26	6427.92	18712.31
70		Laptop bag (red)		80,401	48,301		74,739	43,789			Men dress shirt (white)	31802.04	45401.84	42151.39	26849.59	38766.64	48375.6
71		Men dress shirt (black)	8,775	4,700	15,851	10,037	5,067	17,320			Men shorts (black)	3834.4	11142.8	27488.04	4282.11	10111.1	28168.93
72		Men dress shirt (gray)	17,400	6,325	15,651	16,524	6,428	18,712			Men shorts (gray)	13770.54	3456.4	14904.86	14199.97	3360.53	13331.25
73		Men dress shirt (white)	31,802	45,402	42,151	26,850	38,767	48,376			Men type T (Brown) simple	8164.84		2990.4	7011.05		2411.01
74		Men shorts (black)	3,834	11,143	27,488	4,282	10,111	28,169			Men type T simple (black)	8292.8		4620.4	7456.37		5437.04
75		Men shorts (gray)	13,771	3,456	14,905	14,200	3,361	13,331			Men type T simple (white)	8244.8	9193.02	2184.35	9556.85	10448.3	2260.21
76		Men type T (Brown) simple	8,165		2,990	7,011		2,411			Smartphone case diamond	12000.84	4375.4		13636.55	4821.5
77		Men type T simple (black)	8,293		4,620	7,456		5,437			Smartphone case simple	6660.46	19240.85	11180.62	5453.21	16351.22	13256.97
78		Men type T simple (white)	8,245	9,193	2,184	9,557	10,448	2,260			Unisex tank top (black)	1602.48		3973.05	1310.28		3744.26
79		Smartphone case diamond	12,001	4,375		13,637	4,822				Unisex tank top (white)		7393.39			6161.55
80		Smartphone case simple	6,660	19,241	11,181	5,453	16,351	13,257			Women crop top (black)	3030.01	9870.96		2953.48	10114.38
81		Unisex tank top (black)	1,602		3,973	1,310		3,744			Women dress (black) long	55801	63923.27		48701.64	67963.31
82		Unisex tank top (white)		7,393			6,162				Women dress (red) ****tail	43276.72	20894.4		50025.8	18910.04
83		Women crop top (black)	3,030	9,871		2,953	10,114				Women type T (black) simple	8172.59		12301.23	8310.18		12501.27
84		Women dress (black) long	55,801	63,923		48,702	67,963				Women type T (white) simple	5352.55		5088.3	5303.37		5221.93
85		Women dress (red) ****tail	43,277	20,894		50,026	18,910				Women type T simple (red)	7904.68	4797.4	5434.8	8097.02	4739.52	5152.93
86		Women type T (black) simple	8,173		12,301	8,310		12,501		Liebher	Laptop bag (black)	20040.03		23160.14	21433.45		27351.45
87		Women type T (white) simple	5,353		5,088	5,303		5,222			Laptop bag (red)		49320.76	9600.21		39603.71	8098.27
88		Women type T simple (red)	7,905	4,797	5,435	8,097	4,740	5,153			Men dress shirt (black)	11250.23	12600.44		10411.12	13812.03
89	Erma Total		274,627	306,957	212,120	265,285	293,584	219,682			Men dress shirt (gray)	16475.8	20075.5	42477.08	17354.75	18041.33	46383.16
90	Liebher	Laptop bag (black)	20,040		23,160	21,433		27,351			Men dress shirt (white)		12200.61			10002.11
91		Laptop bag (red)		49,321	9,600		39,604	8,098			Men shorts (black)	5904.4	5832.33	4194.32	5154.63	5666.27	4544.28
92		Men dress shirt (black)	11,250	12,600		10,411	13,812				Men shorts (gray)	13303.17	7650.4	4554.4	14698.77	6690.52	4764.94
93		Men dress shirt (gray)	16,476	20,076	42,477	17,355	18,041	46,383			Men type T (Brown) simple			6292.66			6965.14
94		Men dress shirt (white)		12,201			10,002				Men type T simple (black)	4068.17	2856.13		3879.02	2394.78
95		Men shorts (black)	5,904	5,832	4,194	5,155	5,666	4,544			Men type T simple (white)	2760.25	5172.4		3170.34	6192.98
96		Men shorts (gray)	13,303	7,650	4,554	14,699	6,691	4,765			Smartphone case diamond	30476.67	9875.8		31866.06	10207.08
97		Men type T (Brown) simple			6,293			6,965			Smartphone case simple		5300.02	7400.4		5008	7024.86
98		Men type T simple (black)	4,068	2,856		3,879	2,395				Unisex tank top (black)	1440.4	3654.4	2628.02	1285.48	4142.37	2781.93
99		Men type T simple (white)	2,760	5,172		3,170	6,193				Unisex tank top (white)	1044.4		2484.37	859.32		2385.94
100		Smartphone case diamond	30,477	9,876		31,866	10,207				Women crop top (black)		4790.44	2410.19		5308.99	2653.61
101		Smartphone case simple		5,300	7,400		5,008	7,025			Women dress (black) long	19902.4			15971.46
102		Unisex tank top (black)	1,440	3,654	2,628	1,285	4,142	2,782			Women dress (red) ****tail	38874.68	25668.17	15562.19	39196.54	27302.14	13116.64
103		Unisex tank top (white)	1,044		2,484	859		2,386			Women type T (black) simple			18001.48			14818.04
104		Women crop top (black)		4,790	2,410		5,309	2,654			Women type T (white) simple	9000.72	4212.4	9840.74	10423.85	4342.15	10523.05
105		Women dress (black) long	19,902			15,971					Women type T simple (red)		2522.4			2387.41
106		Women dress (red) ****tail	38,875	25,668	15,562	39,197	27,302	13,117		Werner Strauss	Laptop bag (black)			11160.4			12784.04
107		Women type T (black) simple			18,001			14,818			Laptop bag (red)	32700.39	21420.03	16560.3	27278	23930.82	15969.44
108		Women type T (white) simple	9,001	4,212	9,841	10,424	4,342	10,523			Men dress shirt (black)	21550.8			24109.99
109		Women type T simple (red)		2,522			2,387				Men dress shirt (gray)	14375.57	20976.14		14251.46	24817.62
110	Liebher Total		174,541	171,732	148,606	175,705	161,102	151,411			Men dress shirt (white)			7100.33			6385.96
111	Werner Strauss	Laptop bag (black)			11,160			12,784			Men shorts (black)	17227.07	3168.24	6714.4	15003.97	2795.04	7422.7
112		Laptop bag (red)	32,700	21,420	16,560	27,278	23,931	15,969			Men shorts (gray)		4446.44	7200.4		4426.74	6331.93
113		Men dress shirt (black)	21,551			24,110					Men type T (Brown) simple	6955.78	3705.4		6209.79	4171.3
114		Men dress shirt (gray)	14,376	20,976		14,251	24,818				Men type T simple (black)		2964.17	13548.94		2634.37	14267.29
115		Men dress shirt (white)			7,100			6,386			Men type T simple (white)	10260.8		3252.16	10459.24		3579.32
116		Men shorts (black)	17,227	3,168	6,714	15,004	2,795	7,423			Smartphone case diamond	10800.4		4300.35	11403.76		3864.57
117		Men shorts (gray)		4,446	7,200		4,427	6,332			Smartphone case simple	6120.14	8440.27		5362.35	7818.7
118		Men type T (Brown) simple	6,956	3,705		6,210	4,171				Unisex tank top (black)	1500.4		2496.04	1295.59		2336.39
119		Men type T simple (black)		2,964	13,549		2,634	14,267			Unisex tank top (white)		2364.4	2022.26		1994.92	2297.79
120		Men type T simple (white)	10,261		3,252	10,459		3,579			Women crop top (black)	8570.34	4030.8	4330.39	9530.76	3413.38	3731.74
121		Smartphone case diamond	10,800		4,300	11,404		3,865			Women dress (black) long		67084.81	18414.4		53768.96	20638.99
122		Smartphone case simple	6,120	8,440		5,362	7,819				Women dress (red) ****tail	18724.4	9796.4		16655.97	11105.46
123		Unisex tank top (black)	1,500		2,496	1,296		2,336			Women type T (black) simple			6816.57			5875.25
124		Unisex tank top (white)		2,364	2,022		1,995	2,298			Women type T (white) simple			2604.26			2708.21
125		Women crop top (black)	8,570	4,031	4,330	9,531	3,413	3,732			Women type T simple (red)		10907.44			9505.15
126		Women dress (black) long		67,085	18,414		53,769	20,639
127		Women dress (red) ****tail	18,724	9,796		16,656	11,105
128		Women type T (black) simple			6,817			5,875
129		Women type T (white) simple			2,604			2,708
130		Women type T simple (red)		10,907			9,505
131	Werner Strauss Total		148,786	159,305	106,521	141,561	150,382	108,194
132	Grand Total		1,111,673	1,179,911	968,102	1,117,215	1,173,556	981,494
133
Before

Cell Formulas
Range	Formula
J4	J4	=FORMULATEXT(J5)
J5:Q125	J5	=LET(a,A5:H132,b,IF(a="","",a),FILTER(b,NOT(ISNUMBER(SEARCH("total",TAKE(b,,1))))))
C27:H27,C131:H131,C110:H110,C89:H89,C48:H48	C27	=SUM(C7:C26)
C68:H68	C68	=SUM(C49:C67)
C132:H132	C132	=C131+C110+C89+C68+C48+C27
Dynamic array formulas.

 

[Xlambda]

To be able to see all results in full (too many cells for mini-sheet), and to check accuracy btwn the 2 methods, download Leila's workbook for full lambda experience.

Advanced_UnPivot_XelPlus (1).xlsx
	R	S	T	U	V	W	X	Y
1
2
3		applying UNPIVOT to the previous filtered data, and sorting to get same results as Leila
4
5		=SORT(UNPIVOT(J7:K125,L5:Q6,L7:Q125),{3,4,1,2})
6		Aida GmbH	Laptop bag (black)	Actual	01-01-20	23700.4
7		Aida GmbH	Laptop bag (red)	Actual	01-01-20	60721.2
8		Aida GmbH	Men dress shirt (black)	Actual	01-01-20	10425.1
9		Aida GmbH	Men dress shirt (gray)	Actual	01-01-20	17500.46
10		Aida GmbH	Men dress shirt (white)	Actual	01-01-20	4775.19
11		Aida GmbH	Men shorts (black)	Actual	01-01-20	12996.8
12		Aida GmbH	Men shorts (gray)	Actual	01-01-20	14526.67
13		Aida GmbH	Men type T (Brown) simple	Actual	01-01-20	2548.4
14		Aida GmbH	Men type T simple (black)	Actual	01-01-20	6588.55
15		Aida GmbH	Men type T simple (white)	Actual	01-01-20	4788.47
16		Aida GmbH	Smartphone case diamond	Actual	01-01-20	9875.07
17		Aida GmbH	Smartphone case simple	Actual	01-01-20	35041.75
18		Aida GmbH	Unisex tank top (black)	Actual	01-01-20	1356.4
19		Aida GmbH	Unisex tank top (white)	Actual	01-01-20	4297.16
20		Aida GmbH	Women crop top (black)	Actual	01-01-20	6150.82
21		Aida GmbH	Women dress (black) long	Actual	01-01-20	11594.4
22		Aida GmbH	Women dress (red) ****tail	Actual	01-01-20	23374.4
23		Aida GmbH	Women type T (black) simple	Actual	01-01-20	1920.4
24		Dellicia	Laptop bag (black)	Actual	01-01-20	22200.2
25		Dellicia	Laptop bag (red)	Actual	01-01-20	26100.4
26		Dellicia	Men dress shirt (gray)	Actual	01-01-20	18875.65
27		Dellicia	Men dress shirt (white)	Actual	01-01-20	4850.4
28		Dellicia	Men shorts (black)	Actual	01-01-20	14382.58
29		Dellicia	Men shorts (gray)	Actual	01-01-20	20395.38
30		Dellicia	Men type T (Brown) simple	Actual	01-01-20	14340.48
31		Dellicia	Men type T simple (black)	Actual	01-01-20	13008.96
32		Dellicia	Men type T simple (white)	Actual	01-01-20	4656
33		Dellicia	Smartphone case simple	Actual	01-01-20	5120.4
34		Dellicia	Unisex tank top (black)	Actual	01-01-20	1740.4
35		Dellicia	Unisex tank top (white)	Actual	01-01-20	1458.4
36		Dellicia	Women crop top (black)	Actual	01-01-20	13321.38
37		Dellicia	Women dress (black) long	Actual	01-01-20	37386.5
38		Dellicia	Women dress (red) ****tail	Actual	01-01-20	11346.4
39		Dellicia	Women type T simple (red)	Actual	01-01-20	7904.61
40		Edson	Laptop bag (black)	Actual	01-01-20	15000.48
41		Edson	Men dress shirt (black)	Actual	01-01-20	9375.25
42		Edson	Men dress shirt (gray)	Actual	01-01-20	7300.16
43		Edson	Men dress shirt (white)	Actual	01-01-20	10300.36
44		Edson	Men type T simple (black)	Actual	01-01-20	2472.4
45		Erma	Laptop bag (black)	Actual	01-01-20	30540.8
46		Erma	Men dress shirt (black)	Actual	01-01-20	8775.34
47		Erma	Men dress shirt (gray)	Actual	01-01-20	17400.4
48		Erma	Men dress shirt (white)	Actual	01-01-20	31802.04
49		Erma	Men shorts (black)	Actual	01-01-20	3834.4
50		Erma	Men shorts (gray)	Actual	01-01-20	13770.54
51		Erma	Men type T (Brown) simple	Actual	01-01-20	8164.84
52		Erma	Men type T simple (black)	Actual	01-01-20	8292.8
53		Erma	Men type T simple (white)	Actual	01-01-20	8244.8
54		Erma	Smartphone case diamond	Actual	01-01-20	12000.84
55		Erma	Smartphone case simple	Actual	01-01-20	6660.46
56		Erma	Unisex tank top (black)	Actual	01-01-20	1602.48
57		Erma	Women crop top (black)	Actual	01-01-20	3030.01
58		Erma	Women dress (black) long	Actual	01-01-20	55801
59		Erma	Women dress (red) ****tail	Actual	01-01-20	43276.72
60		Erma	Women type T (black) simple	Actual	01-01-20	8172.59
61		Erma	Women type T (white) simple	Actual	01-01-20	5352.55
62		Erma	Women type T simple (red)	Actual	01-01-20	7904.68
63		Liebher	Laptop bag (black)	Actual	01-01-20	20040.03
64		Liebher	Men dress shirt (black)	Actual	01-01-20	11250.23
65		Liebher	Men dress shirt (gray)	Actual	01-01-20	16475.8
66		Liebher	Men shorts (black)	Actual	01-01-20	5904.4
67		Liebher	Men shorts (gray)	Actual	01-01-20	13303.17
68		Liebher	Men type T simple (black)	Actual	01-01-20	4068.17
69		Liebher	Men type T simple (white)	Actual	01-01-20	2760.25
70		Liebher	Smartphone case diamond	Actual	01-01-20	30476.67
71		Liebher	Unisex tank top (black)	Actual	01-01-20	1440.4
72		Liebher	Unisex tank top (white)	Actual	01-01-20	1044.4
73		Liebher	Women dress (black) long	Actual	01-01-20	19902.4
74		Liebher	Women dress (red) ****tail	Actual	01-01-20	38874.68
75		Liebher	Women type T (white) simple	Actual	01-01-20	9000.72
76		Werner Strauss	Laptop bag (red)	Actual	01-01-20	32700.39
77		Werner Strauss	Men dress shirt (black)	Actual	01-01-20	21550.8
78		Werner Strauss	Men dress shirt (gray)	Actual	01-01-20	14375.57
79		Werner Strauss	Men shorts (black)	Actual	01-01-20	17227.07
80		Werner Strauss	Men type T (Brown) simple	Actual	01-01-20	6955.78
81		Werner Strauss	Men type T simple (white)	Actual	01-01-20	10260.8
82		Werner Strauss	Smartphone case diamond	Actual	01-01-20	10800.4
83		Werner Strauss	Smartphone case simple	Actual	01-01-20	6120.14
84		Werner Strauss	Unisex tank top (black)	Actual	01-01-20	1500.4
85		Werner Strauss	Women crop top (black)	Actual	01-01-20	8570.34
86		Werner Strauss	Women dress (red) ****tail	Actual	01-01-20	18724.4
87		Aida GmbH	Laptop bag (black)	Actual	01-02-20	55801.24
88		Aida GmbH	Men dress shirt (gray)	Actual	01-02-20	25676.25
89		Aida GmbH	Men shorts (black)	Actual	01-02-20	3672.4
90		Aida GmbH	Men shorts (gray)	Actual	01-02-20	11646.68
91		Aida GmbH	Men type T (Brown) simple	Actual	01-02-20	9140.2
92		Aida GmbH	Men type T simple (black)	Actual	01-02-20	2496.24
93		Aida GmbH	Men type T simple (white)	Actual	01-02-20	4548.41
94		Aida GmbH	Smartphone case diamond	Actual	01-02-20	15100.27
95		Aida GmbH	Smartphone case simple	Actual	01-02-20	14040.8
96		Aida GmbH	Unisex tank top (black)	Actual	01-02-20	4170.29
97		Aida GmbH	Unisex tank top (white)	Actual	01-02-20	5161.2
98		Aida GmbH	Women crop top (black)	Actual	01-02-20	3810.42
99		Aida GmbH	Women dress (black) long	Actual	01-02-20	30504.63
100		Aida GmbH	Women dress (red) ****tail	Actual	01-02-20	45880.54
101		Aida GmbH	Women type T (black) simple	Actual	01-02-20	4308.4
102		Aida GmbH	Women type T simple (red)	Actual	01-02-20	4277.19
103		Dellicia	Laptop bag (black)	Actual	01-02-20	36060.5
104		Dellicia	Laptop bag (red)	Actual	01-02-20	61801.2
105		Dellicia	Men dress shirt (black)	Actual	01-02-20	28101.74
106		Dellicia	Men dress shirt (gray)	Actual	01-02-20	16325.62
107		Dellicia	Men dress shirt (white)	Actual	01-02-20	10250.4
108		Dellicia	Men shorts (black)	Actual	01-02-20	5508.22
109		Dellicia	Men shorts (gray)	Actual	01-02-20	8208.63
110		Dellicia	Men type T (Brown) simple	Actual	01-02-20	13963.32
111		Dellicia	Men type T simple (white)	Actual	01-02-20	3060.4
112		Dellicia	Smartphone case diamond	Actual	01-02-20	6275.07
113		Dellicia	Smartphone case simple	Actual	01-02-20	11080.58
114		Dellicia	Unisex tank top (black)	Actual	01-02-20	2298.26
115		Dellicia	Unisex tank top (white)	Actual	01-02-20	3558.77
116		Dellicia	Women crop top (black)	Actual	01-02-20	7650.99
117		Dellicia	Women dress (black) long	Actual	01-02-20	23436.4
118		Dellicia	Women dress (red) ****tail	Actual	01-02-20	18166.4
119		Dellicia	Women type T (black) simple	Actual	01-02-20	4080.34
120		Edson	Men dress shirt (black)	Actual	01-02-20	10450.25
121		Edson	Smartphone case diamond	Actual	01-02-20	10700.01
122		Edson	Women dress (black) long	Actual	01-02-20	16120.4
123		Edson	Women type T (black) simple	Actual	01-02-20	4584.8
124		Erma	Laptop bag (black)	Actual	01-02-20	15840.01
125		Erma	Laptop bag (red)	Actual	01-02-20	80401.27
126		Erma	Men dress shirt (black)	Actual	01-02-20	4700.4
127		Erma	Men dress shirt (gray)	Actual	01-02-20	6325.4
128		Erma	Men dress shirt (white)	Actual	01-02-20	45401.84
129		Erma	Men shorts (black)	Actual	01-02-20	11142.8
130		Erma	Men shorts (gray)	Actual	01-02-20	3456.4
131		Erma	Men type T simple (white)	Actual	01-02-20	9193.02
132		Erma	Smartphone case diamond	Actual	01-02-20	4375.4
133		Erma	Smartphone case simple	Actual	01-02-20	19240.85
134		Erma	Unisex tank top (white)	Actual	01-02-20	7393.39
135		Erma	Women crop top (black)	Actual	01-02-20	9870.96
136		Erma	Women dress (black) long	Actual	01-02-20	63923.27
137		Erma	Women dress (red) ****tail	Actual	01-02-20	20894.4
138		Erma	Women type T simple (red)	Actual	01-02-20	4797.4
139		Liebher	Laptop bag (red)	Actual	01-02-20	49320.76
140		Liebher	Men dress shirt (black)	Actual	01-02-20	12600.44
141		Liebher	Men dress shirt (gray)	Actual	01-02-20	20075.5
142		Liebher	Men dress shirt (white)	Actual	01-02-20	12200.61
143		Liebher	Men shorts (black)	Actual	01-02-20	5832.33
144		Liebher	Men shorts (gray)	Actual	01-02-20	7650.4
145		Liebher	Men type T simple (black)	Actual	01-02-20	2856.13
146		Liebher	Men type T simple (white)	Actual	01-02-20	5172.4
147		Liebher	Smartphone case diamond	Actual	01-02-20	9875.8
148		Liebher	Smartphone case simple	Actual	01-02-20	5300.02
149		Liebher	Unisex tank top (black)	Actual	01-02-20	3654.4
150		Liebher	Women crop top (black)	Actual	01-02-20	4790.44
151		Liebher	Women dress (red) ****tail	Actual	01-02-20	25668.17
152		Liebher	Women type T (white) simple	Actual	01-02-20	4212.4
153		Liebher	Women type T simple (red)	Actual	01-02-20	2522.4
154		Werner Strauss	Laptop bag (red)	Actual	01-02-20	21420.03
155		Werner Strauss	Men dress shirt (gray)	Actual	01-02-20	20976.14
Before

Cell Formulas
Range	Formula
S5	S5	=FORMULATEXT(S6)
S6:W483	S6	=SORT(UNPIVOT(J7:K125,L5:Q6,L7:Q125),{3,4,1,2})
Dynamic array formulas.

Advanced_UnPivot_XelPlus (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1							checking PQ results vs UNPIVOT results
2							=AND(G6#=DataProper)
3	proper data set, Leila's PQ result						TRUE
4
5	Customer	Article	Scenario	Date	Sales Value		=SORT(UNPIVOT(Before!J7:K125,Before!L5:Q6,Before!L7:Q125),{3,4,1,2})
6	Aida GmbH	Laptop bag (black)	Actual	01-01-20	23700.4		Aida GmbH	Laptop bag (black)	Actual	43831	23700.4
7	Aida GmbH	Laptop bag (red)	Actual	01-01-20	60721.2		Aida GmbH	Laptop bag (red)	Actual	43831	60721.2
8	Aida GmbH	Men dress shirt (black)	Actual	01-01-20	10425.1		Aida GmbH	Men dress shirt (black)	Actual	43831	10425.1
9	Aida GmbH	Men dress shirt (gray)	Actual	01-01-20	17500.46		Aida GmbH	Men dress shirt (gray)	Actual	43831	17500.46
10	Aida GmbH	Men dress shirt (white)	Actual	01-01-20	4775.19		Aida GmbH	Men dress shirt (white)	Actual	43831	4775.19
11	Aida GmbH	Men shorts (black)	Actual	01-01-20	12996.8		Aida GmbH	Men shorts (black)	Actual	43831	12996.8
12	Aida GmbH	Men shorts (gray)	Actual	01-01-20	14526.67		Aida GmbH	Men shorts (gray)	Actual	43831	14526.67
13	Aida GmbH	Men type T (Brown) simple	Actual	01-01-20	2548.4		Aida GmbH	Men type T (Brown) simple	Actual	43831	2548.4
14	Aida GmbH	Men type T simple (black)	Actual	01-01-20	6588.55		Aida GmbH	Men type T simple (black)	Actual	43831	6588.55
15	Aida GmbH	Men type T simple (white)	Actual	01-01-20	4788.47		Aida GmbH	Men type T simple (white)	Actual	43831	4788.47
16	Aida GmbH	Smartphone case diamond	Actual	01-01-20	9875.07		Aida GmbH	Smartphone case diamond	Actual	43831	9875.07
17	Aida GmbH	Smartphone case simple	Actual	01-01-20	35041.75		Aida GmbH	Smartphone case simple	Actual	43831	35041.75
18	Aida GmbH	Unisex tank top (black)	Actual	01-01-20	1356.4		Aida GmbH	Unisex tank top (black)	Actual	43831	1356.4
19	Aida GmbH	Unisex tank top (white)	Actual	01-01-20	4297.16		Aida GmbH	Unisex tank top (white)	Actual	43831	4297.16
20	Aida GmbH	Women crop top (black)	Actual	01-01-20	6150.82		Aida GmbH	Women crop top (black)	Actual	43831	6150.82
21	Aida GmbH	Women dress (black) long	Actual	01-01-20	11594.4		Aida GmbH	Women dress (black) long	Actual	43831	11594.4
22	Aida GmbH	Women dress (red) ****tail	Actual	01-01-20	23374.4		Aida GmbH	Women dress (red) ****tail	Actual	43831	23374.4
23	Aida GmbH	Women type T (black) simple	Actual	01-01-20	1920.4		Aida GmbH	Women type T (black) simple	Actual	43831	1920.4
24	Dellicia	Laptop bag (black)	Actual	01-01-20	22200.2		Dellicia	Laptop bag (black)	Actual	43831	22200.2
25	Dellicia	Laptop bag (red)	Actual	01-01-20	26100.4		Dellicia	Laptop bag (red)	Actual	43831	26100.4
26	Dellicia	Men dress shirt (gray)	Actual	01-01-20	18875.65		Dellicia	Men dress shirt (gray)	Actual	43831	18875.65
27	Dellicia	Men dress shirt (white)	Actual	01-01-20	4850.4		Dellicia	Men dress shirt (white)	Actual	43831	4850.4
28	Dellicia	Men shorts (black)	Actual	01-01-20	14382.58		Dellicia	Men shorts (black)	Actual	43831	14382.58
29	Dellicia	Men shorts (gray)	Actual	01-01-20	20395.38		Dellicia	Men shorts (gray)	Actual	43831	20395.38
30	Dellicia	Men type T (Brown) simple	Actual	01-01-20	14340.48		Dellicia	Men type T (Brown) simple	Actual	43831	14340.48
31	Dellicia	Men type T simple (black)	Actual	01-01-20	13008.96		Dellicia	Men type T simple (black)	Actual	43831	13008.96
32	Dellicia	Men type T simple (white)	Actual	01-01-20	4656		Dellicia	Men type T simple (white)	Actual	43831	4656
33	Dellicia	Smartphone case simple	Actual	01-01-20	5120.4		Dellicia	Smartphone case simple	Actual	43831	5120.4
34	Dellicia	Unisex tank top (black)	Actual	01-01-20	1740.4		Dellicia	Unisex tank top (black)	Actual	43831	1740.4
35	Dellicia	Unisex tank top (white)	Actual	01-01-20	1458.4		Dellicia	Unisex tank top (white)	Actual	43831	1458.4
36	Dellicia	Women crop top (black)	Actual	01-01-20	13321.38		Dellicia	Women crop top (black)	Actual	43831	13321.38
37	Dellicia	Women dress (black) long	Actual	01-01-20	37386.5		Dellicia	Women dress (black) long	Actual	43831	37386.5
38	Dellicia	Women dress (red) ****tail	Actual	01-01-20	11346.4		Dellicia	Women dress (red) ****tail	Actual	43831	11346.4
39	Dellicia	Women type T simple (red)	Actual	01-01-20	7904.61		Dellicia	Women type T simple (red)	Actual	43831	7904.61
40	Edson	Laptop bag (black)	Actual	01-01-20	15000.48		Edson	Laptop bag (black)	Actual	43831	15000.48
41	Edson	Men dress shirt (black)	Actual	01-01-20	9375.25		Edson	Men dress shirt (black)	Actual	43831	9375.25
42	Edson	Men dress shirt (gray)	Actual	01-01-20	7300.16		Edson	Men dress shirt (gray)	Actual	43831	7300.16
43	Edson	Men dress shirt (white)	Actual	01-01-20	10300.36		Edson	Men dress shirt (white)	Actual	43831	10300.36
44	Edson	Men type T simple (black)	Actual	01-01-20	2472.4		Edson	Men type T simple (black)	Actual	43831	2472.4
45	Erma	Laptop bag (black)	Actual	01-01-20	30540.8		Erma	Laptop bag (black)	Actual	43831	30540.8
46	Erma	Men dress shirt (black)	Actual	01-01-20	8775.34		Erma	Men dress shirt (black)	Actual	43831	8775.34
47	Erma	Men dress shirt (gray)	Actual	01-01-20	17400.4		Erma	Men dress shirt (gray)	Actual	43831	17400.4
48	Erma	Men dress shirt (white)	Actual	01-01-20	31802.04		Erma	Men dress shirt (white)	Actual	43831	31802.04
49	Erma	Men shorts (black)	Actual	01-01-20	3834.4		Erma	Men shorts (black)	Actual	43831	3834.4
50	Erma	Men shorts (gray)	Actual	01-01-20	13770.54		Erma	Men shorts (gray)	Actual	43831	13770.54
51	Erma	Men type T (Brown) simple	Actual	01-01-20	8164.84		Erma	Men type T (Brown) simple	Actual	43831	8164.84
52	Erma	Men type T simple (black)	Actual	01-01-20	8292.8		Erma	Men type T simple (black)	Actual	43831	8292.8
53	Erma	Men type T simple (white)	Actual	01-01-20	8244.8		Erma	Men type T simple (white)	Actual	43831	8244.8
54	Erma	Smartphone case diamond	Actual	01-01-20	12000.84		Erma	Smartphone case diamond	Actual	43831	12000.84
55	Erma	Smartphone case simple	Actual	01-01-20	6660.46		Erma	Smartphone case simple	Actual	43831	6660.46
56	Erma	Unisex tank top (black)	Actual	01-01-20	1602.48		Erma	Unisex tank top (black)	Actual	43831	1602.48
57	Erma	Women crop top (black)	Actual	01-01-20	3030.01		Erma	Women crop top (black)	Actual	43831	3030.01
58	Erma	Women dress (black) long	Actual	01-01-20	55801		Erma	Women dress (black) long	Actual	43831	55801
59	Erma	Women dress (red) ****tail	Actual	01-01-20	43276.72		Erma	Women dress (red) ****tail	Actual	43831	43276.72
60	Erma	Women type T (black) simple	Actual	01-01-20	8172.59		Erma	Women type T (black) simple	Actual	43831	8172.59
61	Erma	Women type T (white) simple	Actual	01-01-20	5352.55		Erma	Women type T (white) simple	Actual	43831	5352.55
62	Erma	Women type T simple (red)	Actual	01-01-20	7904.68		Erma	Women type T simple (red)	Actual	43831	7904.68
63	Liebher	Laptop bag (black)	Actual	01-01-20	20040.03		Liebher	Laptop bag (black)	Actual	43831	20040.03
64	Liebher	Men dress shirt (black)	Actual	01-01-20	11250.23		Liebher	Men dress shirt (black)	Actual	43831	11250.23
65	Liebher	Men dress shirt (gray)	Actual	01-01-20	16475.8		Liebher	Men dress shirt (gray)	Actual	43831	16475.8
66	Liebher	Men shorts (black)	Actual	01-01-20	5904.4		Liebher	Men shorts (black)	Actual	43831	5904.4
67	Liebher	Men shorts (gray)	Actual	01-01-20	13303.17		Liebher	Men shorts (gray)	Actual	43831	13303.17
68	Liebher	Men type T simple (black)	Actual	01-01-20	4068.17		Liebher	Men type T simple (black)	Actual	43831	4068.17
69	Liebher	Men type T simple (white)	Actual	01-01-20	2760.25		Liebher	Men type T simple (white)	Actual	43831	2760.25
70	Liebher	Smartphone case diamond	Actual	01-01-20	30476.67		Liebher	Smartphone case diamond	Actual	43831	30476.67
71	Liebher	Unisex tank top (black)	Actual	01-01-20	1440.4		Liebher	Unisex tank top (black)	Actual	43831	1440.4
72	Liebher	Unisex tank top (white)	Actual	01-01-20	1044.4		Liebher	Unisex tank top (white)	Actual	43831	1044.4
73	Liebher	Women dress (black) long	Actual	01-01-20	19902.4		Liebher	Women dress (black) long	Actual	43831	19902.4
74	Liebher	Women dress (red) ****tail	Actual	01-01-20	38874.68		Liebher	Women dress (red) ****tail	Actual	43831	38874.68
75	Liebher	Women type T (white) simple	Actual	01-01-20	9000.72		Liebher	Women type T (white) simple	Actual	43831	9000.72
76	Werner Strauss	Laptop bag (red)	Actual	01-01-20	32700.39		Werner Strauss	Laptop bag (red)	Actual	43831	32700.39
77	Werner Strauss	Men dress shirt (black)	Actual	01-01-20	21550.8		Werner Strauss	Men dress shirt (black)	Actual	43831	21550.8
78	Werner Strauss	Men dress shirt (gray)	Actual	01-01-20	14375.57		Werner Strauss	Men dress shirt (gray)	Actual	43831	14375.57
79	Werner Strauss	Men shorts (black)	Actual	01-01-20	17227.07		Werner Strauss	Men shorts (black)	Actual	43831	17227.07
80	Werner Strauss	Men type T (Brown) simple	Actual	01-01-20	6955.78		Werner Strauss	Men type T (Brown) simple	Actual	43831	6955.78
81	Werner Strauss	Men type T simple (white)	Actual	01-01-20	10260.8		Werner Strauss	Men type T simple (white)	Actual	43831	10260.8
82	Werner Strauss	Smartphone case diamond	Actual	01-01-20	10800.4		Werner Strauss	Smartphone case diamond	Actual	43831	10800.4
83	Werner Strauss	Smartphone case simple	Actual	01-01-20	6120.14		Werner Strauss	Smartphone case simple	Actual	43831	6120.14
84	Werner Strauss	Unisex tank top (black)	Actual	01-01-20	1500.4		Werner Strauss	Unisex tank top (black)	Actual	43831	1500.4
85	Werner Strauss	Women crop top (black)	Actual	01-01-20	8570.34		Werner Strauss	Women crop top (black)	Actual	43831	8570.34
86	Werner Strauss	Women dress (red) ****tail	Actual	01-01-20	18724.4		Werner Strauss	Women dress (red) ****tail	Actual	43831	18724.4
87	Aida GmbH	Laptop bag (black)	Actual	01-02-20	55801.24		Aida GmbH	Laptop bag (black)	Actual	43862	55801.24
88	Aida GmbH	Men dress shirt (gray)	Actual	01-02-20	25676.25		Aida GmbH	Men dress shirt (gray)	Actual	43862	25676.25
89	Aida GmbH	Men shorts (black)	Actual	01-02-20	3672.4		Aida GmbH	Men shorts (black)	Actual	43862	3672.4
90	Aida GmbH	Men shorts (gray)	Actual	01-02-20	11646.68		Aida GmbH	Men shorts (gray)	Actual	43862	11646.68
91	Aida GmbH	Men type T (Brown) simple	Actual	01-02-20	9140.2		Aida GmbH	Men type T (Brown) simple	Actual	43862	9140.2
92	Aida GmbH	Men type T simple (black)	Actual	01-02-20	2496.24		Aida GmbH	Men type T simple (black)	Actual	43862	2496.24
93	Aida GmbH	Men type T simple (white)	Actual	01-02-20	4548.41		Aida GmbH	Men type T simple (white)	Actual	43862	4548.41
94	Aida GmbH	Smartphone case diamond	Actual	01-02-20	15100.27		Aida GmbH	Smartphone case diamond	Actual	43862	15100.27
95	Aida GmbH	Smartphone case simple	Actual	01-02-20	14040.8		Aida GmbH	Smartphone case simple	Actual	43862	14040.8
96	Aida GmbH	Unisex tank top (black)	Actual	01-02-20	4170.29		Aida GmbH	Unisex tank top (black)	Actual	43862	4170.29
97	Aida GmbH	Unisex tank top (white)	Actual	01-02-20	5161.2		Aida GmbH	Unisex tank top (white)	Actual	43862	5161.2
98	Aida GmbH	Women crop top (black)	Actual	01-02-20	3810.42		Aida GmbH	Women crop top (black)	Actual	43862	3810.42
99	Aida GmbH	Women dress (black) long	Actual	01-02-20	30504.63		Aida GmbH	Women dress (black) long	Actual	43862	30504.63
100	Aida GmbH	Women dress (red) ****tail	Actual	01-02-20	45880.54		Aida GmbH	Women dress (red) ****tail	Actual	43862	45880.54
101	Aida GmbH	Women type T (black) simple	Actual	01-02-20	4308.4		Aida GmbH	Women type T (black) simple	Actual	43862	4308.4
102	Aida GmbH	Women type T simple (red)	Actual	01-02-20	4277.19		Aida GmbH	Women type T simple (red)	Actual	43862	4277.19
103	Dellicia	Laptop bag (black)	Actual	01-02-20	36060.5		Dellicia	Laptop bag (black)	Actual	43862	36060.5
104	Dellicia	Laptop bag (red)	Actual	01-02-20	61801.2		Dellicia	Laptop bag (red)	Actual	43862	61801.2
105	Dellicia	Men dress shirt (black)	Actual	01-02-20	28101.74		Dellicia	Men dress shirt (black)	Actual	43862	28101.74
106	Dellicia	Men dress shirt (gray)	Actual	01-02-20	16325.62		Dellicia	Men dress shirt (gray)	Actual	43862	16325.62
107	Dellicia	Men dress shirt (white)	Actual	01-02-20	10250.4		Dellicia	Men dress shirt (white)	Actual	43862	10250.4
ProperData

Cell Formulas
Range	Formula
G2,G5	G2	=FORMULATEXT(G3)
G3	G3	=AND(G6#=DataProper)
G6:K483	G6	=SORT(UNPIVOT(Before!J7:K125,Before!L5:Q6,Before!L7:Q125),{3,4,1,2})
Dynamic array formulas.

 

[Xlambda]

Another UNPIVOT challenge latest Goodly's YT:

There are 3 excel files containing data, and an output data file, of final results. Downloadable link YT's description.
For presentation purposes, each file has its UNPIVOT formula, and output file stacks all of them. Also, a single cell formula can be used. With all the workbooks open, a single cell formula for final results took 2 min to edit. (many ranges)

S1.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	Store-1
2	INCOME STATEMENT AS ON 31ST DECEMBER 2022
3
4				2022	%	2021	%	2020	%	2019	2018	2017	2016	2015	2014	2013	2012	2011	2010	2009		2008		2007		2006
5	SALES	1		7,665,499.64		6,877,877.23		7,915,191.08		6,390,653.09	6,180,665.04	6,522,702.84	7,407,254.81	7,881,384.08	7,900,683.67	7,623,621.69	7,474,840.01	6,046,665.40	4,797,450.50	4,238,988.64		2,569,998.94		2,107,365.63		1,780,543.73
6	LESS: COST OF SALES		61.67	(4,727,358.71)	63.27	(4,351,331.30)	65.5	(5,184,178.21)	65.15	(4,163,713.07)	(4,075,940.66)	(4,372,549.45)	(5,086,478.49)	(5,435,427.23)	(5,512,907.95)	(5,389,529.74)	(5,366,470.63)	(4,104,185.74)	(3,289,682.02)	(2,923,228.32)		(1,858,275.51)		(1,487,724.66)		(1,302,388.62)
7	GROSS PROFIT	2	38.33	2,938,140.93	36.73	2,526,545.93	34.50343577	2,731,012.87	34.85	2,226,940.02	2,104,724.38	2,150,153.39	2,320,776.32	2,445,956.85	2,387,775.72	2,234,091.95	2,108,369.38	1,942,479.66	1,507,768.48	1,315,760.32		711,723.43		619,640.97		478,155.11
8	OTHER INCOME		-		-						5,864.71	36,411.96	112,478.14	84,466.61	89,798.14	115,561.64	70,428.53	102,593.35	75,959.84	70,796.89		39,572.69		34,851.92		202.08
9	SALARY PAID TO EMPLOYEES		10.09	(773,734.02)	6.84	(470,174.68)	6.3	(499,496.00)	6.84	(437,017.25)	(488,748.00)	(405,440.00)	(434,587.00)	(440,406.00)	(454,766.00)	(435,582.55)	(432,781.83)	(371,901.50)	(324,723.00)	(259,957.92)		(202,196.00)		(167,964.00)		(159,691.00)
10	GENERAL & ADM EXPENSES		14.11	(1,081,932.85)	17.56	(1,207,622.64)	11.8	(930,638.52)	14.27	(912,245.14)	(797,013.37)	(831,214.57)	(828,880.69)	(905,344.09)	(736,150.36)	(686,768.01)	(619,857.31)	(526,845.56)	(484,674.72)	(392,560.92)		(306,506.30)		(246,439.12)		(178,485.55)
11	EBITDA	3	14.12	1,082,474.06	12.34	848,748.61	16.43521094	1,300,878.35	13.73	877,677.63	824,827.72	949,910.78	1,169,786.77	1,184,673.37	1,286,657.50	1,227,303.03	1,126,158.77	1,146,325.95	774,330.60	734,038.37		242,593.82		240,089.77		140,180.64
12	LESS: SUNDRY EXPENSES		-		-						(15,705.99)	(21,532.62)	(17,608.58)	(12,851.28)	(19,427.66)	(21,995.90)	(27,127.13)	(16,120.06)	(15,118.76)	(14,097.53)		(11,762.00)		(10,893.00)
13	LESS: MANAGEMENT FEES		1.21	(92,641.04)	1.02	(69,958.35)	1.48	(117,294.97)	1.31	(83,399.23)	(76,884.87)	(88,216.93)	(109,483.00)	(111,349.61)	(99,276.07)	(94,424.98)	(86,099.24)	(88,541.47)	(59,477.43)	(53,995.57)		(17,312.38)		(15,900.00)		(6,658.00)
14	LESS DEPRECIATION + AMOR		0.84	(64,304.92)	0.91	(62,620.87)	-0.148458071	(11,750.74)
15	PROFIT BEFORE TAX	4	12.07	925,528.10	10.41	716,169.39	14.80485598	1,171,832.64
16	LESS TAX PAYABLE		4.90	(43,232.49)		(49,899.38)	4.9	(54,737.65)
17	NET PROFIT	5	11.51	882,295.61	9.69	666,270.01	14.11330414	1,117,094.99	12.43	794,278.40	732,236.86	840,161.23	1,042,695.19	1,060,472.48	1,167,953.77	1,110,882.15	1,012,932.40	1,041,664.42	699,734.41	665,945.27		213,519.44		213,296.77		133,522.64
18
19	=IFNA(HSTACK(UNPIVOT(A5:A17,B4:Z4,B5:Z17),"Store 1"),"Store 1")
20	SALES		1	Store 1
21	SALES	2022	7665499.64	Store 1
22	SALES	2021	6877877.23	Store 1
23	SALES	2020	7915191.08	Store 1
24	SALES	2019	6390653.09	Store 1
25	SALES	2018	6180665.04	Store 1
26	SALES	2017	6522702.84	Store 1
27	SALES	2016	7407254.81	Store 1
28	SALES	2015	7881384.08	Store 1
29	SALES	2014	7900683.67	Store 1
30	SALES	2013	7623621.69	Store 1
31	SALES	2012	7474840.01	Store 1
32	SALES	2011	6046665.4	Store 1
33	SALES	2010	4797450.5	Store 1
34	SALES	2009	4238988.64	Store 1
35	SALES	2008	2569998.94	Store 1
36	SALES	2007	2107365.63	Store 1
37	SALES	2006	1780543.73	Store 1
38	LESS: COST OF SALES		61.67	Store 1
39	LESS: COST OF SALES	2022	-4727358.71	Store 1
40	LESS: COST OF SALES	%	63.27	Store 1
41	LESS: COST OF SALES	2021	-4351331.3	Store 1
42	LESS: COST OF SALES	%	65.5	Store 1
43	LESS: COST OF SALES	2020	-5184178.21	Store 1
44	LESS: COST OF SALES	%	65.15316997	Store 1
45	LESS: COST OF SALES	2019	-4163713.07	Store 1
A

Cell Formulas
Range	Formula
I6	I6	=4163713.07/J5*100
D7,D17,F17,H17,Z7,X7,V7,F7,H7,J7:T7	D7	=SUM(D5:D6)
G7,I7	G7	=H7/H5*100
C7:C8,C15,C11:C12	C7	=D7/7665499.64*100
E7:E8,E17,E15,E11:E12	E7	=F7/6877877.23*100
I9	I9	=437017.25/J5*100
I10	I10	=912245.14/J5*100
D11,D15,F15,H15,Z11,X11,V11,F11,H11,J11:T11	D11	=SUM(D7:D10)
G11,I11	G11	=H11/H5*100
I13	I13	=83399.23/J5*100
G14	G14	=H14/H5*100
G15	G15	=H15/H5*100
C17,G17,I17	C17	=D17/D5*100
J17:T17,Z17,X17,V17	J17	=SUM(J11:J13)
A19	A19	=FORMULATEXT(A20)
A20:D234	A20	=IFNA(HSTACK(UNPIVOT(A5:A17,B4:Z4,B5:Z17),"Store 1"),"Store 1")
Dynamic array formulas.

S2.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
1	Store-2
2	INCOME STATEMENT AS ON DECEMBER 31ST 2022
3
4		Notes	%	2022	%	2021	%	2020	%	2019	2018	2017	2016	2015	2014	2013	2012	2011	2010	2009 6 MONTHS
5	SALES	1		2,530,323.81		2,889,934.17		3,540,411.51		3,381,540.62	3,269,185.76	3,599,922.76	3,498,175.82	4,448,249.48	6,982,799.80	6,544,735.71	6,082,094.58	4,560,255.73	2,529,657.04	546,549.00
6	COST OF SALE		63.55	(1,608,084.96)	64.00	(1,849,631.48)	66	(2,327,132.15)	64.64	(2,185,730.63)	(2,145,831.41)	(2,406,588.21)	(2,418,893.81)	(3,110,257.42)	(5,450,553.30)	(5,129,108.37)	(4,912,262.93)	(3,629,287.11)	(1,922,545.32)	(365,829.06)
7	GROSS PROFIT	2	36.45	922,238.85	36.00	1,040,302.69	34	1,213,279.36	35.36	1,195,809.99	1,123,354.35	1,193,334.55	1,079,282.01	1,337,992.06	1,532,246.50	1,415,627.34	1,169,831.65	930,968.62	607,111.72	180,719.94
8	OTHER INCOME		-		-						2,735.26	21,087.23	49,998.66	49,130.42	75,705.09	94,480.31	55,837.37	89,758.63	48,734.37	489.00
9			-		-
10	SALARY PAID TO EMPLOYEES		12.30	(311,333.82)	8.42	(243,399.32)	7.5	(263,797.00)	8.54	(288,652.00)	(276,525.00)	(283,727.00)	(248,001.00)	(399,332.00)	(392,446.00)	(292,831.25)	(259,997.55)	(237,603.50)	(188,915.00)	(99,532.00)
11	GENERAL & ADM EXPENSES		20.43	(516,933.09)	21.57	(623,223.04)	17.1	(606,697.80)	19.07	(644,862.02)	(608,287.41)	(668,132.91)	(631,974.28)	(727,778.97)	(802,377.84)	(709,027.32)	(574,701.14)	(527,450.79)	(480,748.57)	(190,287.93)
12	EBITDA	3	3.71	93,971.94	6.01	173,680.33	9.7	342,784.56	7.76	262,295.97	241,277.20	262,561.87	249,305.39	260,011.51	413,127.75	508,249.08	390,970.33	255,672.96	(13,817.48)	(108,610.99)
13	LESS SUNDRY EXPENSES		-		-						(14,212.59)	(14,603.83)	(11,280.37)	(12,307.51)	(15,407.41)	(19,650.30)	(23,937.50)	(19,969.94)	(16,974.49)
14	LESS: MANAGEMENT FEES		0.20	(7,026.32)	0.49	(14,096.71)	0.84	(29,804.35)	0.74	(24,924.05)	(21,576.28)	(23,561.62)	(22,617.76)	(23,537.49)	(31,157.82)	(38,277.32)	(28,753.72)	(7,544.25)
15	LESS DEPRECIATION		0.66	(16,749.35)	0.50	(14,367.01)	0.43	(15,220.54)
16	PROFIT BEFORE TAX	4	2.77	70,196.27	5.02	145,216.61	8.41031245	297,759.67
17	LESS TAX PAYABLE		4.90	(3,278.95)		(10,962.20)	4.9	(13,908.70)
18	NET PROFIT	5	2.64	66,917.32	4.65	134,254.41	8.02	283,850.97	7.02	237,371.92	205,488.33	224,396.42	215,407.26	224,166.51	366,562.52	450,321.46	338,279.11	228,158.77	(30,791.97)	(108,610.99)
19
20	=IFNA(HSTACK(UNPIVOT(A5:A18,B4:T4,B5:T18),"Store 2"),"Store 2")
21	SALES	Notes	1	Store 2
22	SALES	2022	2530323.81	Store 2
23	SALES	2021	2889934.17	Store 2
24	SALES	2020	3540411.51	Store 2
25	SALES	2019	3381540.62	Store 2
26	SALES	2018	3269185.76	Store 2
27	SALES	2017	3599922.76	Store 2
28	SALES	2016	3498175.82	Store 2
29	SALES	2015	4448249.48	Store 2
30	SALES	2014	6982799.8	Store 2
31	SALES	2013	6544735.71	Store 2
32	SALES	2012	6082094.58	Store 2
33	SALES	2011	4560255.73	Store 2
34	SALES	2010	2529657.04	Store 2
35	SALES	2009 6 MONTHS	546549	Store 2
36	COST OF SALE	%	63.55	Store 2
37	COST OF SALE	2022	-1608084.96	Store 2
38	COST OF SALE	%	64	Store 2
39	COST OF SALE	2021	-1849631.48	Store 2
A

Cell Formulas
Range	Formula
I6	I6	=2185730.63/J5*100
D7,D18,F18,H18,F7,H7,J7:T7	D7	=SUM(D5:D6)
G7,I7	G7	=H7/H5*100
C8:C9,C16,C12:C13	C8	=D8/2530323.81*100
E7:E9,E18,E16,E12:E13	E7	=F7/2889934.17*100
I10	I10	=288652/J5*100
I11	I11	=644862.02/J5*100
D12,F12,H12,J12:T12	D12	=SUM(D7:D11)
G12,I12	G12	=H12/H5*100
I14	I14	=24924.05/J5*100
D16,F16,H16	D16	=SUM(D12:D15)
G16	G16	=H16/H5*100
C18,G18,I18	C18	=D18/D5*100
J18:T18	J18	=SUM(J12:J14)
A20	A20	=FORMULATEXT(A21)
A21:D205	A21	=IFNA(HSTACK(UNPIVOT(A5:A18,B4:T4,B5:T18),"Store 2"),"Store 2")
Dynamic array formulas.

S3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Store-3
2	INCOME STATEMENT AS ON DECEMBER 31ST 2022
3
4		,	%	2022	%	2021	%	2020	%	2019	2018	2017	2016	2015	2014	2013	2012	2011	2010	2009		2008
5	SALES	1		2,879,841.75		3,266,367.54		4,765,376.95		3,434,119.91	2,766,042.26	2,388,318.73	2,704,385.36	3,110,292.06	3,415,709.05	3,903,096.91	4,683,396.59	3,487,151.31	2,142,854.95	2,098,597.94		1,532,053.92
6	LESS: COST OF SALES		62.70	(1,805,676.54)	63.33	(2,068,512.27)	64.7	(3,082,180.22)	64.11	(2,201,445.50)	(1,813,221.68)	(1,597,314.45)	(1,853,685.91)	(2,173,744.79)	(2,395,028.90)	(2,726,701.80)	(3,422,870.05)	(2,411,005.37)	(1,493,168.00)	(1,476,761.87)		(1,062,861.18)
7	GROSS PROFIT	2	37.30	1,074,165.21	36.67	1,197,855.27	35.3	1,683,196.73	35.89	1,232,674.41	952,820.58	791,004.28	850,699.45	936,547.27	1,020,680.15	1,176,395.11	1,260,526.54	1,076,145.94	649,686.95	621,836.07		469,192.74
8	OTHER INCOME		-		-						3,134.33	13,931.01	38,749.77	35,594.98	40,958.13	58,336.26	49,200.66	65,338.63	37,523.97	69,736.89		34,857.72
9	SALARY PAID TO EMPLOYEES		11.34	(326,629.21)	8.75	(285,855.95)	7.36	(350,567.00)	8.03	(275,831.00)	(261,982.00)	(233,338.00)	(228,129.00)	(303,862.00)	(318,207.00)	(292,614.25)	(272,252.51)	(254,345.35)	(227,526.00)	(206,786.92)		(173,943.00)
10	GENERAL & ADM EXPENSES		17.61	(507,085.46)	19.25	(628,634.22)	13.7	(652,608.02)	16.99	(583,571.25)	(505,964.46)	(437,458.85)	(424,009.40)	(470,789.55)	(431,626.65)	(505,707.56)	(485,039.23)	(410,739.43)	(370,229.79)	(393,531.00)		(261,474.45)
11	EBITDA	3	8.35	240,450.54	8.68	283,365.10	14.3	680,021.71	10.87	373,272.16	188,008.45	134,138.44	237,310.82	197,490.70	311,804.63	436,409.56	552,435.46	476,399.79	89,455.13	91,255.04		68,633.01
12	LESS: SUNDRY EXPENSES		-		-						(11,855.03)	(10,098.66)	(9,372.54)	(10,116.10)	(12,236.07)	(13,316.70)	(17,404.45)	(13,488.64)	(10,422.86)	(10,036.99)
13	LESS: MANAGEMENT FEES		0.71	(20,403.85)	0.72	(23,419.24)	1.27	(60,290.44)	1.03	(35,469.30)	(16,738.56)	(11,786.59)	(21,659.29)	(17,804.82)	(23,468.50)	(33,145.52)	(41,914.87)	(36,264.93)	(6,191.46)	(6,091.35)		(5,147.47)
14	LESS: DEPRECIATION + AMORT		0.56	(16,202.47)	0.52	(17,108.68)	0.37	(17,401.05)
15	PROFIT BEFORE TAX	4	7.08	203,844.22	7.43	242,837.18	12.63971825	602,330.22
16	LESS TAX PAYABLE		4.90	(9,521.80)		(19,796.71)	4.9	(28,135.54)
17	NET PROFIT	5	6.75	194,322.42	6.83	223,040.47	12.04930242	574,194.68	9.84	337,802.86	159,414.86	112,253.19	206,278.99	169,569.78	276,100.06	389,947.34	493,116.14	426,646.22	72,840.81	75,126.70		63,485.54
18
19	=IFNA(HSTACK(UNPIVOT(A5:A17,B4:V4,B5:V17),"Store 3"),"Store 3")
20	SALES	,	1	Store 3
21	SALES	2022	2879841.75	Store 3
22	SALES	2021	3266367.54	Store 3
23	SALES	2020	4765376.95	Store 3
24	SALES	2019	3434119.91	Store 3
25	SALES	2018	2766042.26	Store 3
26	SALES	2017	2388318.73	Store 3
27	SALES	2016	2704385.36	Store 3
28	SALES	2015	3110292.06	Store 3
29	SALES	2014	3415709.05	Store 3
30	SALES	2013	3903096.91	Store 3
31	SALES	2012	4683396.59	Store 3
32	SALES	2011	3487151.31	Store 3
33	SALES	2010	2142854.95	Store 3
34	SALES	2009	2098597.94	Store 3
35	SALES	2008	1532053.92	Store 3
36	LESS: COST OF SALES	%	62.7	Store 3
37	LESS: COST OF SALES	2022	-1805676.54	Store 3
38	LESS: COST OF SALES	%	63.33	Store 3
39	LESS: COST OF SALES	2021	-2068512.27	Store 3
A

Cell Formulas
Range	Formula
I6	I6	=2201445.5/J5*100
D7,D17,F17,H17,V7,H7,J7:T7,F7	D7	=SUM(D5:D6)
I7	I7	=J7/J5*100
C7:C8,C15,C11:C12	C7	=D7/2879841.75*100
E7:E8,E17,E15,E11:E12	E7	=F7/3266367.54*100
I9	I9	=275831/J5*100
I10	I10	=583571.25/J5*100
D11,D15,F15,H15,V11,F11,H11,J11:T11	D11	=SUM(D7:D10)
G11,I11	G11	=H11/H5*100
I13	I13	=35469.3/J5*100
G15	G15	=H15/H5*100
C17,G17,I17	C17	=D17/D5*100
J17:T17,V17	J17	=SUM(J11:J13)
A19	A19	=FORMULATEXT(A20)
A20:D214	A20	=IFNA(HSTACK(UNPIVOT(A5:A17,B4:V4,B5:V17),"Store 3"),"Store 3")
Dynamic array formulas.