APIVOT

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

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
APIVOT array pivot, reproduces basic functionality of Pivot Table, with a single cell lambda, for any array, regular, dynamic, table. calls AAGGREGATE , APPEND2V , APPEND2H
Basic functionality means: as in PT interface we can select rows values, column values, values, value fields, only sum, count, min and max
- disadvantages: was not designed to compete with mighty PT, does not have grouping functionality as PT does, though, could be added but the formulas can get discouraging huge.
- advantages: no table format needed, no refresh needed, so if we filter a large table for some complex criteria in a simple dynamic array, this lambda can be handy.
Other functions on minisheet ASELECT
Note: Sample data, courtesy of Mike Girvin ExcelIsFun
Excel Formula: 
=LAMBDA(a,rw,cl,vl,vf,
    LET(g,"Grand Total",fn,IF(vf=0,0,vf),q,SEQUENCE(,COLUMNS(a)),c,INDEX(a,,cl),r,INDEX(a,,rw),v,INDEX(a,,vl),uc,SORT(UNIQUE(c)),ur,SORT(UNIQUE(r)),tc,TRANSPOSE(uc),f,IF(AND(ISNUMBER(v)),fn,1),
       ar,SWITCH(f,0,SUMIFS(v,r,ur,c,tc),2,MINIFS(v,r,ur,c,tc),3,MAXIFS(v,r,ur,c,tc),1,COUNTIFS(r,ur,c,tc)),fg,SWITCH(f,2,0,3,1,4,7,5),
       rc,AAGGREGATE(ar,fg,),br,TRANSPOSE(AAGGREGATE(TRANSPOSE(ar),fg,)),
       w,ROWS(ar)+1,u,COLUMNS(ar)+2,sr,SEQUENCE(w),sc,SEQUENCE(,u)-1,lc,IF(sr=w,g,INDEX(ur,sr)),tr,SWITCH(sc,0,"("&rw&"\"&cl&")"&" "&vl&" vf="&fn,u-1,g,INDEX(tc,sc)),
       aa,APPEND2V(APPEND2H(ar,rc,),br,),ab,IF(aa="",SWITCH(f,2,MIN(br,rc),3,MAX(br,rc),SUM(rc)),aa),ac,APPEND2V(tr,APPEND2H(lc,ab,),),
       IFS(ISNA(XMATCH(fn,{0,1,2,3})),"check function nr.",NOT(AND(ISNUMBER(XMATCH(CHOOSE({1,2,3},rw,cl,vl),q)))),"check selection",TRUE,ac)
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1sample data, courtesy of Mike GirvinExcelIsFun=APIVOT(Rev,2,4,6,)vf=0sumSum of Net RevenueColumn Labels
2DateSales RepUnitsProductRegionNet Revenue(2\4) 6 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total
301-01-20Tynia Malone500QuadMidWest12560.88Chantel Mims7659.578379.3328169.7129746.5913486.9687442.16Chantel Mims7659.578379.3328169.7129746.61348787442.16
401-01-20Kiki Sho38YanakiMidWest902.79Hien Pham7785.007809.9422079.839952.8819538.6867166.33Hien Pham77857809.9422079.839952.8819538.767166.33
501-01-20Hien Pham500YanakiMidWest8035.63Janis Figueroa161.701104.4218189.425683.153039.7528178.44Janis Figueroa161.71104.4218189.425683.153039.7528178.44
601-01-20Chantel Mims500YanakiWest8035.63Kiki Sho9137.046161.932809.605981.8010598.5734688.94Kiki Sho9137.046161.932809.65981.810598.634688.94
701-01-20Tynia Malone39QuadEast1481.47Tynia Malone948.47339.5038904.912434.6314517.6357145.14Tynia Malone948.47339.538904.912434.6314517.657145.14
801-01-20Kiki Sho48SunsetMidWest934.2Grand Total25691.7823795.12110153.553799.0561181.59274621.01Grand Total25691.7823795.12110153.4753799.161181.6274621.01
901-01-20Hien Pham13QuadCanada580.97
1002-01-20Tynia Malone27YanakiNorthWest641.45=APIVOT(Rev,2,4,6,1)vf=1count
1102-01-20Hien Pham13SunsetMidWest337.35(2\4) 6 vf=1Crested BeautMajestic BeautQuadSunsetYanakiGrand Total
1202-01-20Chantel Mims36SunsetMidWest732.87Chantel Mims7567631
1302-01-20Tynia Malone44QuadSouth1596.61Hien Pham2456724
1403-01-20Hien Pham100Crested BeautMidWest1816.5Janis Figueroa1242110
1503-01-20Chantel Mims7YanakiMexico188.65Kiki Sho5234519
1603-01-20Hien Pham5YanakiMexico139.75Tynia Malone1177319
1703-01-20Chantel Mims37QuadMexico1374.05Grand Total1614252622103
1803-01-20Janis Figueroa525QuadMexico13188.92
1903-01-20Chantel Mims450SunsetNorthWest6714.56=APIVOT(Rev,2,4,6,2)vf=2minMin of Net RevenueColumn Labels
2004-01-20Hien Pham45SunsetCanada916.09(2\4) 6 vf=2Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total
2104-01-20Tynia Malone3SunsetWest74.85Chantel Mims215.6209.7312.83639.6726.9526.95Chantel Mims215.6209.7312.83639.6726.9526.95
2204-01-20Chantel Mims48Crested BeautWest934.2Hien Pham1816.533.95218.4524.9555.924.95Hien Pham1816.533.95218.4524.9555.924.95
2304-01-20Chantel Mims100Crested BeautMidWest1746.5Janis Figueroa161.734.9585.38174.653039.7534.95Janis Figueroa161.734.9585.38174.653039.7534.95
2404-01-20Hien Pham2YanakiSouth55.9Kiki Sho249.5305.55305.83311.457.957.9Kiki Sho249.5305.55305.83311.457.957.9
2504-01-20Kiki Sho25QuadMexico907.16Tynia Malone948.47339.5298.8374.85641.4574.85Tynia Malone948.47339.5298.8374.85641.4574.85
2604-01-20Chantel Mims8Crested BeautSouth215.6Grand Total161.733.9585.3824.9526.9524.95Grand Total161.733.9585.3824.9526.9524.95
2704-01-20Kiki Sho34Crested BeautMidWest778.86
2804-01-20Chantel Mims275YanakiWest4419.59=APIVOT(Rev,2,4,6,3)vf=3max
2905-01-20Chantel Mims200QuadEast5976.6(2\4) 6 vf=3Crested BeautMajestic BeautQuadSunsetYanakiGrand Total
3005-01-20Hien Pham600YanakiEast9297.75Chantel Mims2724.756291.2514114.387531.788035.6314114.38
3105-01-20Janis Figueroa36Majestic BeautMidWest1069.47Hien Pham5968.56291.2513816.967574.199297.7513816.96
3205-01-20Chantel Mims28QuadMidWest1039.82Janis Figueroa161.71069.4713188.925508.53039.7513188.92
3305-01-20Kiki Sho525YanakiMidWest8437.41Kiki Sho5021.195856.381596.614191.258437.418437.41
3405-01-20Chantel Mims6Majestic BeautCanada209.7Tynia Malone948.47339.512560.88934.27633.8412560.88
3505-01-20Kiki Sho9Majestic BeautWest305.55Grand Total5968.56291.2514114.387574.199297.7514114.38
3605-01-20Hien Pham550QuadMidWest13816.96
3706-01-20Kiki Sho250SunsetEast4191.25=APIVOT(Rev,2,4,3,)Sum of Units2Column Labels
3806-01-20Tynia Malone7QuadCanada298.83(2\4) 3 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total
3906-01-20Chantel Mims150Crested BeautCanada2724.75Chantel Mims401321102220058174566Chantel Mims401321102220058174566
4006-01-20Tynia Malone43Crested BeautMidWest948.47Hien Pham50029885666011893503Hien Pham50029885666011893503
4107-01-20Kiki Sho21SunsetNorthWest544.95Janis Figueroa6376774071501277Janis Figueroa6376774071501277
4207-01-20Hien Pham13QuadWest554.97Kiki Sho560309763316141890Kiki Sho560309763316141890
4307-01-20Tynia Malone24SunsetWest529.38Tynia Malone431014711118772512Tynia Malone431014711118772512
4407-01-20Tynia Malone15SunsetMexico389.25Grand Total151097541023514364713748Grand Total151097541023514364713748
4508-01-20Kiki Sho2YanakiMidWest57.9
4608-01-20Tynia Malone4SunsetNorthWest99.8=APIVOT(Rev,4,5,3,)Sum of UnitsColumn Labels
4708-01-20Kiki Sho42YanakiEast997.82(4\5) 3 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total
4808-01-20Chantel Mims500SunsetEast7460.63Crested Beaut1501694112830408891510Crested Beaut150169411283408891510
4908-01-20Hien Pham38Majestic BeautEast1161.19Majestic Beaut15381033657259260975Majestic Beaut15381033657259260975
5008-01-20Tynia Malone48SunsetMexico934.2Quad170241638107827512554454102Quad170241638107827512554454102
5108-01-20Chantel Mims29SunsetSouth639.67Sunset55802664497482299853514Sunset55802664497482299853514
5209-01-20Hien Pham45YanakiCanada1069.09Yanaki458242110655393777763647Yanaki458242110655393777763647
5309-01-20Tynia Malone10SunsetCanada239.5Grand Total43520741744325913532328255513748Grand Total43520741744325913532328255513748
5409-01-20Kiki Sho7QuadWest305.83
5509-01-20Janis Figueroa6Crested BeautMidWest161.7=APIVOT(Rev,4,5,6,)Sum of Net RevenueColumn Labels
5609-01-20Chantel Mims1YanakiWest26.95(4\5) 6 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total
5709-01-20Tynia Malone475YanakiNorthWest7633.84Crested Beaut2724.753153.696338.655452.0306184.11838.5625691.78Crested Beaut2724.753153.696338.655452.036184.11838.5625691.78
5809-01-20Chantel Mims33Crested BeautMexico699.85Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.12Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.12
5909-01-20Janis Figueroa75QuadCanada2457.56Quad5794.927543.4517379.5727417.666908.4833327.4711781.9110153.47Quad5794.927543.4517379.627417.666908.4833327.511781.9110153.47
6009-01-20Chantel Mims50Majestic BeautNorthWest1348.13Sunset1155.5912777.589997.947512.927533.96639.6714181.453799.05Sunset1155.5912777.69997.947512.927533.96639.6714181.453799.05
6110-01-20Janis Figueroa7SunsetNorthWest174.65Yanaki1069.0914095.56579.9517433.739222.856298.2412482.261181.59Yanaki1069.0914095.6579.9517433.739222.856298.2412482.261181.59
6210-01-20Chantel Mims575QuadSouth14114.38Grand Total11268.638731.4734635.6164742.1925264.0753064.2846914.8274621.01Grand Total11268.638731.534635.664742.1925264.0753064.346914.8274621.01
6310-01-20Janis Figueroa150YanakiEast3039.75
6411-01-20Kiki Sho300Majestic BeautMidWest5856.38
6511-01-20Janis Figueroa400SunsetMidWest5508.5dynamic APIVOT interface usingASELECT(proves that APIVOT works with dynamic arrays,no need of tables, no refresh)
6611-01-20Chantel Mims32YanakiEast760.24date 1date 2row startrow end
6711-01-20Chantel Mims28Crested BeautMexico617.6103-01-2005-01-201227
6811-01-20Kiki Sho41Crested BeautWest904.36=APIVOT(H70#,1,2,3,)
6911-01-20Chantel Mims425SunsetWest5852.78=ASELECT(Rev,J67,K67,,,{2,4,6})(1\2) 3 vf=0Crested BeautQuadSunsetYanakiGrand Total
7012-01-20Chantel Mims6Majestic BeautNorthWest215.7Hien PhamCrested Beaut1816.5Chantel Mims2896.37350.656714.564608.2421569.75
7112-01-20Hien Pham1Majestic BeautWest33.95Chantel MimsYanaki188.65Hien Pham1816.50916.09195.652928.24
7212-01-20Chantel Mims40SunsetEast814.3Hien PhamYanaki139.75Janis Figueroa013188.920013188.92
7312-01-20Hien Pham28YanakiNorthWest689.01Chantel MimsQuad1374.05Kiki Sho778.86907.16001686.02
7412-01-20Chantel Mims34Crested BeautEast721.06Janis FigueroaQuad13188.92Tynia Malone0074.85074.85
7512-01-20Hien Pham550SunsetMexico7574.19Chantel MimsSunset6714.56Grand Total5491.6621446.737705.54803.8939447.78
7613-01-20Chantel Mims7QuadMexico312.83Hien PhamSunset916.09
7713-01-20Hien Pham9YanakiMexico251.55Tynia MaloneSunset74.85
7813-01-20Kiki Sho10Crested BeautEast249.5Chantel MimsCrested Beaut934.2top left corner cell format
7913-01-20Chantel Mims250Majestic BeautSouth6291.25Chantel MimsCrested Beaut1746.5(rows\columns) values value field=
8013-01-20Hien Pham9Majestic BeautSouth323.55Hien PhamYanaki55.9kept it in this shape because if we pivot regular arrays, they do not have headers
8113-01-20Tynia Malone375YanakiSouth6242.34Kiki ShoQuad907.16
8214-01-20Tynia Malone425QuadSouth10921.12Chantel MimsCrested Beaut215.6
8314-01-20Hien Pham275QuadNorthWest6908.48Kiki ShoCrested Beaut778.86
8414-01-20Kiki Sho44QuadMexico1596.61Chantel MimsYanaki4419.59
8514-01-20Janis Figueroa1Majestic BeautNorthWest34.95Chantel MimsQuad5976.6
8614-01-20Kiki Sho125Crested BeautEast2183.13
APIVOT post
Cell Formulas
RangeFormula
H1,H69,L68,H55,H46,H37,H28,H19,H10H1=FORMULATEXT(H2)
H2:N8H2=APIVOT(Rev,2,4,6,)
H11:N17H11=APIVOT(Rev,2,4,6,1)
H20:N26H20=APIVOT(Rev,2,4,6,2)
H29:N35H29=APIVOT(Rev,2,4,6,3)
H38:N44H38=APIVOT(Rev,2,4,3,)
H47:P53H47=APIVOT(Rev,4,5,3,)
H56:P62H56=APIVOT(Rev,4,5,6,)
J67J67=XMATCH(H67,Rev[Date],1)
K67K67=XMATCH(I67,Rev[Date],-1)
L69:Q75L69=APIVOT(H70#,1,2,3,)
H70:J85H70=ASELECT(Rev,J67,K67,,,{2,4,6})
Dynamic array formulas.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXY
1sample table 600 rows
2SalespersonOrder IDOrder dateOrder amountOrdered by Salesperson's surnames
3Jack Potter328404-01-214686.244Max Order amount/Salesperson for each month and year (integers)
4Bob Caldwell328504-01-2117702.13=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),S[Order amount],LAMBDA(x,INT(MAX(x))))
5Bob Caldwell328605-01-218563.469PTCaldwellChowdhryCodeGrosseHintonHofferHollinworthJohnsonLambleOnslowPotterShandSmithTreflWoodcockGT
6Taj Shand328706-01-2120481.3321-(01)-Jan178738091232502154419249154202313218854157802334421212212851802323344
7Jack Potter328809-01-2115780.9921-(02)-Feb21596194441490722969243042303113880162111621620749171062312617332188142496524965
8Justine Hoffer328911-01-217270.79521-(03)-Mar2223196321965920532122581498311289188652351924219120991976621983238422451724517
9Sheridan Smith329011-01-215840.16921-(04)-Apr45892458513984226322035923855590023041239271843512571183381298324585
10Taj Shand329111-01-2115376.8121-(05)-May120621904523970145461539356542251897684745113991834910239195141884823970
11Archer Lamble329212-01-2123132.9821-(06)-Jun792515852523616507187852071081072251411846178101755324594226971715724594
12Hudson Hollinworth329312-01-2119249.7821-(07)-Jul15008239032198623942179842472011175221522233278101574519459241621465624720
13Hudson Onslow329412-01-2118854.7621-(08)-Aug53851210724004236028500235122214010880822124100205661581190171654024100
14Justine Hoffer329512-01-2121544.8121-(09)-Sep22280232272326121103179022345522803202842309088091960122681103042298223455
15Taj Shand329613-01-2123344.8721-(10)-Oct5746137062014719368234891905523697118901023920674234812166113804249392239024939
16Don Johnson329715-01-2115420.9321-(11)-Nov15998242451375584431968024179203752188423014224852428224714218081499624714
17Amy Trefl329816-01-2115300.2621-(12)-Dec2278523455207771809923868238741219522386220082349542792327723198170512051523874
18Lily Code329918-01-218091.06922-(01)-Jan15660218132251722836179822419723914174631960222139238642391623581247822243924782
19Hudson Onslow330018-01-2116146.7122-(02)-Feb16128146442499921777165372239123445172851663512823911984002409724999
20Bob Caldwell330118-01-215858.817GT22785245852400424999243042472023914234452351924219239272428224714249392496524999
21Kayla Woodcock330219-01-2118023.42
22Sheridan Smith330320-01-2121212.96Ordered by surnames
23Hudson Hollinworth330421-01-216270.728Top 2 Orders amount/Sales person all time (integers)
24Archer Lamble330521-01-2111684.54=APIVOT(INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),,S[Order amount],LAMBDA(x,INT(LARGE(x,SEQUENCE(2)))))
25Sophia Hinton330621-01-2123250.87PTGT
26Hudson Onslow330721-01-215584.412Caldwell22785, 22280
27Hudson Hollinworth330821-01-2118973.51Chowdhry24585, 24245
28Kayla Woodcock330923-01-2115544.88Code24004, 23970
29Sophia Hinton331025-01-219941.139Grosse24999, 23942
30Lily Code331125-01-215418.415Hinton24304, 23868
31Hudson Onslow331226-01-2116683.98Hoffer24720, 24197
32Amy Trefl331328-01-2121285.61Hollinworth23914, 23855
33Bob Caldwell331428-01-2117873.08Johnson23445, 22803
34Amy Trefl331529-01-2118233.58Lamble23519, 23132
35Amy Trefl331629-01-2120485.99Onslow24219, 24100
36Archer Lamble331729-01-2110979.77Potter23927, 23864
37Hudson Hollinworth331829-01-2116994.46Shand24282, 24151
38Justine Hoffer331930-01-219521.601Smith24714, 24594
39Bob Caldwell332030-01-219560.26Trefl24939, 24782
40Sheridan Smith332130-01-2114020.61Woodcock24965, 24517
41Bob Caldwell332230-01-2113538.7GT24999, 24965
42Bob Caldwell332301-02-2121596.07
43Sheridan Smith332401-02-218228.796
PIV 11
Cell Formulas
RangeFormula
H4,F24H4=FORMULATEXT(H5)
H5:X20H5=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),S[Order amount],LAMBDA(x,INT(MAX(x))))
F25:G41F25=APIVOT(INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),,S[Order amount],LAMBDA(x,INT(LARGE(x,SEQUENCE(2)))))
Dynamic array formulas.
 
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 :
Excel Formula: 
=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
ABCDEFGHIJKLMNO
1
2ProductSalesSol 1. Expandable range
3Carlota4
4CARLOTA9step 1
5Carlota7=MAP(SEQUENCE(ROWS(P)),P[Product],LAMBDA(x,y,SUM(--EXACT(INDEX(P[Product],SEQUENCE(x)),y))))
6Sunset7↓↓↓
7SUNSET61step 2
8Carlota41=FILTER(P[Product],E7#=1)
9CARLOTA42↓↓↓
10Aspen41↓↓↓step 3
11Aspen61↓↓↓=BYROW(G12#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))
12ASPEN13Carlota3
13CARLOTA112CARLOTA3
141Sunset1
152SUNSET1
161Aspen2
173ASPEN1
18
19step 4, single cell
20
21ProductCount
22Carlota3
23CARLOTA3
24Sunset1
25SUNSET1
26Aspen2
27ASPEN1
28
29
1792
Cell Formulas
RangeFormula
E5E5=FORMULATEXT(E7)
E7:E17E7=MAP(SEQUENCE(ROWS(P)),P[Product],LAMBDA(x,y,SUM(--EXACT(INDEX(P[Product],SEQUENCE(x)),y))))
G8G8=FORMULATEXT(G12)
I11I11=FORMULATEXT(I12)
G12:G17G12=FILTER(P[Product],E7#=1)
I12:I17I12=BYROW(G12#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))
G21:H27G21=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.
 
2nd solution, non-expandable range, embedded lambda to replace 2 BYROWs.
Formula, single cell B29:
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1Sol 2. non expandable range
2ProductSalesstep 1
3Carlota4=--EXACT(P[Product],TOROW(P[Product]))
4CARLOTA910100100000
5Carlota701000010001
6Sunset710100100000
7SUNSET600010000000
8Carlota400001000000
9CARLOTA410100100000
10Aspen401000010001
11Aspen600000001100
12ASPEN100000001100
13CARLOTA1100000000010
1401000010001step 3
15=BYROW(E18#,LAMBDA(x,XLOOKUP(1,x,TOROW(P[Product]))))
16step 2↓↓↓step 4
17=UNIQUE(E4#)↓↓↓=BYROW(Q18#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))
1810100100000Carlota3
1901000010001CARLOTA3
2000010000000Sunset1
2100001000000SUNSET1
2200000001100Aspen2
2300000000010ASPEN1
24
25instead of 2 BYROWS we can use only 1, b([ i ])
26 =b()
27single 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)
29ProductCount↓↓↓=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))(1)
30Carlota3Carlota3
31CARLOTA3CARLOTA3
32Sunset1Sunset1
33SUNSET1SUNSET1
34Aspen2Aspen2
35ASPEN1ASPEN1
36
1792
Cell Formulas
RangeFormula
E3,S29,S17,E17E3=FORMULATEXT(E4)
E4:O14E4=--EXACT(P[Product],TOROW(P[Product]))
Q15,Q27Q15=FORMULATEXT(Q18)
E18:O23E18=UNIQUE(E4#)
Q18:Q23Q18=BYROW(E18#,LAMBDA(x,XLOOKUP(1,x,TOROW(P[Product]))))
S18:S23S18=BYROW(Q18#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))
B29:C35B29=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:Q35Q30=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))()
S30:S35S30=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.
 
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
ABCDEFGHIJKLMNOPQ
1appending a "_" at the end of the UPPER ones
2ProductSales=MAP(P[Product],LAMBDA(x,IF(EXACT(XLOOKUP(x,E4#,E4#),x),x,x&"_")))
3Carlota4=UNIQUE(P[Product])
4CARLOTA9CarlotaCarlota=APIVOT(G4#,,P[Sales],LAMBDA(x,COUNT(x)),"Count")
5Carlota7SunsetCARLOTA_CountGT
6Sunset7AspenCarlotaAspen2=APIVOT(G4#,,P[Sales],LAMBDA(x,SUM(x)),"Sum")
7SUNSET6SunsetASPEN_1SumGT
8Carlota4SUNSET_Carlota3Aspen10
9CARLOTA4CarlotaCARLOTA_3ASPEN_1
10Aspen4CARLOTA_Sunset1Carlota15
11Aspen6AspenSUNSET_1CARLOTA_24
12ASPEN1AspenGT11Sunset7
13CARLOTA11ASPEN_SUNSET_6
14CARLOTA_GT63
15
16real life
17=APIVOT(P[Product],,P[Sales],LAMBDA(x,COUNT(x)),"Count")
18CountGT
19Aspen3=APIVOT(P[Product],,P[Sales],LAMBDA(x,SUM(x)),"Sum")
20Carlota6SumGT
21Sunset2Aspen11
22GT11Carlota39
23Sunset13
24GT63
25
26=APIVOT(P[Product],,P[Sales],LAMBDA(x,AVERAGE(x)),"Avrg")
27AvrgGT
28Aspen3.666667
29Carlota6.5
30Sunset6.5
31GT5.727273
32
PIV 12
Cell Formulas
RangeFormula
G2G2=FORMULATEXT(G4)
E3,I26,L19,I17,L6,I4E3=FORMULATEXT(E4)
E4:E6E4=UNIQUE(P[Product])
G4:G14G4=MAP(P[Product],LAMBDA(x,IF(EXACT(XLOOKUP(x,E4#,E4#),x),x,x&"_")))
I5:J12I5=APIVOT(G4#,,P[Sales],LAMBDA(x,COUNT(x)),"Count")
L7:M14L7=APIVOT(G4#,,P[Sales],LAMBDA(x,SUM(x)),"Sum")
I18:J22I18=APIVOT(P[Product],,P[Sales],LAMBDA(x,COUNT(x)),"Count")
L20:M24L20=APIVOT(P[Product],,P[Sales],LAMBDA(x,SUM(x)),"Sum")
I27:J31I27=APIVOT(P[Product],,P[Sales],LAMBDA(x,AVERAGE(x)),"Avrg")
Dynamic array formulas.
 
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.
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQRSTUV
1Awesome Chocolates - 2023 Budget Workbook
2
3Month
4PersonJan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23=UNPIVOT(B5:B29,C4:N4,C5:N29,{"Person","Month","Budget"})
5Jehu Rudeforth730315465890795550475405PersonMonthBudget
6Van Tuxwell1,025520550220980325395445Jehu Rudeforth01-01-23730
7Gigi Bohling220625625290370385Jehu Rudeforth01-02-23315
8Jan Morforth255375840410815Jehu Rudeforth01-03-23465
9Oby Sorrel700350635625220265385310360450740525Jehu Rudeforth01-04-23890
10Gunar ****shoot680285485540520410550865405660265820Jehu Rudeforth01-05-23795
11Brien Boise375465260400435535660Jehu Rudeforth01-06-23550
12Rafaelita Blaksland605350940235655255Jehu Rudeforth01-07-23475
13Barr Faughny635205310575485560470230395280420795Jehu Rudeforth01-08-23405
14Mallorie Waber305285460255325730Van Tuxwell01-01-231025
15Karlen McCaffrey250370415480570595Van Tuxwell01-02-23520
16Marney O'Breen920505490520605335Van Tuxwell01-03-23550
17Beverie Moffet585360635360870640Van Tuxwell01-04-23220
18Roddy Speechley465315610355525Van Tuxwell01-05-23980
19Curtice Advani4155303357505304051,100460Van Tuxwell01-06-23325
20Husein Augar280330465350340650435320Van Tuxwell01-07-23395
21Kaine Padly2755254302605103354653002055251,320935Van Tuxwell01-08-23445
22Dennison Crosswaite200400625390420Gigi Bohling01-01-23220
23Wilone O'Kielt265495625575835Gigi Bohling01-02-23625
24Andria Kimpton1,040345625285205325220675Gigi Bohling01-03-23625
25Kelci Walkden2854851,015410375340580390Gigi Bohling01-04-23290
26Camilla Castle910680305415245Gigi Bohling01-05-23370
27Madelene Upcott235575230520610320685725Gigi Bohling01-06-23385
28Dotty Strutley300650445435410Jan Morforth01-01-23255
29Ches Bonnell2904301,095305580Jan Morforth01-02-23375
30Jan Morforth01-03-23840
31Jan Morforth01-04-23410
32Jan Morforth01-05-23815
33Oby Sorrel01-01-23700
34Oby Sorrel01-02-23350
35Oby Sorrel01-03-23635
36Oby Sorrel01-04-23625
37Oby Sorrel01-05-23220
38Oby Sorrel01-06-23265
39Oby Sorrel01-07-23385
40Oby Sorrel01-08-23310
41Oby Sorrel01-09-23360
42Oby Sorrel01-10-23450
43Oby Sorrel01-11-23740
44Oby Sorrel01-12-23525
45Gunar ****shoot01-01-23680
46Gunar ****shoot01-02-23285
47Gunar ****shoot01-03-23485
48Gunar ****shoot01-04-23540
49Gunar ****shoot01-05-23520
50Gunar ****shoot01-06-23410
51Gunar ****shoot01-07-23550
52Gunar ****shoot01-08-23865
53Gunar ****shoot01-09-23405
54Gunar ****shoot01-10-23660
55Gunar ****shoot01-11-23265
56Gunar ****shoot01-12-23820
57Brien Boise01-01-23375
58Brien Boise01-02-23465
59Brien Boise01-03-23260
60Brien Boise01-04-23400
61Brien Boise01-05-23435
62Brien Boise01-06-23535
63Brien Boise01-07-23660
64Rafaelita Blaksland01-01-23605
65Rafaelita Blaksland01-02-23350
66Rafaelita Blaksland01-03-23940
67Rafaelita Blaksland01-04-23235
68Rafaelita Blaksland01-05-23655
69Rafaelita Blaksland01-07-23255
70Barr Faughny01-01-23635
71Barr Faughny01-02-23205
72Barr Faughny01-03-23310
73Barr Faughny01-04-23575
74Barr Faughny01-05-23485
75Barr Faughny01-06-23560
76Barr Faughny01-07-23470
77Barr Faughny01-08-23230
78Barr Faughny01-09-23395
79Barr Faughny01-10-23280
80Barr Faughny01-11-23420
81Barr Faughny01-12-23795
82Mallorie Waber01-01-23305
83Mallorie Waber01-02-23285
84Mallorie Waber01-03-23460
85Mallorie Waber01-04-23255
Budget
Cell Formulas
RangeFormula
Q4Q4=FORMULATEXT(Q5)
Q5:S187Q5=UNPIVOT(B5:B29,C4:N4,C5:N29,{"Person","Month","Budget"})
Dynamic array formulas.

unpivot-example.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2PersonCountryJan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23=UNPIVOT(B3:C32,D2:O2,D3:O32,{"Person","Country","Month","Budget"})
3Jehu RudeforthIndia90105951451001209085859585120PersonCountryMonthBudget
4New Zealand85145150901801008017016514015085Jehu RudeforthIndia01-01-2390
5Australia11512512085809012012513514595125Jehu RudeforthIndia01-02-23105
6Van TuxwellIndia90951151301351309013510010585115Jehu RudeforthIndia01-03-2395
7New Zealand125901251108011090858512595110Jehu RudeforthIndia01-04-23145
8Australia1109510513512090125959512011090Jehu RudeforthIndia01-05-23100
9Gigi BohlingIndia1101301308590120808511516085140Jehu RudeforthIndia01-06-23120
10New Zealand100959514014513012515517080140105Jehu RudeforthIndia01-07-2390
11Australia145120140130901101551159011511585Jehu RudeforthIndia01-08-2385
12Jan MorforthIndia9010510013080100859511590125115Jehu RudeforthIndia01-09-2385
13New Zealand145809011016013511511012010512590Jehu RudeforthIndia01-10-2395
14Australia195100809011010515011014016080105Jehu RudeforthIndia01-11-2385
15Oby SorrelIndia1101401001151001201109510513011585Jehu RudeforthIndia01-12-23120
16New Zealand110110105130801359513095105100130Jehu RudeforthNew Zealand01-01-2385
17Australia11090145859513515510019080115125Jehu RudeforthNew Zealand01-02-23145
18Gunar ****shootIndia1451058511585951559514585110160Jehu RudeforthNew Zealand01-03-23150
19New Zealand1059085801401058513012010014595Jehu RudeforthNew Zealand01-04-2390
20Australia100160951101558512013090140130130Jehu RudeforthNew Zealand01-05-23180
21Brien BoiseIndia10011585170100130851909080105135Jehu RudeforthNew Zealand01-06-23100
22New Zealand10095100105115851101909010516090Jehu RudeforthNew Zealand01-07-2380
23Australia1258590125135135105909517516085Jehu RudeforthNew Zealand01-08-23170
24Rafaelita BlakslandIndia100951201109515516595115140105110Jehu RudeforthNew Zealand01-09-23165
25New Zealand901401151351251201351258011510095Jehu RudeforthNew Zealand01-10-23140
26Australia1108595851001001258595105135145Jehu RudeforthNew Zealand01-11-23150
27Barr FaughnyIndia1001001351401701309510510512585120Jehu RudeforthNew Zealand01-12-2385
28New Zealand9010015510510511595801309585145Jehu RudeforthAustralia01-01-23115
29Australia15014016511510585901251759080120Jehu RudeforthAustralia01-02-23125
30Mallorie WaberIndia1001301309080100120135100125145110Jehu RudeforthAustralia01-03-23120
31New Zealand1409580165100120951159010011085Jehu RudeforthAustralia01-04-2385
32Australia1401051208015590100110155170110110Jehu RudeforthAustralia01-05-2380
33Jehu RudeforthAustralia01-06-2390
34Jehu RudeforthAustralia01-07-23120
35Jehu RudeforthAustralia01-08-23125
36Jehu RudeforthAustralia01-09-23135
37Jehu RudeforthAustralia01-10-23145
38Jehu RudeforthAustralia01-11-2395
39Jehu RudeforthAustralia01-12-23125
40Van TuxwellIndia01-01-2390
41Van TuxwellIndia01-02-2395
42Van TuxwellIndia01-03-23115
43Van TuxwellIndia01-04-23130
44Van TuxwellIndia01-05-23135
45Van TuxwellIndia01-06-23130
46Van TuxwellIndia01-07-2390
47Van TuxwellIndia01-08-23135
48Van TuxwellIndia01-09-23100
49Van TuxwellIndia01-10-23105
50Van TuxwellIndia01-11-2385
51Van TuxwellIndia01-12-23115
52Van TuxwellNew Zealand01-01-23125
53Van TuxwellNew Zealand01-02-2390
54Van TuxwellNew Zealand01-03-23125
55Van TuxwellNew Zealand01-04-23110
56Van TuxwellNew Zealand01-05-2380
57Van TuxwellNew Zealand01-06-23110
58Van TuxwellNew Zealand01-07-2390
59Van TuxwellNew Zealand01-08-2385
60Van TuxwellNew Zealand01-09-2385
61Van TuxwellNew Zealand01-10-23125
62Van TuxwellNew Zealand01-11-2395
63Van TuxwellNew Zealand01-12-23110
64Van TuxwellAustralia01-01-23110
65Van TuxwellAustralia01-02-2395
66Van TuxwellAustralia01-03-23105
67Van TuxwellAustralia01-04-23135
68Van TuxwellAustralia01-05-23120
69Van TuxwellAustralia01-06-2390
70Van TuxwellAustralia01-07-23125
71Van TuxwellAustralia01-08-2395
72Van TuxwellAustralia01-09-2395
73Van TuxwellAustralia01-10-23120
74Van TuxwellAustralia01-11-23110
75Van TuxwellAustralia01-12-2390
76Gigi BohlingIndia01-01-23110
Unpivot this
Cell Formulas
RangeFormula
Q2Q2=FORMULATEXT(Q3)
Q3:T363Q3=UNPIVOT(B3:C32,D2:O2,D3:O32,{"Person","Country","Month","Budget"})
Dynamic array formulas.
 
Versatility of UNPIVOT, examples:
UNPIVOT.xlsx
ABCDEFGHIJKLMNOP
1 - rows/columns array as vectors
2When values array has blanks, data is filtered
3=UNPIVOT(B5:B12,C4:I4,C5:I12,{"Prod","Day","Val."})
4Product/DayDay 1Day 2Day 3Day 4Day 5Day 6Day 7ProdDayVal.
5Quad42094499310165271064596QuadDay 1420
6Sunset479118186010376171230QuadDay 2944
7Aspen6198888148513201157QuadDay 3993
8Yanaki111598227479411098261281QuadDay 41016
9Bellen834875776775377423QuadDay 5527
10Carlota8481037555624723329QuadDay 61064
11Vrang1102442831314662678QuadDay 7596
12Sunshine75912413476499491162794SunsetDay 1479
13SunsetDay 21181
14SunsetDay 3860
15SunsetDay 41037
16SunsetDay 5617
17SunsetDay 71230
18AspenDay 1619
19AspenDay 3888
20AspenDay 4814
21AspenDay 5851
22AspenDay 6320
23AspenDay 71157
24YanakiDay 11115
25YanakiDay 2982
26YanakiDay 3274
27YanakiDay 4794
28YanakiDay 51109
29YanakiDay 6826
30YanakiDay 71281
31BellenDay 1834
32BellenDay 2875
33BellenDay 3776
34BellenDay 5775
35BellenDay 6377
36BellenDay 7423
37CarlotaDay 1848
38CarlotaDay 31037
39CarlotaDay 4555
40CarlotaDay 5624
41CarlotaDay 6723
42CarlotaDay 7329
43VrangDay 11102
44VrangDay 2442
45VrangDay 3831
46VrangDay 4314
47VrangDay 5662
48VrangDay 7678
49SunshineDay 1759
50SunshineDay 21241
51SunshineDay 3347
52SunshineDay 4649
53SunshineDay 5949
54SunshineDay 61162
55SunshineDay 7794
56
UNPIVOT 1
Cell Formulas
RangeFormula
K3K3=FORMULATEXT(K4)
K4:M55K4=UNPIVOT(B5:B12,C4:I4,C5:I12,{"Prod","Day","Val."})
Dynamic array formulas.

UNPIVOT.xlsx
ABCDEFGHIJKLMNOPQRST
1 - rows/columns arrays can be 2D , not only vectors
2UNPIVOT Versatility
3 - no problem to fill the blanks where is needed( if rows or columns array have blanks)
4and to exclude the blanks where is needed (values array is filtered of blanks)
5blank
6cr,B8:D31,c,F5:K6,v,F8:K31
7AbCd=UNPIVOT(B10:D33,F7:K8,F10:K33)
8x1x2x3x1x2x3AaxAbx143
9rvAaxAbx378
10Aax4378951537AaxCdx195
11y49358158AaxCdx215
12z162116AaxCdx337
13bx6936525317AayAbx149
14y4353706982AayAbx335
15z63162053AayCdx181
16cx5421958886AayCdx358
17y9624708695AazAbx116
18z2230139648AazAbx221
19dx2738532829AazAbx216
20y63226825AbxAbx169
21z3045615586AbxAbx236
22Bax435750813242AbxAbx352
23y4532787077AbxCdx153
24z67732994AbxCdx317
25bx9766242814AbyAbx143
26y9422742128AbyAbx353
27z9034635868AbyCdx170
28cx6362333091AbyCdx269
29y4868434789AbyCdx382
30z31775223AbzCdx263
31dx577050225086AbzCdx316
32y8964582131AbzCdx120
33z9738923180AbzCdx253
34AcxAbx154
35AcxAbx221
36AcxCdx195
37AcxCdx288
38AcxCdx386
39AcyAbx196
40AcyAbx224
41AcyAbx370
42AcyCdx186
43AcyCdx395
44AczCdx222
45AczCdx330
46AczCdx113
47AczCdx296
48AczCdx348
49AdxAbx127
50AdxAbx238
51AdxCdx153
52AdxCdx228
53AdxCdx329
54AdyAbx163
55AdyAbx322
56AdyCdx168
57AdyCdx225
58AdzAbx130
59AdzAbx245
60AdzAbx361
61AdzAbx255
62AdzAbx386
63BaxAbx143
64BaxAbx257
65BaxAbx350
66BaxCdx181
67BaxCdx232
68BaxCdx342
69BayAbx145
70BayAbx232
71BayAbx378
72BayCdx170
73BayCdx377
74BazCdx267
75BazCdx373
76BazCdx229
77BazCdx394
78BbxAbx197
79BbxAbx366
80BbxCdx124
81BbxCdx228
82BbxCdx314
83BbyAbx194
84BbyAbx222
85BbyAbx374
86BbyCdx121
87BbyCdx228
88BbzAbx190
89BbzAbx234
90BbzCdx163
91BbzCdx258
92BbzCdx368
93BcxAbx163
94BcxAbx262
95BcxAbx333
96BcxAbx230
97BcxAbx391
98BcyAbx248
99BcyAbx368
100BcyCdx143
101BcyCdx247
102BcyCdx389
103BczAbx131
104BczAbx277
105BczAbx252
106BczAbx323
107BdxAbx157
108BdxAbx270
109BdxAbx350
110BdxCdx122
111BdxCdx250
112BdxCdx386
113BdyAbx189
114BdyAbx364
115BdyCdx158
116BdyCdx221
117BdyCdx331
118BdzAbx197
119BdzAbx238
120BdzAbx392
121BdzAbx231
122BdzAbx380
123
UNPIVOT 2
Cell Formulas
RangeFormula
M7M7=FORMULATEXT(M8)
M8:R122M8=UNPIVOT(B10:D33,F7:K8,F10:K33)
Dynamic array formulas.

UNPIVOT.xlsx
ABCDEFGHIJKLMNOPQRS
1
2Data scenario without column headersc,omittedData scenario without row headersr,omitted
3=UNPIVOT(B4:B6,,C4:E6)=UNPIVOT(,J4:M4,J5:M9)
4Team 1Mel ScheidlerAntione GrinnellRey McglothlinTeam 1Mel ScheidlerEastWestSouthNorthEast23
5Team 2Theodore KrigerAntione ScheidlerTeam 1Antione Grinnell23456798West45
6Team 3Freddie OttenHouston YbarboJesus PasquarielloTeam 1Rey Mcglothlin32678918South67
7Team 2Theodore Kriger214356North98
8Team 2Antione Scheidler5321East32
9Team 3Freddie Otten32West67
10Team 3Houston YbarboSouth89
11Team 3Jesus PasquarielloNorth18
12East21
13Data is consistent with teams membershipWest43
14North56
15West53
16North21
17North32
18
UNPIVOT 3
Cell Formulas
RangeFormula
G3,P3G3=FORMULATEXT(G4)
G4:H11G4=UNPIVOT(B4:B6,,C4:E6)
P4:Q17P4=UNPIVOT(,J4:M4,J5:M9)
Dynamic array formulas.
 
Task: Create a consistent random data set with a list of products based on their available types
UNPIVOT.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Task: Create a consistent random data set with a list of products based on their available typeslc, last column argumentConsistent 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))
3if lc=1, last column will be eliminated having no relevance↓↓↓↓
4Types of existing products by brandlc,omittedlc,1↓↓↓↓Single cell formula
5Brand/TypephonefoldabletabletlaptopPCTV=UNPIVOT(B6:B10,C5:H5,C6:H10)=UNPIVOT(B6:B10,C5:H5,C6:H10,,1)↓↓↓↓
6HPxxxHPphonexHPphoneMicrosoftfoldable514Sonylaptop218
7SamsungxxxxxHPlaptopxHPlaptopSonytablet957Applephone250
8ApplexxxxHPPCxHPPCSonylaptop116SamsungTV798
9MicrosoftxxxxSamsungphonexSamsungphoneMicrosofttablet161Microsofttablet105
10SonyxxxxSamsungfoldablexSamsungfoldableMicrosofttablet152HPphone396
11SamsungtabletxSamsungtabletMicrosoftfoldable171HPPC629
12SamsunglaptopxSamsunglaptopSonytablet531Microsoftfoldable399
13SamsungTVxSamsungTVMicrosofttablet848HPphone848
14ApplephonexApplephoneMicrosoftPC138Applelaptop728
15AppletabletxAppletabletSamsunglaptop710Samsunglaptop578
16ApplelaptopxApplelaptopMicrosofttablet872Microsofttablet814
17ApplePCxApplePCSamsungfoldable723Applephone331
18MicrosoftfoldablexMicrosoftfoldableSonytablet787Microsoftfoldable427
19MicrosofttabletxMicrosofttabletApplephone155Applephone223
20MicrosoftlaptopxMicrosoftlaptopSamsungtablet648Samsungtablet132
21MicrosoftPCxMicrosoftPCMicrosoftPC106SamsungTV681
22SonyphonexSonyphoneMicrosoftfoldable479SonyTV404
23SonytabletxSonytabletApplelaptop357SamsungTV529
24SonylaptopxSonylaptopApplephone144Samsungphone491
25SonyTVxSonyTVAppletablet232SamsungTV630
26Applelaptop957Samsungtablet893
27As we see data is consistent with their typeSonylaptop305Sonytablet798
28(ex: Sony has no foldable type)Microsofttablet765Samsungtablet209
29Samsungfoldable570Samsungfoldable245
30HPlaptop498Sonytablet468
31MicrosoftPC645Samsungtablet829
32MicrosoftPC982ApplePC376
33ApplePC261MicrosoftPC855
34Appletablet823Samsungfoldable802
35Microsoftfoldable124Samsunglaptop816
36Single 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
RangeFormula
S2S2=FORMULATEXT(S6)
J5,N5J5=FORMULATEXT(J6)
J6:L25J6=UNPIVOT(B6:B10,C5:H5,C6:H10)
N6:O25N6=UNPIVOT(B6:B10,C5:H5,C6:H10,,1)
S6:U35S6=HSTACK(INDEX(N6#,RANDARRAY(30,,1,ROWS(N6#),1),{1,2}),RANDARRAY(30,,100,999,1))
W6:Y35W6=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)))
M37M37=FORMULATEXT(W6)
Dynamic array formulas.
 
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.
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQR
1Advanced UnPivot Technique
2Monthly Sales (Actual vs Budget)
3!!!! Observe rows 60-63, full blank rows of "values" arrayfiltering 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))))))
5ActualBudget ActualBudget
6Customer NameArticle DescriptionJanuaryFebruaryMarchJanuaryFebruaryMarchCustomer NameArticle Description01-01-2001-02-2001-03-2001-01-2001-02-2001-03-20
7Aida GmbHLaptop bag (black)23,70055,80114,16022,41462,98113,682Aida GmbHLaptop bag (black)23700.455801.2414160.0222414.2462981.1413681.65
8Laptop bag (red)60,72125,44070,101 21,438Laptop bag (red)60721.225440.3570100.5521438.38
9Men dress shirt (black)10,42513,0019,601 13,916Men dress shirt (black)10425.113000.519600.5413916.44
10Men dress shirt (gray)17,50025,6764,30017,51729,3184,038Men dress shirt (gray)17500.4625676.254300.417516.5629317.534038.06
11Men dress shirt (white)4,77516,0765,658 13,272Men dress shirt (white)4775.1916075.635658.0913272.13
12Men shorts (black)12,9973,67213,55511,7023,52513,879Men shorts (black)12996.83672.413555.2811702.053525.213878.63
13Men shorts (gray)14,52711,64710,63914,98613,7438,958Men shorts (gray)14526.6711646.6810638.814986.3713742.538957.77
14Men type T (Brown) simple2,5489,1405,5512,6539,8836,162Men type T (Brown) simple2548.49140.25551.426539883.116162.22
15Men type T simple (black)6,5892,4966,6137,6342,9067,692Men type T simple (black)6588.552496.246612.87634.022906.387691.94
16Men type T simple (white) 4,7884,5484,7284,0813,8954,089Men type T simple (white) 4788.474548.414728.314081.223895.224088.67
17Smartphone case diamond9,87515,1009,62917,559 Smartphone case diamond9875.0715100.279628.8117558.58
18Smartphone case simple35,04214,04127,06141,48016,63424,709Smartphone case simple35041.7514040.827061.2741479.7316633.6524709.24
19Unisex tank top (black)1,3564,1701,5181,4503,4681,761Unisex tank top (black)1356.44170.291518.371449.723467.741760.74
20Unisex tank top (white)4,2975,1612,3285,1275,7581,980Unisex tank top (white)4297.165161.22328.45127.495758.41980.36
21Women crop top (black)6,1513,8101,6406,3503,4391,859Women crop top (black)6150.823810.421640.456349.733438.631858.6
22Women dress (black) long11,59430,50512,36632,710 Women dress (black) long11594.430504.6312366.3632709.71
23Women dress (red) ****tail23,37445,88131,43519,13547,06632,849Women dress (red) ****tail23374.445880.5431434.5519135.2347066.0732848.53
24Women type T (black) simple1,9204,3089,9132,2983,7309,139Women type T (black) simple1920.44308.49913.042297.543730.469138.58
25Women type T (white) simple7,513 7,590Women type T (white) simple7512.857590.27
26Women type T simple (red)4,2775,642 4,7936,070Women type T simple (red)4277.195642.44792.736070.36
27Aida GmbH Total252,182240,235201,115264,181261,407193,083DelliciaLaptop bag (black)22200.236060.523820.3726461.2530806.3420731.9
28DelliciaLaptop bag (black)22,20036,06123,82026,46130,80620,732Laptop bag (red)26100.461801.226553.570101.92
29Laptop bag (red)26,10061,80126,55470,102 Men dress shirt (black)28101.7417800.828084.1820203.71
30Men dress shirt (black)28,10217,801 28,08420,204Men dress shirt (gray)18875.6516325.626950.418520.9217452.335795.45
31Men dress shirt (gray)18,87616,3266,95018,52117,4525,795Men dress shirt (white)4850.410250.412225.85525.8211904.4913223.45
32Men dress shirt (white)4,85010,25012,2265,52611,90413,223Men shorts (black)14382.585508.2225903.6115224.364962.1729418.93
33Men shorts (black)14,3835,50825,90415,2244,96229,419Men shorts (gray)20395.388208.6315210.821130.558633.6717320.94
34Men shorts (gray)20,3958,20915,21121,1318,63417,321Men type T (Brown) simple14340.4813963.3214086.1115434.34
35Men type T (Brown) simple14,34013,96314,08615,434 Men type T simple (black)13008.9613759.43
36Men type T simple (black)13,00913,759 Men type T simple (white) 46563060.43552.193832.013547.083973.08
37Men type T simple (white) 4,6563,0603,5523,8323,5473,973Smartphone case diamond6275.075300.316095.455069.13
38Smartphone case diamond6,2755,300 6,0955,069Smartphone case simple5120.411080.586560.334952.789777.577791.57
39Smartphone case simple5,12011,0816,5604,9539,7787,792Unisex tank top (black)1740.42298.261823.061842.42
40Unisex tank top (black)1,7402,2981,8231,842 Unisex tank top (white)1458.43558.779222.831638.384261.117741.49
41Unisex tank top (white)1,4583,5599,2231,6384,2617,741Women crop top (black)13321.387650.993750.4715711.316263.443885.69
42Women crop top (black)13,3217,6513,75015,7116,2633,886Women dress (black) long37386.523436.470246.7942808.0926002.3171271.98
43Women dress (black) long37,38723,43670,24742,80826,00271,272Women dress (red) ****tail11346.418166.461318.739240.3217291.664479.42
44Women dress (red) ****tail11,34618,16661,3199,24017,29264,479Women type T (black) simple4080.346372.843648.837021.04
45Women type T (black) simple4,0806,373 3,6497,021Women type T (white) simple
46Women type T (white) simple Women type T simple (red)7904.612977.328968.682511.67
47Women type T simple (red)7,9052,9778,969 2,512EdsonLaptop bag (black)15000.4812796.51
48Dellicia Total217,088259,827271,214230,237266,109280,439Laptop bag (red)
49EdsonLaptop bag (black)15,00012,797 Men dress shirt (black)9375.2510450.257508.1111525.58
50Laptop bag (red) Men dress shirt (gray)7300.168296.79
51Men dress shirt (black)9,37510,4507,50811,526 Men dress shirt (white)10300.368967.79
52Men dress shirt (gray)7,3008,297 Men shorts (gray)
53Men dress shirt (white)10,3008,968 Men type T (Brown) simple2652.42312.83
54Men shorts (gray) Men type T simple (black)2472.42676.63
55Men type T (Brown) simple2,652 2,313Men type T simple (white) 3912.43553.42
56Men type T simple (black)2,4722,677 Smartphone case diamond10700.019572.26
57Men type T simple (white) 3,912 3,553Smartphone case simple4760.285485.25
58Smartphone case diamond10,700 9,572 Unisex tank top (black)
59Smartphone case simple4,760 5,485Unisex tank top (white)
60Unisex tank top (black) Women crop top (black)
61Unisex tank top (white) Women dress (black) long16120.414552.11
62Women crop top (black) Women dress (red) ****tail14260.1413881.44
63Women dress (black) long16,120 14,552 Women type T (black) simple4584.85321.15
64Women dress (red) ****tail14,260 13,881Women type T (white) simple2940.413451.49
65Women type T (black) simple4,585 5,321 Women type T simple (red)
66Women type T (white) simple2,940 3,451ErmaLaptop bag (black)30540.815840.0125576.3715602.39
67Women type T simple (red) Laptop bag (red)80401.2748301.2474738.6243789.23
68Edson Total44,44941,85528,52640,24640,97128,684Men dress shirt (black)8775.344700.415850.8710037.185066.9217319.53
69ErmaLaptop bag (black)30,54115,84025,57615,602 Men dress shirt (gray)17400.46325.415650.516524.266427.9218712.31
70Laptop bag (red)80,40148,301 74,73943,789Men dress shirt (white)31802.0445401.8442151.3926849.5938766.6448375.6
71Men dress shirt (black)8,7754,70015,85110,0375,06717,320Men shorts (black)3834.411142.827488.044282.1110111.128168.93
72Men dress shirt (gray)17,4006,32515,65116,5246,42818,712Men shorts (gray)13770.543456.414904.8614199.973360.5313331.25
73Men dress shirt (white)31,80245,40242,15126,85038,76748,376Men type T (Brown) simple8164.842990.47011.052411.01
74Men shorts (black)3,83411,14327,4884,28210,11128,169Men type T simple (black)8292.84620.47456.375437.04
75Men shorts (gray)13,7713,45614,90514,2003,36113,331Men type T simple (white) 8244.89193.022184.359556.8510448.32260.21
76Men type T (Brown) simple8,1652,9907,011 2,411Smartphone case diamond12000.844375.413636.554821.5
77Men type T simple (black)8,2934,6207,456 5,437Smartphone case simple6660.4619240.8511180.625453.2116351.2213256.97
78Men type T simple (white) 8,2459,1932,1849,55710,4482,260Unisex tank top (black)1602.483973.051310.283744.26
79Smartphone case diamond12,0014,37513,6374,822 Unisex tank top (white)7393.396161.55
80Smartphone case simple6,66019,24111,1815,45316,35113,257Women crop top (black)3030.019870.962953.4810114.38
81Unisex tank top (black)1,6023,9731,310 3,744Women dress (black) long5580163923.2748701.6467963.31
82Unisex tank top (white)7,393 6,162 Women dress (red) ****tail43276.7220894.450025.818910.04
83Women crop top (black)3,0309,8712,95310,114 Women type T (black) simple8172.5912301.238310.1812501.27
84Women dress (black) long55,80163,92348,70267,963 Women type T (white) simple5352.555088.35303.375221.93
85Women dress (red) ****tail43,27720,89450,02618,910 Women type T simple (red)7904.684797.45434.88097.024739.525152.93
86Women type T (black) simple8,17312,3018,310 12,501LiebherLaptop bag (black)20040.0323160.1421433.4527351.45
87Women type T (white) simple5,3535,0885,303 5,222Laptop bag (red)49320.769600.2139603.718098.27
88Women type T simple (red)7,9054,7975,4358,0974,7405,153Men dress shirt (black)11250.2312600.4410411.1213812.03
89Erma Total274,627306,957212,120265,285293,584219,682Men dress shirt (gray)16475.820075.542477.0817354.7518041.3346383.16
90LiebherLaptop bag (black)20,04023,16021,433 27,351Men dress shirt (white)12200.6110002.11
91Laptop bag (red)49,3219,600 39,6048,098Men shorts (black)5904.45832.334194.325154.635666.274544.28
92Men dress shirt (black)11,25012,60010,41113,812 Men shorts (gray)13303.177650.44554.414698.776690.524764.94
93Men dress shirt (gray)16,47620,07642,47717,35518,04146,383Men type T (Brown) simple6292.666965.14
94Men dress shirt (white)12,201 10,002 Men type T simple (black)4068.172856.133879.022394.78
95Men shorts (black)5,9045,8324,1945,1555,6664,544Men type T simple (white) 2760.255172.43170.346192.98
96Men shorts (gray)13,3037,6504,55414,6996,6914,765Smartphone case diamond30476.679875.831866.0610207.08
97Men type T (Brown) simple6,293 6,965Smartphone case simple5300.027400.450087024.86
98Men type T simple (black)4,0682,8563,8792,395 Unisex tank top (black)1440.43654.42628.021285.484142.372781.93
99Men type T simple (white) 2,7605,1723,1706,193 Unisex tank top (white)1044.42484.37859.322385.94
100Smartphone case diamond30,4779,87631,86610,207 Women crop top (black)4790.442410.195308.992653.61
101Smartphone case simple5,3007,400 5,0087,025Women dress (black) long19902.415971.46
102Unisex tank top (black)1,4403,6542,6281,2854,1422,782Women dress (red) ****tail38874.6825668.1715562.1939196.5427302.1413116.64
103Unisex tank top (white)1,0442,484859 2,386Women type T (black) simple18001.4814818.04
104Women crop top (black)4,7902,410 5,3092,654Women type T (white) simple9000.724212.49840.7410423.854342.1510523.05
105Women dress (black) long19,90215,971 Women type T simple (red)2522.42387.41
106Women dress (red) ****tail38,87525,66815,56239,19727,30213,117Werner StraussLaptop bag (black)11160.412784.04
107Women type T (black) simple18,001 14,818Laptop bag (red)32700.3921420.0316560.32727823930.8215969.44
108Women type T (white) simple9,0014,2129,84110,4244,34210,523Men dress shirt (black)21550.824109.99
109Women type T simple (red)2,522 2,387 Men dress shirt (gray)14375.5720976.1414251.4624817.62
110Liebher Total174,541171,732148,606175,705161,102151,411Men dress shirt (white)7100.336385.96
111Werner StraussLaptop bag (black)11,160 12,784Men shorts (black)17227.073168.246714.415003.972795.047422.7
112Laptop bag (red)32,70021,42016,56027,27823,93115,969Men shorts (gray)4446.447200.44426.746331.93
113Men dress shirt (black)21,55124,110 Men type T (Brown) simple6955.783705.46209.794171.3
114Men dress shirt (gray)14,37620,97614,25124,818 Men type T simple (black)2964.1713548.942634.3714267.29
115Men dress shirt (white)7,100 6,386Men type T simple (white) 10260.83252.1610459.243579.32
116Men shorts (black)17,2273,1686,71415,0042,7957,423Smartphone case diamond10800.44300.3511403.763864.57
117Men shorts (gray)4,4467,200 4,4276,332Smartphone case simple6120.148440.275362.357818.7
118Men type T (Brown) simple6,9563,7056,2104,171 Unisex tank top (black)1500.42496.041295.592336.39
119Men type T simple (black)2,96413,549 2,63414,267Unisex tank top (white)2364.42022.261994.922297.79
120Men type T simple (white) 10,2613,25210,459 3,579Women crop top (black)8570.344030.84330.399530.763413.383731.74
121Smartphone case diamond10,8004,30011,404 3,865Women dress (black) long67084.8118414.453768.9620638.99
122Smartphone case simple6,1208,4405,3627,819 Women dress (red) ****tail18724.49796.416655.9711105.46
123Unisex tank top (black)1,5002,4961,296 2,336Women type T (black) simple6816.575875.25
124Unisex tank top (white)2,3642,022 1,9952,298Women type T (white) simple2604.262708.21
125Women crop top (black)8,5704,0314,3309,5313,4133,732Women type T simple (red)10907.449505.15
126Women dress (black) long67,08518,414 53,76920,639
127Women dress (red) ****tail18,7249,79616,65611,105
128Women type T (black) simple6,817 5,875
129Women type T (white) simple2,604 2,708
130Women type T simple (red)10,907 9,505
131Werner Strauss Total148,786159,305106,521141,561150,382108,194
132Grand Total1,111,6731,179,911968,1021,117,2151,173,556981,494
133
Before
Cell Formulas
RangeFormula
J4J4=FORMULATEXT(J5)
J5:Q125J5=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:H48C27=SUM(C7:C26)
C68:H68C68=SUM(C49:C67)
C132:H132C132=C131+C110+C89+C68+C48+C27
Dynamic array formulas.
 
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
RSTUVWXY
1
2
3applying 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})
6Aida GmbHLaptop bag (black)Actual01-01-2023700.4
7Aida GmbHLaptop bag (red)Actual01-01-2060721.2
8Aida GmbHMen dress shirt (black)Actual01-01-2010425.1
9Aida GmbHMen dress shirt (gray)Actual01-01-2017500.46
10Aida GmbHMen dress shirt (white)Actual01-01-204775.19
11Aida GmbHMen shorts (black)Actual01-01-2012996.8
12Aida GmbHMen shorts (gray)Actual01-01-2014526.67
13Aida GmbHMen type T (Brown) simpleActual01-01-202548.4
14Aida GmbHMen type T simple (black)Actual01-01-206588.55
15Aida GmbHMen type T simple (white) Actual01-01-204788.47
16Aida GmbHSmartphone case diamondActual01-01-209875.07
17Aida GmbHSmartphone case simpleActual01-01-2035041.75
18Aida GmbHUnisex tank top (black)Actual01-01-201356.4
19Aida GmbHUnisex tank top (white)Actual01-01-204297.16
20Aida GmbHWomen crop top (black)Actual01-01-206150.82
21Aida GmbHWomen dress (black) longActual01-01-2011594.4
22Aida GmbHWomen dress (red) ****tailActual01-01-2023374.4
23Aida GmbHWomen type T (black) simpleActual01-01-201920.4
24DelliciaLaptop bag (black)Actual01-01-2022200.2
25DelliciaLaptop bag (red)Actual01-01-2026100.4
26DelliciaMen dress shirt (gray)Actual01-01-2018875.65
27DelliciaMen dress shirt (white)Actual01-01-204850.4
28DelliciaMen shorts (black)Actual01-01-2014382.58
29DelliciaMen shorts (gray)Actual01-01-2020395.38
30DelliciaMen type T (Brown) simpleActual01-01-2014340.48
31DelliciaMen type T simple (black)Actual01-01-2013008.96
32DelliciaMen type T simple (white) Actual01-01-204656
33DelliciaSmartphone case simpleActual01-01-205120.4
34DelliciaUnisex tank top (black)Actual01-01-201740.4
35DelliciaUnisex tank top (white)Actual01-01-201458.4
36DelliciaWomen crop top (black)Actual01-01-2013321.38
37DelliciaWomen dress (black) longActual01-01-2037386.5
38DelliciaWomen dress (red) ****tailActual01-01-2011346.4
39DelliciaWomen type T simple (red)Actual01-01-207904.61
40EdsonLaptop bag (black)Actual01-01-2015000.48
41EdsonMen dress shirt (black)Actual01-01-209375.25
42EdsonMen dress shirt (gray)Actual01-01-207300.16
43EdsonMen dress shirt (white)Actual01-01-2010300.36
44EdsonMen type T simple (black)Actual01-01-202472.4
45ErmaLaptop bag (black)Actual01-01-2030540.8
46ErmaMen dress shirt (black)Actual01-01-208775.34
47ErmaMen dress shirt (gray)Actual01-01-2017400.4
48ErmaMen dress shirt (white)Actual01-01-2031802.04
49ErmaMen shorts (black)Actual01-01-203834.4
50ErmaMen shorts (gray)Actual01-01-2013770.54
51ErmaMen type T (Brown) simpleActual01-01-208164.84
52ErmaMen type T simple (black)Actual01-01-208292.8
53ErmaMen type T simple (white) Actual01-01-208244.8
54ErmaSmartphone case diamondActual01-01-2012000.84
55ErmaSmartphone case simpleActual01-01-206660.46
56ErmaUnisex tank top (black)Actual01-01-201602.48
57ErmaWomen crop top (black)Actual01-01-203030.01
58ErmaWomen dress (black) longActual01-01-2055801
59ErmaWomen dress (red) ****tailActual01-01-2043276.72
60ErmaWomen type T (black) simpleActual01-01-208172.59
61ErmaWomen type T (white) simpleActual01-01-205352.55
62ErmaWomen type T simple (red)Actual01-01-207904.68
63LiebherLaptop bag (black)Actual01-01-2020040.03
64LiebherMen dress shirt (black)Actual01-01-2011250.23
65LiebherMen dress shirt (gray)Actual01-01-2016475.8
66LiebherMen shorts (black)Actual01-01-205904.4
67LiebherMen shorts (gray)Actual01-01-2013303.17
68LiebherMen type T simple (black)Actual01-01-204068.17
69LiebherMen type T simple (white) Actual01-01-202760.25
70LiebherSmartphone case diamondActual01-01-2030476.67
71LiebherUnisex tank top (black)Actual01-01-201440.4
72LiebherUnisex tank top (white)Actual01-01-201044.4
73LiebherWomen dress (black) longActual01-01-2019902.4
74LiebherWomen dress (red) ****tailActual01-01-2038874.68
75LiebherWomen type T (white) simpleActual01-01-209000.72
76Werner StraussLaptop bag (red)Actual01-01-2032700.39
77Werner StraussMen dress shirt (black)Actual01-01-2021550.8
78Werner StraussMen dress shirt (gray)Actual01-01-2014375.57
79Werner StraussMen shorts (black)Actual01-01-2017227.07
80Werner StraussMen type T (Brown) simpleActual01-01-206955.78
81Werner StraussMen type T simple (white) Actual01-01-2010260.8
82Werner StraussSmartphone case diamondActual01-01-2010800.4
83Werner StraussSmartphone case simpleActual01-01-206120.14
84Werner StraussUnisex tank top (black)Actual01-01-201500.4
85Werner StraussWomen crop top (black)Actual01-01-208570.34
86Werner StraussWomen dress (red) ****tailActual01-01-2018724.4
87Aida GmbHLaptop bag (black)Actual01-02-2055801.24
88Aida GmbHMen dress shirt (gray)Actual01-02-2025676.25
89Aida GmbHMen shorts (black)Actual01-02-203672.4
90Aida GmbHMen shorts (gray)Actual01-02-2011646.68
91Aida GmbHMen type T (Brown) simpleActual01-02-209140.2
92Aida GmbHMen type T simple (black)Actual01-02-202496.24
93Aida GmbHMen type T simple (white) Actual01-02-204548.41
94Aida GmbHSmartphone case diamondActual01-02-2015100.27
95Aida GmbHSmartphone case simpleActual01-02-2014040.8
96Aida GmbHUnisex tank top (black)Actual01-02-204170.29
97Aida GmbHUnisex tank top (white)Actual01-02-205161.2
98Aida GmbHWomen crop top (black)Actual01-02-203810.42
99Aida GmbHWomen dress (black) longActual01-02-2030504.63
100Aida GmbHWomen dress (red) ****tailActual01-02-2045880.54
101Aida GmbHWomen type T (black) simpleActual01-02-204308.4
102Aida GmbHWomen type T simple (red)Actual01-02-204277.19
103DelliciaLaptop bag (black)Actual01-02-2036060.5
104DelliciaLaptop bag (red)Actual01-02-2061801.2
105DelliciaMen dress shirt (black)Actual01-02-2028101.74
106DelliciaMen dress shirt (gray)Actual01-02-2016325.62
107DelliciaMen dress shirt (white)Actual01-02-2010250.4
108DelliciaMen shorts (black)Actual01-02-205508.22
109DelliciaMen shorts (gray)Actual01-02-208208.63
110DelliciaMen type T (Brown) simpleActual01-02-2013963.32
111DelliciaMen type T simple (white) Actual01-02-203060.4
112DelliciaSmartphone case diamondActual01-02-206275.07
113DelliciaSmartphone case simpleActual01-02-2011080.58
114DelliciaUnisex tank top (black)Actual01-02-202298.26
115DelliciaUnisex tank top (white)Actual01-02-203558.77
116DelliciaWomen crop top (black)Actual01-02-207650.99
117DelliciaWomen dress (black) longActual01-02-2023436.4
118DelliciaWomen dress (red) ****tailActual01-02-2018166.4
119DelliciaWomen type T (black) simpleActual01-02-204080.34
120EdsonMen dress shirt (black)Actual01-02-2010450.25
121EdsonSmartphone case diamondActual01-02-2010700.01
122EdsonWomen dress (black) longActual01-02-2016120.4
123EdsonWomen type T (black) simpleActual01-02-204584.8
124ErmaLaptop bag (black)Actual01-02-2015840.01
125ErmaLaptop bag (red)Actual01-02-2080401.27
126ErmaMen dress shirt (black)Actual01-02-204700.4
127ErmaMen dress shirt (gray)Actual01-02-206325.4
128ErmaMen dress shirt (white)Actual01-02-2045401.84
129ErmaMen shorts (black)Actual01-02-2011142.8
130ErmaMen shorts (gray)Actual01-02-203456.4
131ErmaMen type T simple (white) Actual01-02-209193.02
132ErmaSmartphone case diamondActual01-02-204375.4
133ErmaSmartphone case simpleActual01-02-2019240.85
134ErmaUnisex tank top (white)Actual01-02-207393.39
135ErmaWomen crop top (black)Actual01-02-209870.96
136ErmaWomen dress (black) longActual01-02-2063923.27
137ErmaWomen dress (red) ****tailActual01-02-2020894.4
138ErmaWomen type T simple (red)Actual01-02-204797.4
139LiebherLaptop bag (red)Actual01-02-2049320.76
140LiebherMen dress shirt (black)Actual01-02-2012600.44
141LiebherMen dress shirt (gray)Actual01-02-2020075.5
142LiebherMen dress shirt (white)Actual01-02-2012200.61
143LiebherMen shorts (black)Actual01-02-205832.33
144LiebherMen shorts (gray)Actual01-02-207650.4
145LiebherMen type T simple (black)Actual01-02-202856.13
146LiebherMen type T simple (white) Actual01-02-205172.4
147LiebherSmartphone case diamondActual01-02-209875.8
148LiebherSmartphone case simpleActual01-02-205300.02
149LiebherUnisex tank top (black)Actual01-02-203654.4
150LiebherWomen crop top (black)Actual01-02-204790.44
151LiebherWomen dress (red) ****tailActual01-02-2025668.17
152LiebherWomen type T (white) simpleActual01-02-204212.4
153LiebherWomen type T simple (red)Actual01-02-202522.4
154Werner StraussLaptop bag (red)Actual01-02-2021420.03
155Werner StraussMen dress shirt (gray)Actual01-02-2020976.14
Before
Cell Formulas
RangeFormula
S5S5=FORMULATEXT(S6)
S6:W483S6=SORT(UNPIVOT(J7:K125,L5:Q6,L7:Q125),{3,4,1,2})
Dynamic array formulas.


Advanced_UnPivot_XelPlus (1).xlsx
ABCDEFGHIJKL
1checking PQ results vs UNPIVOT results
2=AND(G6#=DataProper)
3proper data set, Leila's PQ resultTRUE
4
5CustomerArticleScenarioDateSales Value=SORT(UNPIVOT(Before!J7:K125,Before!L5:Q6,Before!L7:Q125),{3,4,1,2})
6Aida GmbHLaptop bag (black)Actual01-01-2023700.4Aida GmbHLaptop bag (black)Actual4383123700.4
7Aida GmbHLaptop bag (red)Actual01-01-2060721.2Aida GmbHLaptop bag (red)Actual4383160721.2
8Aida GmbHMen dress shirt (black)Actual01-01-2010425.1Aida GmbHMen dress shirt (black)Actual4383110425.1
9Aida GmbHMen dress shirt (gray)Actual01-01-2017500.46Aida GmbHMen dress shirt (gray)Actual4383117500.46
10Aida GmbHMen dress shirt (white)Actual01-01-204775.19Aida GmbHMen dress shirt (white)Actual438314775.19
11Aida GmbHMen shorts (black)Actual01-01-2012996.8Aida GmbHMen shorts (black)Actual4383112996.8
12Aida GmbHMen shorts (gray)Actual01-01-2014526.67Aida GmbHMen shorts (gray)Actual4383114526.67
13Aida GmbHMen type T (Brown) simpleActual01-01-202548.4Aida GmbHMen type T (Brown) simpleActual438312548.4
14Aida GmbHMen type T simple (black)Actual01-01-206588.55Aida GmbHMen type T simple (black)Actual438316588.55
15Aida GmbHMen type T simple (white) Actual01-01-204788.47Aida GmbHMen type T simple (white) Actual438314788.47
16Aida GmbHSmartphone case diamondActual01-01-209875.07Aida GmbHSmartphone case diamondActual438319875.07
17Aida GmbHSmartphone case simpleActual01-01-2035041.75Aida GmbHSmartphone case simpleActual4383135041.75
18Aida GmbHUnisex tank top (black)Actual01-01-201356.4Aida GmbHUnisex tank top (black)Actual438311356.4
19Aida GmbHUnisex tank top (white)Actual01-01-204297.16Aida GmbHUnisex tank top (white)Actual438314297.16
20Aida GmbHWomen crop top (black)Actual01-01-206150.82Aida GmbHWomen crop top (black)Actual438316150.82
21Aida GmbHWomen dress (black) longActual01-01-2011594.4Aida GmbHWomen dress (black) longActual4383111594.4
22Aida GmbHWomen dress (red) ****tailActual01-01-2023374.4Aida GmbHWomen dress (red) ****tailActual4383123374.4
23Aida GmbHWomen type T (black) simpleActual01-01-201920.4Aida GmbHWomen type T (black) simpleActual438311920.4
24DelliciaLaptop bag (black)Actual01-01-2022200.2DelliciaLaptop bag (black)Actual4383122200.2
25DelliciaLaptop bag (red)Actual01-01-2026100.4DelliciaLaptop bag (red)Actual4383126100.4
26DelliciaMen dress shirt (gray)Actual01-01-2018875.65DelliciaMen dress shirt (gray)Actual4383118875.65
27DelliciaMen dress shirt (white)Actual01-01-204850.4DelliciaMen dress shirt (white)Actual438314850.4
28DelliciaMen shorts (black)Actual01-01-2014382.58DelliciaMen shorts (black)Actual4383114382.58
29DelliciaMen shorts (gray)Actual01-01-2020395.38DelliciaMen shorts (gray)Actual4383120395.38
30DelliciaMen type T (Brown) simpleActual01-01-2014340.48DelliciaMen type T (Brown) simpleActual4383114340.48
31DelliciaMen type T simple (black)Actual01-01-2013008.96DelliciaMen type T simple (black)Actual4383113008.96
32DelliciaMen type T simple (white) Actual01-01-204656DelliciaMen type T simple (white) Actual438314656
33DelliciaSmartphone case simpleActual01-01-205120.4DelliciaSmartphone case simpleActual438315120.4
34DelliciaUnisex tank top (black)Actual01-01-201740.4DelliciaUnisex tank top (black)Actual438311740.4
35DelliciaUnisex tank top (white)Actual01-01-201458.4DelliciaUnisex tank top (white)Actual438311458.4
36DelliciaWomen crop top (black)Actual01-01-2013321.38DelliciaWomen crop top (black)Actual4383113321.38
37DelliciaWomen dress (black) longActual01-01-2037386.5DelliciaWomen dress (black) longActual4383137386.5
38DelliciaWomen dress (red) ****tailActual01-01-2011346.4DelliciaWomen dress (red) ****tailActual4383111346.4
39DelliciaWomen type T simple (red)Actual01-01-207904.61DelliciaWomen type T simple (red)Actual438317904.61
40EdsonLaptop bag (black)Actual01-01-2015000.48EdsonLaptop bag (black)Actual4383115000.48
41EdsonMen dress shirt (black)Actual01-01-209375.25EdsonMen dress shirt (black)Actual438319375.25
42EdsonMen dress shirt (gray)Actual01-01-207300.16EdsonMen dress shirt (gray)Actual438317300.16
43EdsonMen dress shirt (white)Actual01-01-2010300.36EdsonMen dress shirt (white)Actual4383110300.36
44EdsonMen type T simple (black)Actual01-01-202472.4EdsonMen type T simple (black)Actual438312472.4
45ErmaLaptop bag (black)Actual01-01-2030540.8ErmaLaptop bag (black)Actual4383130540.8
46ErmaMen dress shirt (black)Actual01-01-208775.34ErmaMen dress shirt (black)Actual438318775.34
47ErmaMen dress shirt (gray)Actual01-01-2017400.4ErmaMen dress shirt (gray)Actual4383117400.4
48ErmaMen dress shirt (white)Actual01-01-2031802.04ErmaMen dress shirt (white)Actual4383131802.04
49ErmaMen shorts (black)Actual01-01-203834.4ErmaMen shorts (black)Actual438313834.4
50ErmaMen shorts (gray)Actual01-01-2013770.54ErmaMen shorts (gray)Actual4383113770.54
51ErmaMen type T (Brown) simpleActual01-01-208164.84ErmaMen type T (Brown) simpleActual438318164.84
52ErmaMen type T simple (black)Actual01-01-208292.8ErmaMen type T simple (black)Actual438318292.8
53ErmaMen type T simple (white) Actual01-01-208244.8ErmaMen type T simple (white) Actual438318244.8
54ErmaSmartphone case diamondActual01-01-2012000.84ErmaSmartphone case diamondActual4383112000.84
55ErmaSmartphone case simpleActual01-01-206660.46ErmaSmartphone case simpleActual438316660.46
56ErmaUnisex tank top (black)Actual01-01-201602.48ErmaUnisex tank top (black)Actual438311602.48
57ErmaWomen crop top (black)Actual01-01-203030.01ErmaWomen crop top (black)Actual438313030.01
58ErmaWomen dress (black) longActual01-01-2055801ErmaWomen dress (black) longActual4383155801
59ErmaWomen dress (red) ****tailActual01-01-2043276.72ErmaWomen dress (red) ****tailActual4383143276.72
60ErmaWomen type T (black) simpleActual01-01-208172.59ErmaWomen type T (black) simpleActual438318172.59
61ErmaWomen type T (white) simpleActual01-01-205352.55ErmaWomen type T (white) simpleActual438315352.55
62ErmaWomen type T simple (red)Actual01-01-207904.68ErmaWomen type T simple (red)Actual438317904.68
63LiebherLaptop bag (black)Actual01-01-2020040.03LiebherLaptop bag (black)Actual4383120040.03
64LiebherMen dress shirt (black)Actual01-01-2011250.23LiebherMen dress shirt (black)Actual4383111250.23
65LiebherMen dress shirt (gray)Actual01-01-2016475.8LiebherMen dress shirt (gray)Actual4383116475.8
66LiebherMen shorts (black)Actual01-01-205904.4LiebherMen shorts (black)Actual438315904.4
67LiebherMen shorts (gray)Actual01-01-2013303.17LiebherMen shorts (gray)Actual4383113303.17
68LiebherMen type T simple (black)Actual01-01-204068.17LiebherMen type T simple (black)Actual438314068.17
69LiebherMen type T simple (white) Actual01-01-202760.25LiebherMen type T simple (white) Actual438312760.25
70LiebherSmartphone case diamondActual01-01-2030476.67LiebherSmartphone case diamondActual4383130476.67
71LiebherUnisex tank top (black)Actual01-01-201440.4LiebherUnisex tank top (black)Actual438311440.4
72LiebherUnisex tank top (white)Actual01-01-201044.4LiebherUnisex tank top (white)Actual438311044.4
73LiebherWomen dress (black) longActual01-01-2019902.4LiebherWomen dress (black) longActual4383119902.4
74LiebherWomen dress (red) ****tailActual01-01-2038874.68LiebherWomen dress (red) ****tailActual4383138874.68
75LiebherWomen type T (white) simpleActual01-01-209000.72LiebherWomen type T (white) simpleActual438319000.72
76Werner StraussLaptop bag (red)Actual01-01-2032700.39Werner StraussLaptop bag (red)Actual4383132700.39
77Werner StraussMen dress shirt (black)Actual01-01-2021550.8Werner StraussMen dress shirt (black)Actual4383121550.8
78Werner StraussMen dress shirt (gray)Actual01-01-2014375.57Werner StraussMen dress shirt (gray)Actual4383114375.57
79Werner StraussMen shorts (black)Actual01-01-2017227.07Werner StraussMen shorts (black)Actual4383117227.07
80Werner StraussMen type T (Brown) simpleActual01-01-206955.78Werner StraussMen type T (Brown) simpleActual438316955.78
81Werner StraussMen type T simple (white) Actual01-01-2010260.8Werner StraussMen type T simple (white) Actual4383110260.8
82Werner StraussSmartphone case diamondActual01-01-2010800.4Werner StraussSmartphone case diamondActual4383110800.4
83Werner StraussSmartphone case simpleActual01-01-206120.14Werner StraussSmartphone case simpleActual438316120.14
84Werner StraussUnisex tank top (black)Actual01-01-201500.4Werner StraussUnisex tank top (black)Actual438311500.4
85Werner StraussWomen crop top (black)Actual01-01-208570.34Werner StraussWomen crop top (black)Actual438318570.34
86Werner StraussWomen dress (red) ****tailActual01-01-2018724.4Werner StraussWomen dress (red) ****tailActual4383118724.4
87Aida GmbHLaptop bag (black)Actual01-02-2055801.24Aida GmbHLaptop bag (black)Actual4386255801.24
88Aida GmbHMen dress shirt (gray)Actual01-02-2025676.25Aida GmbHMen dress shirt (gray)Actual4386225676.25
89Aida GmbHMen shorts (black)Actual01-02-203672.4Aida GmbHMen shorts (black)Actual438623672.4
90Aida GmbHMen shorts (gray)Actual01-02-2011646.68Aida GmbHMen shorts (gray)Actual4386211646.68
91Aida GmbHMen type T (Brown) simpleActual01-02-209140.2Aida GmbHMen type T (Brown) simpleActual438629140.2
92Aida GmbHMen type T simple (black)Actual01-02-202496.24Aida GmbHMen type T simple (black)Actual438622496.24
93Aida GmbHMen type T simple (white) Actual01-02-204548.41Aida GmbHMen type T simple (white) Actual438624548.41
94Aida GmbHSmartphone case diamondActual01-02-2015100.27Aida GmbHSmartphone case diamondActual4386215100.27
95Aida GmbHSmartphone case simpleActual01-02-2014040.8Aida GmbHSmartphone case simpleActual4386214040.8
96Aida GmbHUnisex tank top (black)Actual01-02-204170.29Aida GmbHUnisex tank top (black)Actual438624170.29
97Aida GmbHUnisex tank top (white)Actual01-02-205161.2Aida GmbHUnisex tank top (white)Actual438625161.2
98Aida GmbHWomen crop top (black)Actual01-02-203810.42Aida GmbHWomen crop top (black)Actual438623810.42
99Aida GmbHWomen dress (black) longActual01-02-2030504.63Aida GmbHWomen dress (black) longActual4386230504.63
100Aida GmbHWomen dress (red) ****tailActual01-02-2045880.54Aida GmbHWomen dress (red) ****tailActual4386245880.54
101Aida GmbHWomen type T (black) simpleActual01-02-204308.4Aida GmbHWomen type T (black) simpleActual438624308.4
102Aida GmbHWomen type T simple (red)Actual01-02-204277.19Aida GmbHWomen type T simple (red)Actual438624277.19
103DelliciaLaptop bag (black)Actual01-02-2036060.5DelliciaLaptop bag (black)Actual4386236060.5
104DelliciaLaptop bag (red)Actual01-02-2061801.2DelliciaLaptop bag (red)Actual4386261801.2
105DelliciaMen dress shirt (black)Actual01-02-2028101.74DelliciaMen dress shirt (black)Actual4386228101.74
106DelliciaMen dress shirt (gray)Actual01-02-2016325.62DelliciaMen dress shirt (gray)Actual4386216325.62
107DelliciaMen dress shirt (white)Actual01-02-2010250.4DelliciaMen dress shirt (white)Actual4386210250.4
ProperData
Cell Formulas
RangeFormula
G2,G5G2=FORMULATEXT(G3)
G3G3=AND(G6#=DataProper)
G6:K483G6=SORT(UNPIVOT(Before!J7:K125,Before!L5:Q6,Before!L7:Q125),{3,4,1,2})
Dynamic array formulas.
 
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Store-1
2INCOME STATEMENT AS ON 31ST DECEMBER 2022
3
42022%2021%2020%20192018201720162015201420132012201120102009200820072006
5SALES 17,665,499.646,877,877.237,915,191.086,390,653.096,180,665.046,522,702.847,407,254.817,881,384.087,900,683.677,623,621.697,474,840.016,046,665.404,797,450.504,238,988.642,569,998.942,107,365.631,780,543.73
6LESS: 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)
7GROSS PROFIT 238.332,938,140.9336.732,526,545.9334.503435772,731,012.8734.852,226,940.022,104,724.382,150,153.392,320,776.322,445,956.852,387,775.722,234,091.952,108,369.381,942,479.661,507,768.481,315,760.32711,723.43619,640.97478,155.11
8OTHER INCOME--5,864.7136,411.96112,478.1484,466.6189,798.14115,561.6470,428.53102,593.3575,959.8470,796.8939,572.6934,851.92202.08
9SALARY PAID TO EMPLOYEES10.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)
10GENERAL & ADM EXPENSES14.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)
11EBITDA314.121,082,474.0612.34848,748.6116.435210941,300,878.3513.73877,677.63824,827.72949,910.781,169,786.771,184,673.371,286,657.501,227,303.031,126,158.771,146,325.95774,330.60734,038.37242,593.82240,089.77140,180.64
12LESS: 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)
13LESS: MANAGEMENT FEES1.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)
14LESS DEPRECIATION + AMOR0.84(64,304.92)0.91(62,620.87)-0.148458071(11,750.74)
15PROFIT BEFORE TAX412.07925,528.1010.41716,169.3914.804855981,171,832.64
16LESS TAX PAYABLE4.90(43,232.49)(49,899.38)4.9(54,737.65)
17NET PROFIT 511.51882,295.619.69666,270.0114.113304141,117,094.9912.43794,278.40732,236.86840,161.231,042,695.191,060,472.481,167,953.771,110,882.151,012,932.401,041,664.42699,734.41665,945.27213,519.44213,296.77133,522.64
18
19=IFNA(HSTACK(UNPIVOT(A5:A17,B4:Z4,B5:Z17),"Store 1"),"Store 1")
20SALES 1Store 1
21SALES 20227665499.64Store 1
22SALES 20216877877.23Store 1
23SALES 20207915191.08Store 1
24SALES 20196390653.09Store 1
25SALES 20186180665.04Store 1
26SALES 20176522702.84Store 1
27SALES 20167407254.81Store 1
28SALES 20157881384.08Store 1
29SALES 20147900683.67Store 1
30SALES 20137623621.69Store 1
31SALES 20127474840.01Store 1
32SALES 20116046665.4Store 1
33SALES 20104797450.5Store 1
34SALES 20094238988.64Store 1
35SALES 20082569998.94Store 1
36SALES 20072107365.63Store 1
37SALES 20061780543.73Store 1
38LESS: COST OF SALES 61.67Store 1
39LESS: COST OF SALES 2022-4727358.71Store 1
40LESS: COST OF SALES %63.27Store 1
41LESS: COST OF SALES 2021-4351331.3Store 1
42LESS: COST OF SALES %65.5Store 1
43LESS: COST OF SALES 2020-5184178.21Store 1
44LESS: COST OF SALES %65.15316997Store 1
45LESS: COST OF SALES 2019-4163713.07Store 1
A
Cell Formulas
RangeFormula
I6I6=4163713.07/J5*100
D7,D17,F17,H17,Z7,X7,V7,F7,H7,J7:T7D7=SUM(D5:D6)
G7,I7G7=H7/H5*100
C7:C8,C15,C11:C12C7=D7/7665499.64*100
E7:E8,E17,E15,E11:E12E7=F7/6877877.23*100
I9I9=437017.25/J5*100
I10I10=912245.14/J5*100
D11,D15,F15,H15,Z11,X11,V11,F11,H11,J11:T11D11=SUM(D7:D10)
G11,I11G11=H11/H5*100
I13I13=83399.23/J5*100
G14G14=H14/H5*100
G15G15=H15/H5*100
C17,G17,I17C17=D17/D5*100
J17:T17,Z17,X17,V17J17=SUM(J11:J13)
A19A19=FORMULATEXT(A20)
A20:D234A20=IFNA(HSTACK(UNPIVOT(A5:A17,B4:Z4,B5:Z17),"Store 1"),"Store 1")
Dynamic array formulas.

S2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Store-2
2INCOME STATEMENT AS ON DECEMBER 31ST 2022
3
4Notes%2022%2021%2020%20192018201720162015201420132012201120102009 6 MONTHS
5SALES 12,530,323.812,889,934.173,540,411.513,381,540.623,269,185.763,599,922.763,498,175.824,448,249.486,982,799.806,544,735.716,082,094.584,560,255.732,529,657.04546,549.00
6COST OF SALE63.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)
7GROSS PROFIT 236.45922,238.8536.001,040,302.69341,213,279.3635.361,195,809.991,123,354.351,193,334.551,079,282.011,337,992.061,532,246.501,415,627.341,169,831.65930,968.62607,111.72180,719.94
8OTHER INCOME--2,735.2621,087.2349,998.6649,130.4275,705.0994,480.3155,837.3789,758.6348,734.37489.00
9--
10SALARY PAID TO EMPLOYEES12.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)
11GENERAL & ADM EXPENSES20.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)
12EBITDA33.7193,971.946.01173,680.339.7342,784.567.76262,295.97241,277.20262,561.87249,305.39260,011.51413,127.75508,249.08390,970.33255,672.96(13,817.48)(108,610.99)
13LESS 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)
14LESS: MANAGEMENT FEES0.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)
15LESS DEPRECIATION0.66(16,749.35)0.50(14,367.01)0.43(15,220.54)
16PROFIT BEFORE TAX42.7770,196.275.02145,216.618.41031245297,759.67
17LESS TAX PAYABLE4.90(3,278.95)(10,962.20)4.9(13,908.70)
18NET PROFIT52.6466,917.324.65134,254.418.02283,850.977.02237,371.92205,488.33224,396.42215,407.26224,166.51366,562.52450,321.46338,279.11228,158.77(30,791.97)(108,610.99)
19
20=IFNA(HSTACK(UNPIVOT(A5:A18,B4:T4,B5:T18),"Store 2"),"Store 2")
21SALES Notes1Store 2
22SALES 20222530323.81Store 2
23SALES 20212889934.17Store 2
24SALES 20203540411.51Store 2
25SALES 20193381540.62Store 2
26SALES 20183269185.76Store 2
27SALES 20173599922.76Store 2
28SALES 20163498175.82Store 2
29SALES 20154448249.48Store 2
30SALES 20146982799.8Store 2
31SALES 20136544735.71Store 2
32SALES 20126082094.58Store 2
33SALES 20114560255.73Store 2
34SALES 20102529657.04Store 2
35SALES 2009 6 MONTHS546549Store 2
36COST OF SALE%63.55Store 2
37COST OF SALE2022-1608084.96Store 2
38COST OF SALE%64Store 2
39COST OF SALE2021-1849631.48Store 2
A
Cell Formulas
RangeFormula
I6I6=2185730.63/J5*100
D7,D18,F18,H18,F7,H7,J7:T7D7=SUM(D5:D6)
G7,I7G7=H7/H5*100
C8:C9,C16,C12:C13C8=D8/2530323.81*100
E7:E9,E18,E16,E12:E13E7=F7/2889934.17*100
I10I10=288652/J5*100
I11I11=644862.02/J5*100
D12,F12,H12,J12:T12D12=SUM(D7:D11)
G12,I12G12=H12/H5*100
I14I14=24924.05/J5*100
D16,F16,H16D16=SUM(D12:D15)
G16G16=H16/H5*100
C18,G18,I18C18=D18/D5*100
J18:T18J18=SUM(J12:J14)
A20A20=FORMULATEXT(A21)
A21:D205A21=IFNA(HSTACK(UNPIVOT(A5:A18,B4:T4,B5:T18),"Store 2"),"Store 2")
Dynamic array formulas.

S3.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1Store-3
2INCOME STATEMENT AS ON DECEMBER 31ST 2022
3
4,%2022%2021%2020%201920182017201620152014201320122011201020092008
5SALES 12,879,841.753,266,367.544,765,376.953,434,119.912,766,042.262,388,318.732,704,385.363,110,292.063,415,709.053,903,096.914,683,396.593,487,151.312,142,854.952,098,597.941,532,053.92
6LESS: 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)
7GROSS PROFIT 237.301,074,165.2136.671,197,855.2735.31,683,196.7335.891,232,674.41952,820.58791,004.28850,699.45936,547.271,020,680.151,176,395.111,260,526.541,076,145.94649,686.95621,836.07469,192.74
8OTHER INCOME--3,134.3313,931.0138,749.7735,594.9840,958.1358,336.2649,200.6665,338.6337,523.9769,736.8934,857.72
9SALARY PAID TO EMPLOYEES11.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)
10GENERAL & ADM EXPENSES17.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)
11EBITDA38.35240,450.548.68283,365.1014.3680,021.7110.87373,272.16188,008.45134,138.44237,310.82197,490.70311,804.63436,409.56552,435.46476,399.7989,455.1391,255.0468,633.01
12LESS: 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)
13LESS: MANAGEMENT FEES0.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)
14LESS: DEPRECIATION + AMORT0.56(16,202.47)0.52(17,108.68)0.37(17,401.05)
15PROFIT BEFORE TAX47.08203,844.227.43242,837.1812.63971825602,330.22
16LESS TAX PAYABLE4.90(9,521.80)(19,796.71)4.9(28,135.54)
17NET PROFIT56.75194,322.426.83223,040.4712.04930242574,194.689.84337,802.86159,414.86112,253.19206,278.99169,569.78276,100.06389,947.34493,116.14426,646.2272,840.8175,126.7063,485.54
18
19=IFNA(HSTACK(UNPIVOT(A5:A17,B4:V4,B5:V17),"Store 3"),"Store 3")
20SALES ,1Store 3
21SALES 20222879841.75Store 3
22SALES 20213266367.54Store 3
23SALES 20204765376.95Store 3
24SALES 20193434119.91Store 3
25SALES 20182766042.26Store 3
26SALES 20172388318.73Store 3
27SALES 20162704385.36Store 3
28SALES 20153110292.06Store 3
29SALES 20143415709.05Store 3
30SALES 20133903096.91Store 3
31SALES 20124683396.59Store 3
32SALES 20113487151.31Store 3
33SALES 20102142854.95Store 3
34SALES 20092098597.94Store 3
35SALES 20081532053.92Store 3
36LESS: COST OF SALES %62.7Store 3
37LESS: COST OF SALES 2022-1805676.54Store 3
38LESS: COST OF SALES %63.33Store 3
39LESS: COST OF SALES 2021-2068512.27Store 3
A
Cell Formulas
RangeFormula
I6I6=2201445.5/J5*100
D7,D17,F17,H17,V7,H7,J7:T7,F7D7=SUM(D5:D6)
I7I7=J7/J5*100
C7:C8,C15,C11:C12C7=D7/2879841.75*100
E7:E8,E17,E15,E11:E12E7=F7/3266367.54*100
I9I9=275831/J5*100
I10I10=583571.25/J5*100
D11,D15,F15,H15,V11,F11,H11,J11:T11D11=SUM(D7:D10)
G11,I11G11=H11/H5*100
I13I13=35469.3/J5*100
G15G15=H15/H5*100
C17,G17,I17C17=D17/D5*100
J17:T17,V17J17=SUM(J11:J13)
A19A19=FORMULATEXT(A20)
A20:D214A20=IFNA(HSTACK(UNPIVOT(A5:A17,B4:V4,B5:V17),"Store 3"),"Store 3")
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

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

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top