GroupBy(Relation(A),[1])
C-C
A1 <= NA()
B1 <= COUNTIFS($A$1:$A1,$A1)
C1 <= IF($B1=1,A1,NA())
A2 <= NA()
B2 <= COUNTIFS($A$1:$A2,$A2)
C2 <= IF($B2=1,A2,NA())
GroupBy(Relation(A),[1])
----------------------------------------------------
GroupBy(Relation(A,B),[1,2]).CountDistinct[[1]]
E-G
A1 <= NA()
B1 <= NA()
C1 <= COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)
D1 <= IF(AND(ISERROR(A1)),INDEX(0,-1),COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1,$A$1:$A1,$A1))
E1 <= IF($C1=1,A1,NA())
F1 <= IF($C1=1,B1,NA())
G1 <= IF(ISNA(E1),NA(),COUNTIFS($A:$A,$A1,$B:$B,$B1,D:D,1))
A2 <= NA()
B2 <= NA()
C2 <= COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2)
D2 <= IF(AND(ISERROR(A2)),INDEX(0,-1),COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2,$A$1:$A2,$A2))
E2 <= IF($C2=1,A2,NA())
F2 <= IF($C2=1,B2,NA())
G2 <= IF(ISNA(E2),NA(),COUNTIFS($A:$A,$A2,$B:$B,$B2,D:D,1))
GroupBy(Relation(A,B),[1,2]).CountDistinct[[1]]
----------------------------------------------------
GroupBy(Relation(A,B)).CountDistinct[[1]]
E-E
A1 <= NA()
B1 <= NA()
C1 <= IF(ISNA(A1),0,1)
D1 <= IF(AND(ISERROR(A1)),INDEX(0,-1),COUNTIFS($A$1:$A1,$A1))
E1 <= IF(SUM(C:C)=0,NA(),IF(COUNT(D:D)=0,INDEX(0,-1),COUNTIFS(D:D,1)))
A2 <= NA()
B2 <= NA()
C2 <= IF(ISNA(A2),0,1)
D2 <= IF(AND(ISERROR(A2)),INDEX(0,-1),COUNTIFS($A$1:$A2,$A2))
E2 <= NA()
GroupBy(Relation(A,B)).CountDistinct[[1]]
----------------------------------------------------
GroupBy(Relation(A,B,C,D,E,F,G,H,I,J),[1,2])
L-M
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= NA()
F1 <= NA()
G1 <= NA()
H1 <= NA()
I1 <= NA()
J1 <= NA()
K1 <= COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)
L1 <= IF($K1=1,A1,NA())
M1 <= IF($K1=1,B1,NA())
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= NA()
F2 <= NA()
G2 <= NA()
H2 <= NA()
I2 <= NA()
J2 <= NA()
K2 <= COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2)
L2 <= IF($K2=1,A2,NA())
M2 <= IF($K2=1,B2,NA())
GroupBy(Relation(A,B,C,D,E,F,G,H,I,J),[1,2])
----------------------------------------------------
GroupBy(Relation(A,B,C,D,E,F,G,H,I,J),[1,2]).Min[3].Max[4].Sum[5].Count[[6,7]].Avg[8].CountDistinct[[9,10]]
Q-X
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= NA()
F1 <= NA()
G1 <= NA()
H1 <= NA()
I1 <= NA()
J1 <= NA()
K1 <= COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)
L1 <= IF(ISNA($A1),NA(),IF(ISERR($C1),COUNTIFS($A:$A,$A1,$B:$B,$B1)-COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1),COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,"<"&$C1)))
M1 <= IF(ISNA($A1),NA(),IF(ISERR($D1),COUNTIFS($A:$A,$A1,$B:$B,$B1)-COUNTIFS($A:$A,$A1,$B:$B,$B1,$D:$D,$D1),COUNTIFS($A:$A,$A1,$B:$B,$B1,$D:$D,">"&$D1)))
N1 <= IFERROR(E1,"")
O1 <= IFERROR(H1,"")
P1 <= IF(AND(ISERROR(I1),ISERROR(J1)),INDEX(0,-1),COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1,$I$1:$I1,$I1,$J$1:$J1,$J1))
Q1 <= IF($K1=1,A1,NA())
R1 <= IF($K1=1,B1,NA())
S1 <= IF(ISNA($Q1),NA(),AVERAGEIFS(C:C,$A:$A,$A1,$B:$B,$B1,$L:L,0))
T1 <= IF(ISNA($Q1),NA(),AVERAGEIFS(D:D,$A:$A,$A1,$B:$B,$B1,$M:M,0))
U1 <= IF(ISNA($Q1),NA(),SUMIFS(N:N,$A:$A,$A1,$B:$B,$B1))
V1 <= IF(ISNA($Q1),NA(),COUNTIFS($A:$A,$A1,$B:$B,$B1)-COUNTIFS($A:$A,$A1,$B:$B,$B1,F:F,INDEX(0,-1),G:G,INDEX(0,-1)))
W1 <= IF(ISNA($Q1),NA(),AVERAGEIFS(O:O,$A:$A,$A1,$B:$B,$B1))
X1 <= IF(ISNA(Q1),NA(),COUNTIFS($A:$A,$A1,$B:$B,$B1,P:P,1))
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= NA()
F2 <= NA()
G2 <= NA()
H2 <= NA()
I2 <= NA()
J2 <= NA()
K2 <= COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2)
L2 <= IF(ISNA($A2),NA(),IF(ISERR($C2),COUNTIFS($A:$A,$A2,$B:$B,$B2)-COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2),COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,"<"&$C2)))
M2 <= IF(ISNA($A2),NA(),IF(ISERR($D2),COUNTIFS($A:$A,$A2,$B:$B,$B2)-COUNTIFS($A:$A,$A2,$B:$B,$B2,$D:$D,$D2),COUNTIFS($A:$A,$A2,$B:$B,$B2,$D:$D,">"&$D2)))
N2 <= IFERROR(E2,"")
O2 <= IFERROR(H2,"")
P2 <= IF(AND(ISERROR(I2),ISERROR(J2)),INDEX(0,-1),COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2,$I$1:$I2,$I2,$J$1:$J2,$J2))
Q2 <= IF($K2=1,A2,NA())
R2 <= IF($K2=1,B2,NA())
S2 <= IF(ISNA($Q2),NA(),AVERAGEIFS(C:C,$A:$A,$A2,$B:$B,$B2,$L:L,0))
T2 <= IF(ISNA($Q2),NA(),AVERAGEIFS(D:D,$A:$A,$A2,$B:$B,$B2,$M:M,0))
U2 <= IF(ISNA($Q2),NA(),SUMIFS(N:N,$A:$A,$A2,$B:$B,$B2))
V2 <= IF(ISNA($Q2),NA(),COUNTIFS($A:$A,$A2,$B:$B,$B2)-COUNTIFS($A:$A,$A2,$B:$B,$B2,F:F,INDEX(0,-1),G:G,INDEX(0,-1)))
W2 <= IF(ISNA($Q2),NA(),AVERAGEIFS(O:O,$A:$A,$A2,$B:$B,$B2))
X2 <= IF(ISNA(Q2),NA(),COUNTIFS($A:$A,$A2,$B:$B,$B2,P:P,1))
GroupBy(Relation(A,B,C,D,E,F,G,H,I,J),[1,2]).Min[3].Max[4].Sum[5].Count[[6,7]].Avg[8].CountDistinct[[9,10]]
----------------------------------------------------
GroupBy(Relation(A,B,C,D,E,F,G,H,I,J)).Min[3].Max[4].Sum[5].Count[[6,7]].Avg[8].CountDistinct[[9,10]]
R-W
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= NA()
F1 <= NA()
G1 <= NA()
H1 <= NA()
I1 <= NA()
J1 <= NA()
K1 <= IF(ISNA(A1),0,1)
L1 <= IFERROR(C1,"")
M1 <= IFERROR(D1,"")
N1 <= IFERROR(E1,"")
O1 <= IF(AND(ISERROR(F1),ISERROR(G1)),INDEX(0,-1),1)
P1 <= IFERROR(H1,"")
Q1 <= IF(AND(ISERROR(I1),ISERROR(J1)),INDEX(0,-1),COUNTIFS($I$1:$I1,$I1,$J$1:$J1,$J1))
R1 <= IF(SUM(K:K)=0,NA(),IF(COUNT(L:L)=0,INDEX(0,-1),MIN(L:L)))
S1 <= IF(SUM(K:K)=0,NA(),IF(COUNT(M:M)=0,INDEX(0,-1),MAX(M:M)))
T1 <= IF(SUM(K:K)=0,NA(),IF(COUNT(N:N)=0,INDEX(0,-1),SUM(N:N)))
U1 <= IF(SUM(K:K)=0,NA(),IF(COUNT(O:O)=0,INDEX(0,-1),COUNTIFS(O:O,1)))
V1 <= IF(SUM(K:K)=0,NA(),IF(COUNT(P:P)=0,INDEX(0,-1),AVERAGE(P:P)))
W1 <= IF(SUM(K:K)=0,NA(),IF(COUNT(Q:Q)=0,INDEX(0,-1),COUNTIFS(Q:Q,1)))
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= NA()
F2 <= NA()
G2 <= NA()
H2 <= NA()
I2 <= NA()
J2 <= NA()
K2 <= IF(ISNA(A2),0,1)
L2 <= IFERROR(C2,"")
M2 <= IFERROR(D2,"")
N2 <= IFERROR(E2,"")
O2 <= IF(AND(ISERROR(F2),ISERROR(G2)),INDEX(0,-1),1)
P2 <= IFERROR(H2,"")
Q2 <= IF(AND(ISERROR(I2),ISERROR(J2)),INDEX(0,-1),COUNTIFS($I$1:$I2,$I2,$J$1:$J2,$J2))
R2 <= NA()
S2 <= NA()
T2 <= NA()
U2 <= NA()
V2 <= NA()
W2 <= NA()
GroupBy(Relation(A,B,C,D,E,F,G,H,I,J)).Min[3].Max[4].Sum[5].Count[[6,7]].Avg[8].CountDistinct[[9,10]]
----------------------------------------------------
CartProd(Relation(A),Relation(B))
E-F
A1 <= NA()
B1 <= NA()
C1 <= COUNTA(B:B)-COUNTIFS(B:B,NA())
D1 <= COUNTA(A:A)-COUNTIFS(A:A,NA())
E1 <= IF(ROW()>$C$1*$D$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$C$1)+1))
F1 <= IF(ISNA($E1),NA(),INDEX(B:B,MOD(ROW()-1,$C$1)+1))
A2 <= NA()
B2 <= NA()
E2 <= IF(ROW()>$C$1*$D$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$C$1)+1))
F2 <= IF(ISNA($E2),NA(),INDEX(B:B,MOD(ROW()-1,$C$1)+1))
CartProd(Relation(A),Relation(B))
----------------------------------------------------
CartProd(Relation(A,B),Relation(C,D))
G-J
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= COUNTA(C:C)-COUNTIFS(C:C,NA())
F1 <= COUNTA(A:A)-COUNTIFS(A:A,NA())
G1 <= IF(ROW()>$E$1*$F$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$E$1)+1))
H1 <= IF(ROW()>$E$1*$F$1,NA(),INDEX(B:B,QUOTIENT(ROW()-1,$E$1)+1))
I1 <= IF(ISNA($G1),NA(),INDEX(C:C,MOD(ROW()-1,$E$1)+1))
J1 <= IF(ISNA($G1),NA(),INDEX(D:D,MOD(ROW()-1,$E$1)+1))
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
G2 <= IF(ROW()>$E$1*$F$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$E$1)+1))
H2 <= IF(ROW()>$E$1*$F$1,NA(),INDEX(B:B,QUOTIENT(ROW()-1,$E$1)+1))
I2 <= IF(ISNA($G2),NA(),INDEX(C:C,MOD(ROW()-1,$E$1)+1))
J2 <= IF(ISNA($G2),NA(),INDEX(D:D,MOD(ROW()-1,$E$1)+1))
CartProd(Relation(A,B),Relation(C,D))
----------------------------------------------------
CartProd(Relation(A,B,C),Relation(D,E,F))
I-N
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= NA()
F1 <= NA()
G1 <= COUNTA(D:D)-COUNTIFS(D:D,NA())
H1 <= COUNTA(A:A)-COUNTIFS(A:A,NA())
I1 <= IF(ROW()>$G$1*$H$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$G$1)+1))
J1 <= IF(ROW()>$G$1*$H$1,NA(),INDEX(B:B,QUOTIENT(ROW()-1,$G$1)+1))
K1 <= IF(ROW()>$G$1*$H$1,NA(),INDEX(C:C,QUOTIENT(ROW()-1,$G$1)+1))
L1 <= IF(ISNA($I1),NA(),INDEX(D:D,MOD(ROW()-1,$G$1)+1))
M1 <= IF(ISNA($I1),NA(),INDEX(E:E,MOD(ROW()-1,$G$1)+1))
N1 <= IF(ISNA($I1),NA(),INDEX(F:F,MOD(ROW()-1,$G$1)+1))
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= NA()
F2 <= NA()
I2 <= IF(ROW()>$G$1*$H$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$G$1)+1))
J2 <= IF(ROW()>$G$1*$H$1,NA(),INDEX(B:B,QUOTIENT(ROW()-1,$G$1)+1))
K2 <= IF(ROW()>$G$1*$H$1,NA(),INDEX(C:C,QUOTIENT(ROW()-1,$G$1)+1))
L2 <= IF(ISNA($I2),NA(),INDEX(D:D,MOD(ROW()-1,$G$1)+1))
M2 <= IF(ISNA($I2),NA(),INDEX(E:E,MOD(ROW()-1,$G$1)+1))
N2 <= IF(ISNA($I2),NA(),INDEX(F:F,MOD(ROW()-1,$G$1)+1))
CartProd(Relation(A,B,C),Relation(D,E,F))
----------------------------------------------------
DiffBag(Relation(A),Relation(B))
C-C
A1 <= NA()
B1 <= NA()
C1 <= IF(COUNTIFS($B:$B,$A1)<COUNTIFS($A$1:$A1,$A1),A1,NA())
A2 <= NA()
B2 <= NA()
C2 <= IF(COUNTIFS($B:$B,$A2)<COUNTIFS($A$1:$A2,$A2),A2,NA())
DiffBag(Relation(A),Relation(B))
----------------------------------------------------
DiffBag(Relation(A,B),Relation(C,D))
E-F
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= IF(COUNTIFS($C:$C,$A1,$D:$D,$B1)<COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1),A1,NA())
F1 <= IF(ISNA($E1),NA(),B1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= IF(COUNTIFS($C:$C,$A2,$D:$D,$B2)<COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2),A2,NA())
F2 <= IF(ISNA($E2),NA(),B2)
DiffBag(Relation(A,B),Relation(C,D))
----------------------------------------------------
DiffSet(Relation(A),Relation(B))
E-E
A1 <= NA()
B1 <= NA()
C1 <= IF(OR(ISNA(A1),COUNTIFS($B:$B,$A1)>0),0,1)
D1 <= MATCH(ROW(),$C:$C,0)
E1 <= INDEX(A:A,$D1)
A2 <= NA()
B2 <= NA()
C2 <= IF(OR(ISNA($A2),COUNTIFS($A$1:$A2,$A2)>1,COUNTIFS($B:$B,$A2)>0),C1,1+C1)
D2 <= MATCH(ROW(),$C:$C,0)
E2 <= INDEX(A:A,$D2)
DiffSet(Relation(A),Relation(B))
----------------------------------------------------
DiffSet(Relation(A,B),Relation(C,D))
G-H
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= IF(OR(ISNA(A1),COUNTIFS($C:$C,$A1,$D:$D,$B1)>0),0,1)
F1 <= MATCH(ROW(),$E:$E,0)
G1 <= INDEX(A:A,$F1)
H1 <= INDEX(B:B,$F1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= IF(OR(ISNA($A2),COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2)>1,COUNTIFS($C:$C,$A2,$D:$D,$B2)>0),E1,1+E1)
F2 <= MATCH(ROW(),$E:$E,0)
G2 <= INDEX(A:A,$F2)
H2 <= INDEX(B:B,$F2)
DiffSet(Relation(A,B),Relation(C,D))
----------------------------------------------------
DupRem(Relation(A))
D-D
A1 <= NA()
B1 <= IF(ISNA(A1),0,1)
C1 <= MATCH(ROW(),$B:$B,0)
D1 <= INDEX(A:A,$C1)
A2 <= NA()
B2 <= IF(OR(ISNA($A2),COUNTIFS($A$1:$A2,$A2)>1),B1,1+B1)
C2 <= MATCH(ROW(),$B:$B,0)
D2 <= INDEX(A:A,$C2)
DupRem(Relation(A))
----------------------------------------------------
DupRem(Relation(A,B,C))
F-H
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= IF(ISNA(A1),0,1)
E1 <= MATCH(ROW(),$D:$D,0)
F1 <= INDEX(A:A,$E1)
G1 <= INDEX(B:B,$E1)
H1 <= INDEX(C:C,$E1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= IF(OR(ISNA($A2),COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2,$C$1:$C2,$C2)>1),D1,1+D1)
E2 <= MATCH(ROW(),$D:$D,0)
F2 <= INDEX(A:A,$E2)
G2 <= INDEX(B:B,$E2)
H2 <= INDEX(C:C,$E2)
DupRem(Relation(A,B,C))
----------------------------------------------------
EqJoin(Relation(A),Relation(B))
AH-AH
A1 <= NA()
B1 <= NA()
C1 <= COUNTA($A:$A)-COUNTIFS($A:$A,NA())
D1 <= IF(ISNA($A1),$C$1+1,IF(ISERR($A1),$C$1-COUNTIFS($A:$A,$A1)+COUNTIFS($A$1:$A1,$A1),COUNTIFS($A:$A,"<"&$A1)+COUNTIFS($A$1:$A1,$A1)))
E1 <= MATCH(ROW(),$D:$D,0)
F1 <= INDEX(A:A,$E1)
G1 <= COUNTA($B:$B)-COUNTIFS($B:$B,NA())
H1 <= IF(ISNA($B1),$G$1+1,IF(ISERR($B1),$G$1-COUNTIFS($B:$B,$B1)+COUNTIFS($B$1:$B1,$B1),COUNTIFS($B:$B,"<"&$B1)+COUNTIFS($B$1:$B1,$B1)))
I1 <= MATCH(ROW(),$H:$H,0)
J1 <= INDEX(B:B,$I1)
K1 <= IF(ISERROR(MATCH(F1,$J:$J,0)),NA(),F1)
L1 <= IF(ISERROR(MATCH(J1,$F:$F,0)),NA(),J1)
M1 <= COUNTIFS($K$1:$K1,$K1)
N1 <= IF($M1=1,K1,NA())
O1 <= 1
P1 <= COUNTIFS($L$1:$L1,$L1)
Q1 <= IF($P1=1,L1,NA())
R1 <= 1
S1 <= IF(ISNA($N1),0,1)
T1 <= MATCH(ROW(),$S:$S,0)
U1 <= INDEX(N:N,$T1)
V1 <= INDEX(O:O,$T1)
W1 <= IF(ISNA($Q1),0,1)
X1 <= MATCH(ROW(),$W:$W,0)
Y1 <= INDEX(Q:Q,$X1)
Z1 <= INDEX(R:R,$X1)
AA1 <= MATCH(U1,F:F,0)
AB1 <= MATCH(Y1,J:J,0)
AC1 <= IFERROR(V1*Z1,"")
AD1 <= 0
AE1 <= SUM(AC:AC)
AF1 <= IF(ROW()>$AE$1,NA(),MATCH(ROW()-1,$AD:$AD,1))
AG1 <= IF(ISNA(AF1),NA(),1)
AH1 <= INDEX(U:U,AF1)
A2 <= NA()
B2 <= NA()
D2 <= IF(ISNA($A2),$C$1+1,IF(ISERR($A2),$C$1-COUNTIFS($A:$A,$A2)+COUNTIFS($A$1:$A2,$A2),COUNTIFS($A:$A,"<"&$A2)+COUNTIFS($A$1:$A2,$A2)))
E2 <= MATCH(ROW(),$D:$D,0)
F2 <= INDEX(A:A,$E2)
H2 <= IF(ISNA($B2),$G$1+1,IF(ISERR($B2),$G$1-COUNTIFS($B:$B,$B2)+COUNTIFS($B$1:$B2,$B2),COUNTIFS($B:$B,"<"&$B2)+COUNTIFS($B$1:$B2,$B2)))
I2 <= MATCH(ROW(),$H:$H,0)
J2 <= INDEX(B:B,$I2)
K2 <= IF(ISERROR(MATCH(F2,$J:$J,0)),NA(),F2)
L2 <= IF(ISERROR(MATCH(J2,$F:$F,0)),NA(),J2)
M2 <= COUNTIFS($K$1:$K2,$K2)
N2 <= IF($M2=1,K2,NA())
O2 <= 1
P2 <= COUNTIFS($L$1:$L2,$L2)
Q2 <= IF($P2=1,L2,NA())
R2 <= 1
S2 <= IF(ISNA($N2),S1,S1+1)
T2 <= MATCH(ROW(),$S:$S,0)
U2 <= INDEX(N:N,$T2)
V2 <= INDEX(O:O,$T2)
W2 <= IF(ISNA($Q2),W1,W1+1)
X2 <= MATCH(ROW(),$W:$W,0)
Y2 <= INDEX(Q:Q,$X2)
Z2 <= INDEX(R:R,$X2)
AA2 <= MATCH(U2,F:F,0)
AB2 <= MATCH(Y2,J:J,0)
AC2 <= IFERROR(V2*Z2,"")
AD2 <= IFERROR(AC1+AD1,"")
AF2 <= IF(ROW()>$AE$1,NA(),MATCH(ROW()-1,$AD:$AD,1))
AG2 <= IF(ISNA(AF2),NA(),IF(AF2<>AF1,1,1+AG1))
AH2 <= INDEX(U:U,AF2)
EqJoin(Relation(A),Relation(B))
----------------------------------------------------
EqJoin(Relation(A,B),Relation(C,D))
AN-AP
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= COUNTA($A:$A)-COUNTIFS($A:$A,NA())
F1 <= IF(ISNA($A1),$E$1+1,IF(ISERR($A1),$E$1-COUNTIFS($A:$A,$A1)+COUNTIFS($A$1:$A1,$A1),COUNTIFS($A:$A,"<"&$A1)+COUNTIFS($A$1:$A1,$A1)))
G1 <= MATCH(ROW(),$F:$F,0)
H1 <= INDEX(A:A,$G1)
I1 <= INDEX(B:B,$G1)
J1 <= COUNTA($C:$C)-COUNTIFS($C:$C,NA())
K1 <= IF(ISNA($C1),$J$1+1,IF(ISERR($C1),$J$1-COUNTIFS($C:$C,$C1)+COUNTIFS($C$1:$C1,$C1),COUNTIFS($C:$C,"<"&$C1)+COUNTIFS($C$1:$C1,$C1)))
L1 <= MATCH(ROW(),$K:$K,0)
M1 <= INDEX(C:C,$L1)
N1 <= INDEX(D:D,$L1)
O1 <= IF(ISERROR(MATCH(H1,$M:$M,0)),NA(),H1)
P1 <= IF(ISNA(O1),NA(),I1)
Q1 <= IF(ISERROR(MATCH(M1,$H:$H,0)),NA(),M1)
R1 <= IF(ISNA(Q1),NA(),N1)
S1 <= COUNTIFS($O$1:$O1,$O1)
T1 <= IF($S1=1,O1,NA())
U1 <= IF(ISNA($T1),NA(),COUNTIFS($O:$O,$O1)-COUNTIFS($O:$O,$O1,P:P,INDEX(0,-1)))
V1 <= COUNTIFS($Q$1:$Q1,$Q1)
W1 <= IF($V1=1,Q1,NA())
X1 <= IF(ISNA($W1),NA(),COUNTIFS($Q:$Q,$Q1)-COUNTIFS($Q:$Q,$Q1,R:R,INDEX(0,-1)))
Y1 <= IF(ISNA($T1),0,1)
Z1 <= MATCH(ROW(),$Y:$Y,0)
AA1 <= INDEX(T:T,$Z1)
AB1 <= INDEX(U:U,$Z1)
AC1 <= IF(ISNA($W1),0,1)
AD1 <= MATCH(ROW(),$AC:$AC,0)
AE1 <= INDEX(W:W,$AD1)
AF1 <= INDEX(X:X,$AD1)
AG1 <= MATCH(AA1,H:H,0)
AH1 <= MATCH(AE1,M:M,0)
AI1 <= IFERROR(AB1*AF1,"")
AJ1 <= 0
AK1 <= SUM(AI:AI)
AL1 <= IF(ROW()>$AK$1,NA(),MATCH(ROW()-1,$AJ:$AJ,1))
AM1 <= IF(ISNA(AL1),NA(),1)
AN1 <= INDEX(AA:AA,AL1)
AO1 <= INDEX(I:I,INDEX(AG:AG,AL1)+MOD(AM1-1,INDEX($AB:$AB,AL1)))
AP1 <= INDEX($N:$N,INDEX($AH:$AH,AL1)+INT((AM1-1)/INDEX($AB:$AB,AL1)))
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
F2 <= IF(ISNA($A2),$E$1+1,IF(ISERR($A2),$E$1-COUNTIFS($A:$A,$A2)+COUNTIFS($A$1:$A2,$A2),COUNTIFS($A:$A,"<"&$A2)+COUNTIFS($A$1:$A2,$A2)))
G2 <= MATCH(ROW(),$F:$F,0)
H2 <= INDEX(A:A,$G2)
I2 <= INDEX(B:B,$G2)
K2 <= IF(ISNA($C2),$J$1+1,IF(ISERR($C2),$J$1-COUNTIFS($C:$C,$C2)+COUNTIFS($C$1:$C2,$C2),COUNTIFS($C:$C,"<"&$C2)+COUNTIFS($C$1:$C2,$C2)))
L2 <= MATCH(ROW(),$K:$K,0)
M2 <= INDEX(C:C,$L2)
N2 <= INDEX(D:D,$L2)
O2 <= IF(ISERROR(MATCH(H2,$M:$M,0)),NA(),H2)
P2 <= IF(ISNA(O2),NA(),I2)
Q2 <= IF(ISERROR(MATCH(M2,$H:$H,0)),NA(),M2)
R2 <= IF(ISNA(Q2),NA(),N2)
S2 <= COUNTIFS($O$1:$O2,$O2)
T2 <= IF($S2=1,O2,NA())
U2 <= IF(ISNA($T2),NA(),COUNTIFS($O:$O,$O2)-COUNTIFS($O:$O,$O2,P:P,INDEX(0,-1)))
V2 <= COUNTIFS($Q$1:$Q2,$Q2)
W2 <= IF($V2=1,Q2,NA())
X2 <= IF(ISNA($W2),NA(),COUNTIFS($Q:$Q,$Q2)-COUNTIFS($Q:$Q,$Q2,R:R,INDEX(0,-1)))
Y2 <= IF(ISNA($T2),Y1,Y1+1)
Z2 <= MATCH(ROW(),$Y:$Y,0)
AA2 <= INDEX(T:T,$Z2)
AB2 <= INDEX(U:U,$Z2)
AC2 <= IF(ISNA($W2),AC1,AC1+1)
AD2 <= MATCH(ROW(),$AC:$AC,0)
AE2 <= INDEX(W:W,$AD2)
AF2 <= INDEX(X:X,$AD2)
AG2 <= MATCH(AA2,H:H,0)
AH2 <= MATCH(AE2,M:M,0)
AI2 <= IFERROR(AB2*AF2,"")
AJ2 <= IFERROR(AI1+AJ1,"")
AL2 <= IF(ROW()>$AK$1,NA(),MATCH(ROW()-1,$AJ:$AJ,1))
AM2 <= IF(ISNA(AL2),NA(),IF(AL2<>AL1,1,1+AM1))
AN2 <= INDEX(AA:AA,AL2)
AO2 <= INDEX(I:I,INDEX(AG:AG,AL2)+MOD(AM2-1,INDEX($AB:$AB,AL2)))
AP2 <= INDEX($N:$N,INDEX($AH:$AH,AL2)+INT((AM2-1)/INDEX($AB:$AB,AL2)))
EqJoin(Relation(A,B),Relation(C,D))
----------------------------------------------------
EqJoin(Relation(A,B,C,D),Relation(E,F,G,H))
AZ-BF
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= NA()
F1 <= NA()
G1 <= NA()
H1 <= NA()
I1 <= COUNTA($B:$B)-COUNTIFS($B:$B,NA())
J1 <= IF(ISNA($B1),$I$1+1,IF(ISERR($B1),$I$1-COUNTIFS($B:$B,$B1)+COUNTIFS($B$1:$B1,$B1),COUNTIFS($B:$B,"<"&$B1)+COUNTIFS($B$1:$B1,$B1)))
K1 <= MATCH(ROW(),$J:$J,0)
L1 <= INDEX(A:A,$K1)
M1 <= INDEX(B:B,$K1)
N1 <= INDEX(C:C,$K1)
O1 <= INDEX(D:D,$K1)
P1 <= COUNTA($G:$G)-COUNTIFS($G:$G,NA())
Q1 <= IF(ISNA($G1),$P$1+1,IF(ISERR($G1),$P$1-COUNTIFS($G:$G,$G1)+COUNTIFS($G$1:$G1,$G1),COUNTIFS($G:$G,"<"&$G1)+COUNTIFS($G$1:$G1,$G1)))
R1 <= MATCH(ROW(),$Q:$Q,0)
S1 <= INDEX(E:E,$R1)
T1 <= INDEX(F:F,$R1)
U1 <= INDEX(G:G,$R1)
V1 <= INDEX(H:H,$R1)
W1 <= IF(ISERROR(MATCH(M1,$U:$U,0)),NA(),M1)
X1 <= IF(ISNA(W1),NA(),L1)
Y1 <= IF(ISNA(W1),NA(),N1)
Z1 <= IF(ISNA(W1),NA(),O1)
AA1 <= IF(ISERROR(MATCH(U1,$M:$M,0)),NA(),U1)
AB1 <= IF(ISNA(AA1),NA(),S1)
AC1 <= IF(ISNA(AA1),NA(),T1)
AD1 <= IF(ISNA(AA1),NA(),V1)
AE1 <= COUNTIFS($W$1:$W1,$W1)
AF1 <= IF($AE1=1,W1,NA())
AG1 <= IF(ISNA($AF1),NA(),COUNTIFS($W:$W,$W1)-COUNTIFS($W:$W,$W1,X:X,INDEX(0,-1),Y:Y,INDEX(0,-1),Z:Z,INDEX(0,-1)))
AH1 <= COUNTIFS($AA$1:$AA1,$AA1)
AI1 <= IF($AH1=1,AA1,NA())
AJ1 <= IF(ISNA($AI1),NA(),COUNTIFS($AA:$AA,$AA1)-COUNTIFS($AA:$AA,$AA1,AB:AB,INDEX(0,-1),AC:AC,INDEX(0,-1),AD:AD,INDEX(0,-1)))
AK1 <= IF(ISNA($AF1),0,1)
AL1 <= MATCH(ROW(),$AK:$AK,0)
AM1 <= INDEX(AF:AF,$AL1)
AN1 <= INDEX(AG:AG,$AL1)
AO1 <= IF(ISNA($AI1),0,1)
AP1 <= MATCH(ROW(),$AO:$AO,0)
AQ1 <= INDEX(AI:AI,$AP1)
AR1 <= INDEX(AJ:AJ,$AP1)
AS1 <= MATCH(AM1,M:M,0)
AT1 <= MATCH(AQ1,U:U,0)
AU1 <= IFERROR(AN1*AR1,"")
AV1 <= 0
AW1 <= SUM(AU:AU)
AX1 <= IF(ROW()>$AW$1,NA(),MATCH(ROW()-1,$AV:$AV,1))
AY1 <= IF(ISNA(AX1),NA(),1)
AZ1 <= INDEX(AM:AM,AX1)
BA1 <= INDEX(L:L,INDEX(AS:AS,AX1)+MOD(AY1-1,INDEX($AN:$AN,AX1)))
BB1 <= INDEX(N:N,INDEX(AS:AS,AX1)+MOD(AY1-1,INDEX($AN:$AN,AX1)))
BC1 <= INDEX(O:O,INDEX(AS:AS,AX1)+MOD(AY1-1,INDEX($AN:$AN,AX1)))
BD1 <= INDEX($S:$S,INDEX($AT:$AT,AX1)+INT((AY1-1)/INDEX($AN:$AN,AX1)))
BE1 <= INDEX($T:$T,INDEX($AT:$AT,AX1)+INT((AY1-1)/INDEX($AN:$AN,AX1)))
BF1 <= INDEX($V:$V,INDEX($AT:$AT,AX1)+INT((AY1-1)/INDEX($AN:$AN,AX1)))
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= NA()
F2 <= NA()
G2 <= NA()
H2 <= NA()
J2 <= IF(ISNA($B2),$I$1+1,IF(ISERR($B2),$I$1-COUNTIFS($B:$B,$B2)+COUNTIFS($B$1:$B2,$B2),COUNTIFS($B:$B,"<"&$B2)+COUNTIFS($B$1:$B2,$B2)))
K2 <= MATCH(ROW(),$J:$J,0)
L2 <= INDEX(A:A,$K2)
M2 <= INDEX(B:B,$K2)
N2 <= INDEX(C:C,$K2)
O2 <= INDEX(D:D,$K2)
Q2 <= IF(ISNA($G2),$P$1+1,IF(ISERR($G2),$P$1-COUNTIFS($G:$G,$G2)+COUNTIFS($G$1:$G2,$G2),COUNTIFS($G:$G,"<"&$G2)+COUNTIFS($G$1:$G2,$G2)))
R2 <= MATCH(ROW(),$Q:$Q,0)
S2 <= INDEX(E:E,$R2)
T2 <= INDEX(F:F,$R2)
U2 <= INDEX(G:G,$R2)
V2 <= INDEX(H:H,$R2)
W2 <= IF(ISERROR(MATCH(M2,$U:$U,0)),NA(),M2)
X2 <= IF(ISNA(W2),NA(),L2)
Y2 <= IF(ISNA(W2),NA(),N2)
Z2 <= IF(ISNA(W2),NA(),O2)
AA2 <= IF(ISERROR(MATCH(U2,$M:$M,0)),NA(),U2)
AB2 <= IF(ISNA(AA2),NA(),S2)
AC2 <= IF(ISNA(AA2),NA(),T2)
AD2 <= IF(ISNA(AA2),NA(),V2)
AE2 <= COUNTIFS($W$1:$W2,$W2)
AF2 <= IF($AE2=1,W2,NA())
AG2 <= IF(ISNA($AF2),NA(),COUNTIFS($W:$W,$W2)-COUNTIFS($W:$W,$W2,X:X,INDEX(0,-1),Y:Y,INDEX(0,-1),Z:Z,INDEX(0,-1)))
AH2 <= COUNTIFS($AA$1:$AA2,$AA2)
AI2 <= IF($AH2=1,AA2,NA())
AJ2 <= IF(ISNA($AI2),NA(),COUNTIFS($AA:$AA,$AA2)-COUNTIFS($AA:$AA,$AA2,AB:AB,INDEX(0,-1),AC:AC,INDEX(0,-1),AD:AD,INDEX(0,-1)))
AK2 <= IF(ISNA($AF2),AK1,AK1+1)
AL2 <= MATCH(ROW(),$AK:$AK,0)
AM2 <= INDEX(AF:AF,$AL2)
AN2 <= INDEX(AG:AG,$AL2)
AO2 <= IF(ISNA($AI2),AO1,AO1+1)
AP2 <= MATCH(ROW(),$AO:$AO,0)
AQ2 <= INDEX(AI:AI,$AP2)
AR2 <= INDEX(AJ:AJ,$AP2)
AS2 <= MATCH(AM2,M:M,0)
AT2 <= MATCH(AQ2,U:U,0)
AU2 <= IFERROR(AN2*AR2,"")
AV2 <= IFERROR(AU1+AV1,"")
AX2 <= IF(ROW()>$AW$1,NA(),MATCH(ROW()-1,$AV:$AV,1))
AY2 <= IF(ISNA(AX2),NA(),IF(AX2<>AX1,1,1+AY1))
AZ2 <= INDEX(AM:AM,AX2)
BA2 <= INDEX(L:L,INDEX(AS:AS,AX2)+MOD(AY2-1,INDEX($AN:$AN,AX2)))
BB2 <= INDEX(N:N,INDEX(AS:AS,AX2)+MOD(AY2-1,INDEX($AN:$AN,AX2)))
BC2 <= INDEX(O:O,INDEX(AS:AS,AX2)+MOD(AY2-1,INDEX($AN:$AN,AX2)))
BD2 <= INDEX($S:$S,INDEX($AT:$AT,AX2)+INT((AY2-1)/INDEX($AN:$AN,AX2)))
BE2 <= INDEX($T:$T,INDEX($AT:$AT,AX2)+INT((AY2-1)/INDEX($AN:$AN,AX2)))
BF2 <= INDEX($V:$V,INDEX($AT:$AT,AX2)+INT((AY2-1)/INDEX($AN:$AN,AX2)))
EqJoin(Relation(A,B,C,D),Relation(E,F,G,H))
----------------------------------------------------
Filter(Relation(A),"TRUE()")
F-F
A1 <= NA()
B1 <= TRUE()
C1 <= IF(ISERROR(B1),NA(),IF(B1, A1, NA()))
D1 <= IF(ISNA($C1),0,1)
E1 <= MATCH(ROW(),$D:$D,0)
F1 <= INDEX(C:C,$E1)
A2 <= NA()
B2 <= TRUE()
C2 <= IF(ISERROR(B2),NA(),IF(B2, A2, NA()))
D2 <= IF(ISNA($C2),D1,D1+1)
E2 <= MATCH(ROW(),$D:$D,0)
F2 <= INDEX(C:C,$E2)
Filter(Relation(A),"TRUE()")
----------------------------------------------------
Filter(Relation(A),"1=1")
F-F
A1 <= NA()
B1 <= 1=1
C1 <= IF(ISERROR(B1),NA(),IF(B1, A1, NA()))
D1 <= IF(ISNA($C1),0,1)
E1 <= MATCH(ROW(),$D:$D,0)
F1 <= INDEX(C:C,$E1)
A2 <= NA()
B2 <= 1=1
C2 <= IF(ISERROR(B2),NA(),IF(B2, A2, NA()))
D2 <= IF(ISNA($C2),D1,D1+1)
E2 <= MATCH(ROW(),$D:$D,0)
F2 <= INDEX(C:C,$E2)
Filter(Relation(A),"1=1")
----------------------------------------------------
Filter(Relation(A),"#1="X"")
F-F
A1 <= NA()
B1 <= A1="X"
C1 <= IF(ISERROR(B1),NA(),IF(B1, A1, NA()))
D1 <= IF(ISNA($C1),0,1)
E1 <= MATCH(ROW(),$D:$D,0)
F1 <= INDEX(C:C,$E1)
A2 <= NA()
B2 <= A2="X"
C2 <= IF(ISERROR(B2),NA(),IF(B2, A2, NA()))
D2 <= IF(ISNA($C2),D1,D1+1)
E2 <= MATCH(ROW(),$D:$D,0)
F2 <= INDEX(C:C,$E2)
Filter(Relation(A),"#1="X"")
----------------------------------------------------
Filter(Relation(A,B,C),"#2=2")
J-L
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= B1=2
E1 <= IF(ISERROR(D1),NA(),IF(D1, A1, NA()))
F1 <= IF(ISERROR(D1),NA(),IF(D1, B1, NA()))
G1 <= IF(ISERROR(D1),NA(),IF(D1, C1, NA()))
H1 <= IF(ISNA($E1),0,1)
I1 <= MATCH(ROW(),$H:$H,0)
J1 <= INDEX(E:E,$I1)
K1 <= INDEX(F:F,$I1)
L1 <= INDEX(G:G,$I1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= B2=2
E2 <= IF(ISERROR(D2),NA(),IF(D2, A2, NA()))
F2 <= IF(ISERROR(D2),NA(),IF(D2, B2, NA()))
G2 <= IF(ISERROR(D2),NA(),IF(D2, C2, NA()))
H2 <= IF(ISNA($E2),H1,H1+1)
I2 <= MATCH(ROW(),$H:$H,0)
J2 <= INDEX(E:E,$I2)
K2 <= INDEX(F:F,$I2)
L2 <= INDEX(G:G,$I2)
Filter(Relation(A,B,C),"#2=2")
----------------------------------------------------
GenJoin(Relation(A),Relation(B),"TRUE()")
L-M
A1 <= NA()
B1 <= NA()
C1 <= COUNTA(B:B)-COUNTIFS(B:B,NA())
D1 <= COUNTA(A:A)-COUNTIFS(A:A,NA())
E1 <= IF(ROW()>$C$1*$D$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$C$1)+1))
F1 <= IF(ISNA($E1),NA(),INDEX(B:B,MOD(ROW()-1,$C$1)+1))
G1 <= TRUE()
H1 <= IF(ISERROR(G1),NA(),IF(G1, E1, NA()))
I1 <= IF(ISERROR(G1),NA(),IF(G1, F1, NA()))
J1 <= IF(ISNA($H1),0,1)
K1 <= MATCH(ROW(),$J:$J,0)
L1 <= INDEX(H:H,$K1)
M1 <= INDEX(I:I,$K1)
A2 <= NA()
B2 <= NA()
E2 <= IF(ROW()>$C$1*$D$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$C$1)+1))
F2 <= IF(ISNA($E2),NA(),INDEX(B:B,MOD(ROW()-1,$C$1)+1))
G2 <= TRUE()
H2 <= IF(ISERROR(G2),NA(),IF(G2, E2, NA()))
I2 <= IF(ISERROR(G2),NA(),IF(G2, F2, NA()))
J2 <= IF(ISNA($H2),J1,J1+1)
K2 <= MATCH(ROW(),$J:$J,0)
L2 <= INDEX(H:H,$K2)
M2 <= INDEX(I:I,$K2)
GenJoin(Relation(A),Relation(B),"TRUE()")
----------------------------------------------------
GenJoin(Relation(A,B),Relation(C,D),"#2<#4")
R-U
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= COUNTA(C:C)-COUNTIFS(C:C,NA())
F1 <= COUNTA(A:A)-COUNTIFS(A:A,NA())
G1 <= IF(ROW()>$E$1*$F$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$E$1)+1))
H1 <= IF(ROW()>$E$1*$F$1,NA(),INDEX(B:B,QUOTIENT(ROW()-1,$E$1)+1))
I1 <= IF(ISNA($G1),NA(),INDEX(C:C,MOD(ROW()-1,$E$1)+1))
J1 <= IF(ISNA($G1),NA(),INDEX(D:D,MOD(ROW()-1,$E$1)+1))
K1 <= H1<J1
L1 <= IF(ISERROR(K1),NA(),IF(K1, G1, NA()))
M1 <= IF(ISERROR(K1),NA(),IF(K1, H1, NA()))
N1 <= IF(ISERROR(K1),NA(),IF(K1, I1, NA()))
O1 <= IF(ISERROR(K1),NA(),IF(K1, J1, NA()))
P1 <= IF(ISNA($L1),0,1)
Q1 <= MATCH(ROW(),$P:$P,0)
R1 <= INDEX(L:L,$Q1)
S1 <= INDEX(M:M,$Q1)
T1 <= INDEX(N:N,$Q1)
U1 <= INDEX(O:O,$Q1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
G2 <= IF(ROW()>$E$1*$F$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$E$1)+1))
H2 <= IF(ROW()>$E$1*$F$1,NA(),INDEX(B:B,QUOTIENT(ROW()-1,$E$1)+1))
I2 <= IF(ISNA($G2),NA(),INDEX(C:C,MOD(ROW()-1,$E$1)+1))
J2 <= IF(ISNA($G2),NA(),INDEX(D:D,MOD(ROW()-1,$E$1)+1))
K2 <= H2<J2
L2 <= IF(ISERROR(K2),NA(),IF(K2, G2, NA()))
M2 <= IF(ISERROR(K2),NA(),IF(K2, H2, NA()))
N2 <= IF(ISERROR(K2),NA(),IF(K2, I2, NA()))
O2 <= IF(ISERROR(K2),NA(),IF(K2, J2, NA()))
P2 <= IF(ISNA($L2),P1,P1+1)
Q2 <= MATCH(ROW(),$P:$P,0)
R2 <= INDEX(L:L,$Q2)
S2 <= INDEX(M:M,$Q2)
T2 <= INDEX(N:N,$Q2)
U2 <= INDEX(O:O,$Q2)
GenJoin(Relation(A,B),Relation(C,D),"#2<#4")
----------------------------------------------------
Project(Relation(A),[1])
B-B
A1 <= NA()
B1 <= A1
A2 <= NA()
B2 <= A2
Project(Relation(A),[1])
----------------------------------------------------
Project(Relation(A,B,C,D,E),[2, 3, 5])
F-H
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= NA()
F1 <= B1
G1 <= C1
H1 <= E1
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= NA()
F2 <= B2
G2 <= C2
H2 <= E2
Project(Relation(A,B,C,D,E),[2, 3, 5])
----------------------------------------------------
SemiJoin(Relation(A),Relation(B))
C-C
A1 <= NA()
B1 <= NA()
C1 <= IF(ISERROR(MATCH(A1,$B:$B,0)),NA(),A1)
A2 <= NA()
B2 <= NA()
C2 <= IF(ISERROR(MATCH(A2,$B:$B,0)),NA(),A2)
SemiJoin(Relation(A),Relation(B))
----------------------------------------------------
SemiJoin(Relation(A,B),Relation(C,D))
E-F
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= IF(ISERROR(MATCH(A1,$D:$D,0)),NA(),A1)
F1 <= IF(ISNA(E1),NA(),B1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
E2 <= IF(ISERROR(MATCH(A2,$D:$D,0)),NA(),A2)
F2 <= IF(ISNA(E2),NA(),B2)
SemiJoin(Relation(A,B),Relation(C,D))
----------------------------------------------------
SortAsc(Relation(A,B,C),1)
G-I
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= COUNTA($A:$A)-COUNTIFS($A:$A,NA())
E1 <= IF(ISNA($A1),$D$1+1,IF(ISERR($A1),$D$1-COUNTIFS($A:$A,$A1)+COUNTIFS($A$1:$A1,$A1),COUNTIFS($A:$A,"<"&$A1)+COUNTIFS($A$1:$A1,$A1)))
F1 <= MATCH(ROW(),$E:$E,0)
G1 <= INDEX(A:A,$F1)
H1 <= INDEX(B:B,$F1)
I1 <= INDEX(C:C,$F1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
E2 <= IF(ISNA($A2),$D$1+1,IF(ISERR($A2),$D$1-COUNTIFS($A:$A,$A2)+COUNTIFS($A$1:$A2,$A2),COUNTIFS($A:$A,"<"&$A2)+COUNTIFS($A$1:$A2,$A2)))
F2 <= MATCH(ROW(),$E:$E,0)
G2 <= INDEX(A:A,$F2)
H2 <= INDEX(B:B,$F2)
I2 <= INDEX(C:C,$F2)
SortAsc(Relation(A,B,C),1)
----------------------------------------------------
SortDesc(Relation(A,B,C),1)
G-I
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= COUNTA($A:$A)-COUNTIFS($A:$A,NA())
E1 <= IF(ISNA($A1),$D$1+1,IF(ISERR($A1),$D$1-COUNTIFS($A:$A,$A1)+COUNTIFS($A$1:$A1,$A1),COUNTIFS($A:$A,">"&$A1)+COUNTIFS($A$1:$A1,$A1)))
F1 <= MATCH(ROW(),$E:$E,0)
G1 <= INDEX(A:A,$F1)
H1 <= INDEX(B:B,$F1)
I1 <= INDEX(C:C,$F1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
E2 <= IF(ISNA($A2),$D$1+1,IF(ISERR($A2),$D$1-COUNTIFS($A:$A,$A2)+COUNTIFS($A$1:$A2,$A2),COUNTIFS($A:$A,">"&$A2)+COUNTIFS($A$1:$A2,$A2)))
F2 <= MATCH(ROW(),$E:$E,0)
G2 <= INDEX(A:A,$F2)
H2 <= INDEX(B:B,$F2)
I2 <= INDEX(C:C,$F2)
SortDesc(Relation(A,B,C),1)
----------------------------------------------------
Stand(Relation(A,B,C))
F-H
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= IF(ISNA($A1),0,1)
E1 <= MATCH(ROW(),$D:$D,0)
F1 <= INDEX(A:A,$E1)
G1 <= INDEX(B:B,$E1)
H1 <= INDEX(C:C,$E1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= IF(ISNA($A2),D1,D1+1)
E2 <= MATCH(ROW(),$D:$D,0)
F2 <= INDEX(A:A,$E2)
G2 <= INDEX(B:B,$E2)
H2 <= INDEX(C:C,$E2)
Stand(Relation(A,B,C))
----------------------------------------------------
Union(Relation(A,B),Relation(C,D))
F-G
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= NA()
E1 <= COUNTA($C:$C)-COUNTIF($C:$C,NA())
F1 <= IF(ROW()<=$E$1,C1,INDEX(A:A,ROW()-$E$1))
G1 <= IF(ROW()<=$E$1,D1,INDEX(B:B,ROW()-$E$1))
A2 <= NA()
B2 <= NA()
C2 <= NA()
D2 <= NA()
F2 <= IF(ROW()<=$E$1,C2,INDEX(A:A,ROW()-$E$1))
G2 <= IF(ROW()<=$E$1,D2,INDEX(B:B,ROW()-$E$1))
Union(Relation(A,B),Relation(C,D))
----------------------------------------------------
Union(tab=Relation(A),Reference(tab))
C-C
A1 <= NA()
B1 <= COUNTA($A:$A)-COUNTIF($A:$A,NA())
C1 <= IF(ROW()<=$B$1,A1,INDEX(A:A,ROW()-$B$1))
A2 <= NA()
C2 <= IF(ROW()<=$B$1,A2,INDEX(A:A,ROW()-$B$1))
Union(tab=Relation(A),Reference(tab))
----------------------------------------------------
DiffSet(Project(EqJoin(tab=Relation(A,B,C),Reference(tab)),[2, 4]),Project(Reference(tab),[1, 2]))
BB-BC
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= COUNTA($B:$B)-COUNTIFS($B:$B,NA())
E1 <= IF(ISNA($B1),$D$1+1,IF(ISERR($B1),$D$1-COUNTIFS($B:$B,$B1)+COUNTIFS($B$1:$B1,$B1),COUNTIFS($B:$B,"<"&$B1)+COUNTIFS($B$1:$B1,$B1)))
F1 <= MATCH(ROW(),$E:$E,0)
G1 <= INDEX(A:A,$F1)
H1 <= INDEX(B:B,$F1)
I1 <= INDEX(C:C,$F1)
J1 <= COUNTA($A:$A)-COUNTIFS($A:$A,NA())
K1 <= IF(ISNA($A1),$J$1+1,IF(ISERR($A1),$J$1-COUNTIFS($A:$A,$A1)+COUNTIFS($A$1:$A1,$A1),COUNTIFS($A:$A,"<"&$A1)+COUNTIFS($A$1:$A1,$A1)))
L1 <= MATCH(ROW(),$K:$K,0)
M1 <= INDEX(A:A,$L1)
N1 <= INDEX(B:B,$L1)
O1 <= INDEX(C:C,$L1)
P1 <= IF(ISERROR(MATCH(H1,$M:$M,0)),NA(),H1)
Q1 <= IF(ISNA(P1),NA(),G1)
R1 <= IF(ISNA(P1),NA(),I1)
S1 <= IF(ISERROR(MATCH(M1,$H:$H,0)),NA(),M1)
T1 <= IF(ISNA(S1),NA(),N1)
U1 <= IF(ISNA(S1),NA(),O1)
V1 <= COUNTIFS($P$1:$P1,$P1)
W1 <= IF($V1=1,P1,NA())
X1 <= IF(ISNA($W1),NA(),COUNTIFS($P:$P,$P1)-COUNTIFS($P:$P,$P1,Q:Q,INDEX(0,-1),R:R,INDEX(0,-1)))
Y1 <= COUNTIFS($S$1:$S1,$S1)
Z1 <= IF($Y1=1,S1,NA())
AA1 <= IF(ISNA($Z1),NA(),COUNTIFS($S:$S,$S1)-COUNTIFS($S:$S,$S1,T:T,INDEX(0,-1),U:U,INDEX(0,-1)))
AB1 <= IF(ISNA($W1),0,1)
AC1 <= MATCH(ROW(),$AB:$AB,0)
AD1 <= INDEX(W:W,$AC1)
AE1 <= INDEX(X:X,$AC1)
AF1 <= IF(ISNA($Z1),0,1)
AG1 <= MATCH(ROW(),$AF:$AF,0)
AH1 <= INDEX(Z:Z,$AG1)
AI1 <= INDEX(AA:AA,$AG1)
AJ1 <= MATCH(AD1,H:H,0)
AK1 <= MATCH(AH1,M:M,0)
AL1 <= IFERROR(AE1*AI1,"")
AM1 <= 0
AN1 <= SUM(AL:AL)
AO1 <= IF(ROW()>$AN$1,NA(),MATCH(ROW()-1,$AM:$AM,1))
AP1 <= IF(ISNA(AO1),NA(),1)
AQ1 <= INDEX(AD:AD,AO1)
AR1 <= INDEX(G:G,INDEX(AJ:AJ,AO1)+MOD(AP1-1,INDEX($AE:$AE,AO1)))
AS1 <= INDEX(I:I,INDEX(AJ:AJ,AO1)+MOD(AP1-1,INDEX($AE:$AE,AO1)))
AT1 <= INDEX($N:$N,INDEX($AK:$AK,AO1)+INT((AP1-1)/INDEX($AE:$AE,AO1)))
AU1 <= INDEX($O:$O,INDEX($AK:$AK,AO1)+INT((AP1-1)/INDEX($AE:$AE,AO1)))
AV1 <= AR1
AW1 <= AT1
AX1 <= A1
AY1 <= B1
AZ1 <= IF(OR(ISNA(AV1),COUNTIFS($AX:$AX,$AV1,$AY:$AY,$AW1)>0),0,1)
BA1 <= MATCH(ROW(),$AZ:$AZ,0)
BB1 <= INDEX(AV:AV,$BA1)
BC1 <= INDEX(AW:AW,$BA1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
E2 <= IF(ISNA($B2),$D$1+1,IF(ISERR($B2),$D$1-COUNTIFS($B:$B,$B2)+COUNTIFS($B$1:$B2,$B2),COUNTIFS($B:$B,"<"&$B2)+COUNTIFS($B$1:$B2,$B2)))
F2 <= MATCH(ROW(),$E:$E,0)
G2 <= INDEX(A:A,$F2)
H2 <= INDEX(B:B,$F2)
I2 <= INDEX(C:C,$F2)
K2 <= IF(ISNA($A2),$J$1+1,IF(ISERR($A2),$J$1-COUNTIFS($A:$A,$A2)+COUNTIFS($A$1:$A2,$A2),COUNTIFS($A:$A,"<"&$A2)+COUNTIFS($A$1:$A2,$A2)))
L2 <= MATCH(ROW(),$K:$K,0)
M2 <= INDEX(A:A,$L2)
N2 <= INDEX(B:B,$L2)
O2 <= INDEX(C:C,$L2)
P2 <= IF(ISERROR(MATCH(H2,$M:$M,0)),NA(),H2)
Q2 <= IF(ISNA(P2),NA(),G2)
R2 <= IF(ISNA(P2),NA(),I2)
S2 <= IF(ISERROR(MATCH(M2,$H:$H,0)),NA(),M2)
T2 <= IF(ISNA(S2),NA(),N2)
U2 <= IF(ISNA(S2),NA(),O2)
V2 <= COUNTIFS($P$1:$P2,$P2)
W2 <= IF($V2=1,P2,NA())
X2 <= IF(ISNA($W2),NA(),COUNTIFS($P:$P,$P2)-COUNTIFS($P:$P,$P2,Q:Q,INDEX(0,-1),R:R,INDEX(0,-1)))
Y2 <= COUNTIFS($S$1:$S2,$S2)
Z2 <= IF($Y2=1,S2,NA())
AA2 <= IF(ISNA($Z2),NA(),COUNTIFS($S:$S,$S2)-COUNTIFS($S:$S,$S2,T:T,INDEX(0,-1),U:U,INDEX(0,-1)))
AB2 <= IF(ISNA($W2),AB1,AB1+1)
AC2 <= MATCH(ROW(),$AB:$AB,0)
AD2 <= INDEX(W:W,$AC2)
AE2 <= INDEX(X:X,$AC2)
AF2 <= IF(ISNA($Z2),AF1,AF1+1)
AG2 <= MATCH(ROW(),$AF:$AF,0)
AH2 <= INDEX(Z:Z,$AG2)
AI2 <= INDEX(AA:AA,$AG2)
AJ2 <= MATCH(AD2,H:H,0)
AK2 <= MATCH(AH2,M:M,0)
AL2 <= IFERROR(AE2*AI2,"")
AM2 <= IFERROR(AL1+AM1,"")
AO2 <= IF(ROW()>$AN$1,NA(),MATCH(ROW()-1,$AM:$AM,1))
AP2 <= IF(ISNA(AO2),NA(),IF(AO2<>AO1,1,1+AP1))
AQ2 <= INDEX(AD:AD,AO2)
AR2 <= INDEX(G:G,INDEX(AJ:AJ,AO2)+MOD(AP2-1,INDEX($AE:$AE,AO2)))
AS2 <= INDEX(I:I,INDEX(AJ:AJ,AO2)+MOD(AP2-1,INDEX($AE:$AE,AO2)))
AT2 <= INDEX($N:$N,INDEX($AK:$AK,AO2)+INT((AP2-1)/INDEX($AE:$AE,AO2)))
AU2 <= INDEX($O:$O,INDEX($AK:$AK,AO2)+INT((AP2-1)/INDEX($AE:$AE,AO2)))
AV2 <= AR2
AW2 <= AT2
AX2 <= A2
AY2 <= B2
AZ2 <= IF(OR(ISNA($AV2),COUNTIFS($AV$1:$AV2,$AV2,$AW$1:$AW2,$AW2)>1,COUNTIFS($AX:$AX,$AV2,$AY:$AY,$AW2)>0),AZ1,1+AZ1)
BA2 <= MATCH(ROW(),$AZ:$AZ,0)
BB2 <= INDEX(AV:AV,$BA2)
BC2 <= INDEX(AW:AW,$BA2)
DiffSet(Project(EqJoin(tab=Relation(A,B,C),Reference(tab)),[2, 4]),Project(Reference(tab),[1, 2]))
----------------------------------------------------
Filter(GroupBy(Select(GenJoin(EqJoin(tab=Relation(A,B,C),Reference(tab)),Reference(tab),"And(#2=#6,#4=#7)"),[#2, #4, #3+#5, #8]),[1,2,4]).Min[3],"#3>#4")
CP-CS
A1 <= NA()
B1 <= NA()
C1 <= NA()
D1 <= COUNTA($B:$B)-COUNTIFS($B:$B,NA())
E1 <= IF(ISNA($B1),$D$1+1,IF(ISERR($B1),$D$1-COUNTIFS($B:$B,$B1)+COUNTIFS($B$1:$B1,$B1),COUNTIFS($B:$B,"<"&$B1)+COUNTIFS($B$1:$B1,$B1)))
F1 <= MATCH(ROW(),$E:$E,0)
G1 <= INDEX(A:A,$F1)
H1 <= INDEX(B:B,$F1)
I1 <= INDEX(C:C,$F1)
J1 <= COUNTA($A:$A)-COUNTIFS($A:$A,NA())
K1 <= IF(ISNA($A1),$J$1+1,IF(ISERR($A1),$J$1-COUNTIFS($A:$A,$A1)+COUNTIFS($A$1:$A1,$A1),COUNTIFS($A:$A,"<"&$A1)+COUNTIFS($A$1:$A1,$A1)))
L1 <= MATCH(ROW(),$K:$K,0)
M1 <= INDEX(A:A,$L1)
N1 <= INDEX(B:B,$L1)
O1 <= INDEX(C:C,$L1)
P1 <= IF(ISERROR(MATCH(H1,$M:$M,0)),NA(),H1)
Q1 <= IF(ISNA(P1),NA(),G1)
R1 <= IF(ISNA(P1),NA(),I1)
S1 <= IF(ISERROR(MATCH(M1,$H:$H,0)),NA(),M1)
T1 <= IF(ISNA(S1),NA(),N1)
U1 <= IF(ISNA(S1),NA(),O1)
V1 <= COUNTIFS($P$1:$P1,$P1)
W1 <= IF($V1=1,P1,NA())
X1 <= IF(ISNA($W1),NA(),COUNTIFS($P:$P,$P1)-COUNTIFS($P:$P,$P1,Q:Q,INDEX(0,-1),R:R,INDEX(0,-1)))
Y1 <= COUNTIFS($S$1:$S1,$S1)
Z1 <= IF($Y1=1,S1,NA())
AA1 <= IF(ISNA($Z1),NA(),COUNTIFS($S:$S,$S1)-COUNTIFS($S:$S,$S1,T:T,INDEX(0,-1),U:U,INDEX(0,-1)))
AB1 <= IF(ISNA($W1),0,1)
AC1 <= MATCH(ROW(),$AB:$AB,0)
AD1 <= INDEX(W:W,$AC1)
AE1 <= INDEX(X:X,$AC1)
AF1 <= IF(ISNA($Z1),0,1)
AG1 <= MATCH(ROW(),$AF:$AF,0)
AH1 <= INDEX(Z:Z,$AG1)
AI1 <= INDEX(AA:AA,$AG1)
AJ1 <= MATCH(AD1,H:H,0)
AK1 <= MATCH(AH1,M:M,0)
AL1 <= IFERROR(AE1*AI1,"")
AM1 <= 0
AN1 <= SUM(AL:AL)
AO1 <= IF(ROW()>$AN$1,NA(),MATCH(ROW()-1,$AM:$AM,1))
AP1 <= IF(ISNA(AO1),NA(),1)
AQ1 <= INDEX(AD:AD,AO1)
AR1 <= INDEX(G:G,INDEX(AJ:AJ,AO1)+MOD(AP1-1,INDEX($AE:$AE,AO1)))
AS1 <= INDEX(I:I,INDEX(AJ:AJ,AO1)+MOD(AP1-1,INDEX($AE:$AE,AO1)))
AT1 <= INDEX($N:$N,INDEX($AK:$AK,AO1)+INT((AP1-1)/INDEX($AE:$AE,AO1)))
AU1 <= INDEX($O:$O,INDEX($AK:$AK,AO1)+INT((AP1-1)/INDEX($AE:$AE,AO1)))
AV1 <= COUNTA(A:A)-COUNTIFS(A:A,NA())
AW1 <= COUNTA(AQ:AQ)-COUNTIFS(AQ:AQ,NA())
AX1 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AQ:AQ,QUOTIENT(ROW()-1,$AV$1)+1))
AY1 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AR:AR,QUOTIENT(ROW()-1,$AV$1)+1))
AZ1 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AS:AS,QUOTIENT(ROW()-1,$AV$1)+1))
BA1 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AT:AT,QUOTIENT(ROW()-1,$AV$1)+1))
BB1 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AU:AU,QUOTIENT(ROW()-1,$AV$1)+1))
BC1 <= IF(ISNA($AX1),NA(),INDEX(A:A,MOD(ROW()-1,$AV$1)+1))
BD1 <= IF(ISNA($AX1),NA(),INDEX(B:B,MOD(ROW()-1,$AV$1)+1))
BE1 <= IF(ISNA($AX1),NA(),INDEX(C:C,MOD(ROW()-1,$AV$1)+1))
BF1 <= And(AY1=BC1,BA1=BD1)
BG1 <= IF(ISERROR(BF1),NA(),IF(BF1, AX1, NA()))
BH1 <= IF(ISERROR(BF1),NA(),IF(BF1, AY1, NA()))
BI1 <= IF(ISERROR(BF1),NA(),IF(BF1, AZ1, NA()))
BJ1 <= IF(ISERROR(BF1),NA(),IF(BF1, BA1, NA()))
BK1 <= IF(ISERROR(BF1),NA(),IF(BF1, BB1, NA()))
BL1 <= IF(ISERROR(BF1),NA(),IF(BF1, BC1, NA()))
BM1 <= IF(ISERROR(BF1),NA(),IF(BF1, BD1, NA()))
BN1 <= IF(ISERROR(BF1),NA(),IF(BF1, BE1, NA()))
BO1 <= IF(ISNA($BG1),0,1)
BP1 <= MATCH(ROW(),$BO:$BO,0)
BQ1 <= INDEX(BG:BG,$BP1)
BR1 <= INDEX(BH:BH,$BP1)
BS1 <= INDEX(BI:BI,$BP1)
BT1 <= INDEX(BJ:BJ,$BP1)
BU1 <= INDEX(BK:BK,$BP1)
BV1 <= INDEX(BL:BL,$BP1)
BW1 <= INDEX(BM:BM,$BP1)
BX1 <= INDEX(BN:BN,$BP1)
BY1 <= IF(ISNA(BQ1), NA(), BR1)
BZ1 <= IF(ISNA(BQ1), NA(), BT1)
CA1 <= IF(ISNA(BQ1), NA(), BS1+BU1)
CB1 <= IF(ISNA(BQ1), NA(), BX1)
CC1 <= COUNTIFS($BY$1:$BY1,$BY1,$BZ$1:$BZ1,$BZ1,$CB$1:$CB1,$CB1)
CD1 <= IF(ISNA($BY1),NA(),IF(ISERR($CA1),COUNTIFS($BY:$BY,$BY1,$BZ:$BZ,$BZ1,$CB:$CB,$CB1)-COUNTIFS($BY:$BY,$BY1,$BZ:$BZ,$BZ1,$CB:$CB,$CB1,$CA:$CA,$CA1),COUNTIFS($BY:$BY,$BY1,$BZ:$BZ,$BZ1,$CB:$CB,$CB1,$CA:$CA,"<"&$CA1)))
CE1 <= IF($CC1=1,BY1,NA())
CF1 <= IF($CC1=1,BZ1,NA())
CG1 <= IF($CC1=1,CB1,NA())
CH1 <= IF(ISNA($CE1),NA(),AVERAGEIFS(CA:CA,$BY:$BY,$BY1,$BZ:$BZ,$BZ1,$CB:$CB,$CB1,$CD:CD,0))
CI1 <= CG1>CH1
CJ1 <= IF(ISERROR(CI1),NA(),IF(CI1, CE1, NA()))
CK1 <= IF(ISERROR(CI1),NA(),IF(CI1, CF1, NA()))
CL1 <= IF(ISERROR(CI1),NA(),IF(CI1, CG1, NA()))
CM1 <= IF(ISERROR(CI1),NA(),IF(CI1, CH1, NA()))
CN1 <= IF(ISNA($CJ1),0,1)
CO1 <= MATCH(ROW(),$CN:$CN,0)
CP1 <= INDEX(CJ:CJ,$CO1)
CQ1 <= INDEX(CK:CK,$CO1)
CR1 <= INDEX(CL:CL,$CO1)
CS1 <= INDEX(CM:CM,$CO1)
A2 <= NA()
B2 <= NA()
C2 <= NA()
E2 <= IF(ISNA($B2),$D$1+1,IF(ISERR($B2),$D$1-COUNTIFS($B:$B,$B2)+COUNTIFS($B$1:$B2,$B2),COUNTIFS($B:$B,"<"&$B2)+COUNTIFS($B$1:$B2,$B2)))
F2 <= MATCH(ROW(),$E:$E,0)
G2 <= INDEX(A:A,$F2)
H2 <= INDEX(B:B,$F2)
I2 <= INDEX(C:C,$F2)
K2 <= IF(ISNA($A2),$J$1+1,IF(ISERR($A2),$J$1-COUNTIFS($A:$A,$A2)+COUNTIFS($A$1:$A2,$A2),COUNTIFS($A:$A,"<"&$A2)+COUNTIFS($A$1:$A2,$A2)))
L2 <= MATCH(ROW(),$K:$K,0)
M2 <= INDEX(A:A,$L2)
N2 <= INDEX(B:B,$L2)
O2 <= INDEX(C:C,$L2)
P2 <= IF(ISERROR(MATCH(H2,$M:$M,0)),NA(),H2)
Q2 <= IF(ISNA(P2),NA(),G2)
R2 <= IF(ISNA(P2),NA(),I2)
S2 <= IF(ISERROR(MATCH(M2,$H:$H,0)),NA(),M2)
T2 <= IF(ISNA(S2),NA(),N2)
U2 <= IF(ISNA(S2),NA(),O2)
V2 <= COUNTIFS($P$1:$P2,$P2)
W2 <= IF($V2=1,P2,NA())
X2 <= IF(ISNA($W2),NA(),COUNTIFS($P:$P,$P2)-COUNTIFS($P:$P,$P2,Q:Q,INDEX(0,-1),R:R,INDEX(0,-1)))
Y2 <= COUNTIFS($S$1:$S2,$S2)
Z2 <= IF($Y2=1,S2,NA())
AA2 <= IF(ISNA($Z2),NA(),COUNTIFS($S:$S,$S2)-COUNTIFS($S:$S,$S2,T:T,INDEX(0,-1),U:U,INDEX(0,-1)))
AB2 <= IF(ISNA($W2),AB1,AB1+1)
AC2 <= MATCH(ROW(),$AB:$AB,0)
AD2 <= INDEX(W:W,$AC2)
AE2 <= INDEX(X:X,$AC2)
AF2 <= IF(ISNA($Z2),AF1,AF1+1)
AG2 <= MATCH(ROW(),$AF:$AF,0)
AH2 <= INDEX(Z:Z,$AG2)
AI2 <= INDEX(AA:AA,$AG2)
AJ2 <= MATCH(AD2,H:H,0)
AK2 <= MATCH(AH2,M:M,0)
AL2 <= IFERROR(AE2*AI2,"")
AM2 <= IFERROR(AL1+AM1,"")
AO2 <= IF(ROW()>$AN$1,NA(),MATCH(ROW()-1,$AM:$AM,1))
AP2 <= IF(ISNA(AO2),NA(),IF(AO2<>AO1,1,1+AP1))
AQ2 <= INDEX(AD:AD,AO2)
AR2 <= INDEX(G:G,INDEX(AJ:AJ,AO2)+MOD(AP2-1,INDEX($AE:$AE,AO2)))
AS2 <= INDEX(I:I,INDEX(AJ:AJ,AO2)+MOD(AP2-1,INDEX($AE:$AE,AO2)))
AT2 <= INDEX($N:$N,INDEX($AK:$AK,AO2)+INT((AP2-1)/INDEX($AE:$AE,AO2)))
AU2 <= INDEX($O:$O,INDEX($AK:$AK,AO2)+INT((AP2-1)/INDEX($AE:$AE,AO2)))
AX2 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AQ:AQ,QUOTIENT(ROW()-1,$AV$1)+1))
AY2 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AR:AR,QUOTIENT(ROW()-1,$AV$1)+1))
AZ2 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AS:AS,QUOTIENT(ROW()-1,$AV$1)+1))
BA2 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AT:AT,QUOTIENT(ROW()-1,$AV$1)+1))
BB2 <= IF(ROW()>$AV$1*$AW$1,NA(),INDEX(AU:AU,QUOTIENT(ROW()-1,$AV$1)+1))
BC2 <= IF(ISNA($AX2),NA(),INDEX(A:A,MOD(ROW()-1,$AV$1)+1))
BD2 <= IF(ISNA($AX2),NA(),INDEX(B:B,MOD(ROW()-1,$AV$1)+1))
BE2 <= IF(ISNA($AX2),NA(),INDEX(C:C,MOD(ROW()-1,$AV$1)+1))
BF2 <= And(AY2=BC2,BA2=BD2)
BG2 <= IF(ISERROR(BF2),NA(),IF(BF2, AX2, NA()))
BH2 <= IF(ISERROR(BF2),NA(),IF(BF2, AY2, NA()))
BI2 <= IF(ISERROR(BF2),NA(),IF(BF2, AZ2, NA()))
BJ2 <= IF(ISERROR(BF2),NA(),IF(BF2, BA2, NA()))
BK2 <= IF(ISERROR(BF2),NA(),IF(BF2, BB2, NA()))
BL2 <= IF(ISERROR(BF2),NA(),IF(BF2, BC2, NA()))
BM2 <= IF(ISERROR(BF2),NA(),IF(BF2, BD2, NA()))
BN2 <= IF(ISERROR(BF2),NA(),IF(BF2, BE2, NA()))
BO2 <= IF(ISNA($BG2),BO1,BO1+1)
BP2 <= MATCH(ROW(),$BO:$BO,0)
BQ2 <= INDEX(BG:BG,$BP2)
BR2 <= INDEX(BH:BH,$BP2)
BS2 <= INDEX(BI:BI,$BP2)
BT2 <= INDEX(BJ:BJ,$BP2)
BU2 <= INDEX(BK:BK,$BP2)
BV2 <= INDEX(BL:BL,$BP2)
BW2 <= INDEX(BM:BM,$BP2)
BX2 <= INDEX(BN:BN,$BP2)
BY2 <= IF(ISNA(BQ2), NA(), BR2)
BZ2 <= IF(ISNA(BQ2), NA(), BT2)
CA2 <= IF(ISNA(BQ2), NA(), BS2+BU2)
CB2 <= IF(ISNA(BQ2), NA(), BX2)
CC2 <= COUNTIFS($BY$1:$BY2,$BY2,$BZ$1:$BZ2,$BZ2,$CB$1:$CB2,$CB2)
CD2 <= IF(ISNA($BY2),NA(),IF(ISERR($CA2),COUNTIFS($BY:$BY,$BY2,$BZ:$BZ,$BZ2,$CB:$CB,$CB2)-COUNTIFS($BY:$BY,$BY2,$BZ:$BZ,$BZ2,$CB:$CB,$CB2,$CA:$CA,$CA2),COUNTIFS($BY:$BY,$BY2,$BZ:$BZ,$BZ2,$CB:$CB,$CB2,$CA:$CA,"<"&$CA2)))
CE2 <= IF($CC2=1,BY2,NA())
CF2 <= IF($CC2=1,BZ2,NA())
CG2 <= IF($CC2=1,CB2,NA())
CH2 <= IF(ISNA($CE2),NA(),AVERAGEIFS(CA:CA,$BY:$BY,$BY2,$BZ:$BZ,$BZ2,$CB:$CB,$CB2,$CD:CD,0))
CI2 <= CG2>CH2
CJ2 <= IF(ISERROR(CI2),NA(),IF(CI2, CE2, NA()))
CK2 <= IF(ISERROR(CI2),NA(),IF(CI2, CF2, NA()))
CL2 <= IF(ISERROR(CI2),NA(),IF(CI2, CG2, NA()))
CM2 <= IF(ISERROR(CI2),NA(),IF(CI2, CH2, NA()))
CN2 <= IF(ISNA($CJ2),CN1,CN1+1)
CO2 <= MATCH(ROW(),$CN:$CN,0)
CP2 <= INDEX(CJ:CJ,$CO2)
CQ2 <= INDEX(CK:CK,$CO2)
CR2 <= INDEX(CL:CL,$CO2)
CS2 <= INDEX(CM:CM,$CO2)
Filter(GroupBy(Select(GenJoin(EqJoin(tab=Relation(A,B,C),Reference(tab)),Reference(tab),"And(#2=#6,#4=#7)"),[#2, #4, #3+#5, #8]),[1,2,4]).Min[3],"#3>#4")
----------------------------------------------------