Bottom line is there's just too much data for standard functions to handle efficiently. Moreover, it's not just the size of the dataset that affects efficiency, but also the total number of unique courses. The final output will be a grid consisting of n^2 cells. If you have 100 unique courses, for example, there'll be a total of 10,000 iterations/calculations. With 1,000 unique courses, it'll be 1,000,000.
As a test, I generated a randomized dataset consisting of 120,000 records, with 50 different areas, 6,000 unique ID's, levels from 1 to 10, and 100 unique courses. COUNTIFS on its own did not output the results in a reasonable amount of time (I waited 3 minutes, then gave up and force-closed Excel). However, the new PIVOTBY function successfully output the same results in approx. 6 seconds. After re-writing my original formula using PIVOTBY instead of COUNTIFS (as well as a binary tree method instead of MAKEARRAY), I was able to output the final results for the entire dataset in just under 1 minute (58 seconds). It's not ideal, I know, but when filters are applied, the total calculation time can be significantly reduced to less than 5 or 10 seconds, depending on the amount of data returned by the filter criteria.
If you have access to the new PIVOTBY function and want to give it a try, start by defining the following 4 custom LAMBDA functions in Name Manager:
COUNTPAIRS
Excel Formula:
=LAMBDA(field1,field2,[include],
LET(
a, DROP(PIVOTBY(field1,field2,field1,ROWS,1,0,,0,,include),,1),
u, TAKE(a,1),
d, DROP(a,1),
x, IF(d="",0,d),
y, TRANSPOSE(x),
m, BiROW(y,LAMBDA(r,BiCOL(x,LAMBDA(c,MMULT(r,c))))),
t, MMULT(m,IF({1},1,TOCOL(u))),
VSTACK(HSTACK("Pairs",u,"Total"),HSTACK(TOCOL(u),m,t),HSTACK("Total",TOROW(t),SUM(t)))
)
)
INCLλ
Excel Formula:
=LAMBDA(field,criteria,[opt],
IF(criteria="",IF({1},1,field),IF(opt=1,field>=criteria,IF(opt=2,field<=criteria,field=criteria)))
)
BiROW
Excel Formula:
=LAMBDA(array,function,
IF(
ROWS(array)=1,
function(array),
VSTACK(
BiROW(TAKE(array,ROWS(array)/2),function),
BiROW(DROP(array,ROWS(array)/2),function)
)
)
)
BiCOL
Excel Formula:
=LAMBDA(array,function,
IF(
COLUMNS(array)=1,
function(array),
HSTACK(
BiCOL(TAKE(array,,COLUMNS(array)/2),function),
BiCOL(DROP(array,,COLUMNS(array)/2),function)
)
)
)
View attachment 117928
Once defined,
COUNTPAIRS can be combined with
INCLλ to set the filter criteria using cells in the worksheet. For example:
Excel Formula:
=COUNTPAIRS(B1:B120001,D1:D120001,INCLλ(A1:A120001,H1,1)*INCLλ(A1:A120001,J1,2)*INCLλ(C1:C120001,H2)*INCLλ(E1:E120001,H3))
View attachment 117929
Please note, you don't have to use the
INCLλ function if you don't want to... you could also just filter by any standard criteria method (e.g. ISNUMBER-XMATCH to filter by a list of ID's).
Also note, due to the way I wrote the
COUNTPAIRS function (by setting the optional
[field_headers] argument of PIVOTBY to
1 - Yes but don't show), all of the referenced fields should include the header row. You can change this to
0 - No, if desired, so that you would only need to reference the data range for each field.
The example shown above took approx. 6 seconds to return the results. This is the best I could come up with for the problem at hand. Perhaps others may know of a better solution. Python might be a possibility, but that's currently outside of my knowledgebase.
