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

Well-known Member
Joined
Mar 8, 2021
Messages
860
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
Great PQ challenge:
PQ alternative, lambda
HPIV(h,v) Hierarchy Pivot
h:
hierarchy index vector
v: values vector
Excel Formula:
=LAMBDA(h,v,LET(l,LEN(h),c,MAX(l),x,SORT(FILTER(h,l=c)),HSTACK(x,XLOOKUP(--LEFT(x,SEQUENCE(,c)),h,v))))
Book1
ABCDEFGHI
1
2=HPIV(B3:B18,C3:C18)
31Cosmetics111CosmeticsFaceCompact Powder
411Face121CosmeticsLipLip Stick
5111Compact Powder122CosmeticsLipLip Gloss
612Lip211MenAccessoriesBelt
7121Lip Stick212MenAccessoriesHat
8122Lip Gloss213MenAccessoriesSunglasses
92Men221MenShirtShirt Casual
1021Accessories311Toys & AccessoriesConstruction ToysLego
11211Belt
12212Hat
13213Sunglasses
1422Shirt
15221Shirt Casual
163Toys & Accessories
1731Construction Toys
18311Lego
19
Sheet1
Cell Formulas
RangeFormula
E2E2=FORMULATEXT(E3)
E3:H10E3=HPIV(B3:B18,C3:C18)
Dynamic array formulas.


HPIV can pivot any hierarchy structure

Book1
ABCDEFGHI
1added more hierarchy structure to the existing one
2
31Cosmetics111CosmeticsFaceCompact Powder
411Face121CosmeticsLipLip Stick
5111Compact Powder122CosmeticsLipLip Gloss
612Lip123CosmeticsLipLip Liner
7121Lip Stick131CosmeticsEyeEye Gloss
8122Lip Gloss132CosmeticsEyeEye Liner
92Men211MenAccessoriesBelt
1021Accessories212MenAccessoriesHat
11211Belt213MenAccessoriesSunglasses
12212Hat221MenShirtShirt Casual
13213Sunglasses222MenShirtShirt Elegant
1422Shirt231MenShoesLoafers
15221Shirt Casual232MenShoesDerby
163Toys & Accessories233MenShoesOxford
1731Construction Toys311Toys & AccessoriesConstruction ToysLego
18311Lego321Toys & AccessoriesPuzzlesJigsaw
19123Lip Liner322Toys & AccessoriesPuzzlesSudoku
2013Eye411WomenAccessoriesBelt
21131Eye Gloss412WomenAccessoriesHat
22132Eye Liner413WomenAccessoriesJewellery
23222Shirt Elegant421WomenShirtShirt Casual
2423Shoes422WomenShirtShirt Elegant
25231Loafers
26232Derby
27233Oxford
284Women
2941Accessories
30411Belt
31412Hat
32413Jewellery
3342Shirt
34421Shirt Casual
35422Shirt Elegant
3632Puzzles
37321Jigsaw
38322Sudoku
39
Sheet2
Cell Formulas
RangeFormula
E3:H24E3=HPIV(B3:B38,C3:C38)
Dynamic array formulas.
 

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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