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