Lambda to count pairs and output array based on criteria

[Phantom1]

I have the following sample data

ID	Course
1000	a
1000	b
1000	c
1001	a
1001	b
1001	d
1002	a
1002	c
1002	d
1003	a
1003	e
1003	f
1004	a
1004	c
1004	f
1005	a
1005	d
1005	f

and need to output with a single formula the following result

	a	b	c	d	e	f	Total
a	6	2	3	3	1	3	18
b	2	2	1	1	0	0	6
c	3	1	3	1	0	1	9
d	3	1	1	3	0	1	9
e	1	0	0	0	1	1	3
f	3	0	1	1	1	3	9
Total	18	6	9	9	3	9	54

which is the count of persons for each pair of courses.

I tried the following formula
=LET(
ids, A2:A19,
courses, B2:B19,
uniqueCourses, UNIQUE(courses),
courseRows, INDEX(uniqueCourses, SEQUENCE(ROWS(uniqueCourses), 1)),
courseCols, TRANSPOSE(courseRows),
countMat, MAP(courseRows, courseCols, LAMBDA(cRow,cCol,
SUM( (courses=cRow) * (courses=cCol)))),
countMat)
but got a bunch of #N/A errors.


Any help, suggestions or new formula, will be appreciate.
Thanks in advance

 

[Fluff]

When using MAP with more than one array, they need to be the same size & shape.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Course
2	1000	a			a	b	c	d	e	f
3	1000	b		a	6	2	3	3	1	3
4	1000	c		b	2	2	1	1	0	0
5	1001	a		c	3	1	3	1	0	1
6	1001	b		d	3	1	1	3	0	1
7	1001	d		e	1	0	0	0	1	1
8	1002	a		f	3	0	1	1	1	3
9	1002	c
10	1002	d
11	1003	a
12	1003	e
13	1003	f
14	1004	a
15	1004	c
16	1004	f
17	1005	a
18	1005	d
19	1005	f
Sheet6

Cell Formulas
Range	Formula
D2:J8	D2	=LET(Data,FILTER(A2:B100,A2:A100<>""),Id,INDEX(Data,,1),Course,INDEX(Data,,2),u,UNIQUE(Course),REDUCE(HSTACK("",TOROW(u)),u,LAMBDA(x,y,VSTACK(x,HSTACK(y,DROP(REDUCE("",u,LAMBDA(a,b,LET(f,FILTER(Id,(Course=y)+(Course=b)),HSTACK(a,IF(b=y,ROWS(f),(ROWS(f)-ROWS(UNIQUE(f,,1)))/2))))),,1))))))
Dynamic array formulas.

 

[hagia_sofia]

Another option (Fluff's is definitely better though):

=LET(
ID,A2:A19,
courses,B2:B19,
u_c,UNIQUE(courses),
u_i,UNIQUE(ID),
m,u_c&" | "&TRANSPOSE(u_c),
n,TOCOL(DROP(REDUCE("",u_i,LAMBDA(x,y,VSTACK(x,FILTER(courses,ID=y)&" | "&TRANSPOSE(FILTER(courses,ID=y))))),1)),
count,WRAPROWS(MAP(TOCOL(m),LAMBDA(x,SUM(--(n=x)))),COLUMNS(m)),
t_1,VSTACK(count,BYCOL(count,SUM)),
t_2,HSTACK(t_1,BYROW(t_1,SUM)),
l,HSTACK(VSTACK(u_c,"Total"),t_2),
VSTACK(HSTACK("",TRANSPOSE(u_c),"Total"),l))

 

[Peter_SSs]

Another way

24 10 09.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Course
2	1000	a			a	b	c	d	e	f
3	1000	b		a	6	2	3	3	1	3
4	1000	c		b	2	2	1	1	0	0
5	1001	a		c	3	1	3	1	0	1
6	1001	b		d	3	1	1	3	0	1
7	1001	d		e	1	0	0	0	1	1
8	1002	a		f	3	0	1	1	1	3
9	1002	c
10	1002	d
11	1003	a
12	1003	e
13	1003	f
14	1004	a
15	1004	c
16	1004	f
17	1005	a
18	1005	d
19	1005	f
Courses

Cell Formulas
Range	Formula
D2:J8	D2	=LET(b,B2:B19,u,UNIQUE(b),w,ROWS(u)+1,MAKEARRAY(w,w,LAMBDA(r,c,LET(x,INDEX(u,r-1),y,INDEX(u,c-1),f,FILTER(A2:A19,(b=x)+(b=y)),IF(r=1,IF(c=1,"",y),IF(c=1,x,IF(r=c,COUNTIF(b,x),COUNT(UNIQUE(FILTER(f,XMATCH(f,f)<XMATCH(f,f,,-1)))))))))))
Dynamic array formulas.

 

[Domenic]

These are all very interesting, but I see only the solution offered by @hagia_sofia includes the totals, as per the original post. In any case, here's my attempt at a solution (without including the totals, though ) ....

phantom.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Course
2	1000	a			a	b	c	d	e	f
3	1000	b		a	6	2	3	3	1	3
4	1000	c		b	2	2	1	1	0	0
5	1001	a		c	3	1	3	1	0	1
6	1001	b		d	3	1	1	3	0	1
7	1001	d		e	1	0	0	0	1	1
8	1002	a		f	3	0	1	1	1	3
9	1002	c
10	1002	d
11	1003	a
12	1003	e
13	1003	f
14	1004	a
15	1004	c
16	1004	f
17	1005	a
18	1005	d
19	1005	f
20
21
22
Sheet1

Cell Formulas
Range	Formula
D2:J8	D2	=LET(rngID,A2:A19,rngCourse,B2:B19,uniqueCourses,SORT(UNIQUE(rngCourse)),array,MAKEARRAY(COUNTA(uniqueCourses),COUNTA(uniqueCourses),LAMBDA(x,y,SUM(IF(ISNUMBER(MATCH(IF(rngCourse=INDEX(uniqueCourses,x),rngID,"@"),IF(rngCourse=INDEX(uniqueCourses,y),rngID,"#"),0)),1)))),table,HSTACK(uniqueCourses,array),table2,VSTACK(HSTACK("",TOROW(uniqueCourses)),table),table2)
Dynamic array formulas.

 

[Peter_SSs]

but I see only the solution offered by @hagia_sofia includes the totals, as per the original post.

Good catch
Must have had these on

24 10 09.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Course
2	1000	a			a	b	c	d	e	f	Total
3	1000	b		a	6	2	3	3	1	3	18
4	1000	c		b	2	2	1	1	0	0	6
5	1001	a		c	3	1	3	1	0	1	9
6	1001	b		d	3	1	1	3	0	1	9
7	1001	d		e	1	0	0	0	1	1	3
8	1002	a		f	3	0	1	1	1	3	9
9	1002	c		Total	18	6	9	9	3	9	54
10	1002	d
11	1003	a
12	1003	e
13	1003	f
14	1004	a
15	1004	c
16	1004	f
17	1005	a
18	1005	d
19	1005	f
Courses (2)

Cell Formulas
Range	Formula
D2:K9	D2	=LET(b,B2:B19,u,UNIQUE(b),w,ROWS(u)+1,t,"Total",m,MAKEARRAY(w,w,LAMBDA(r,c,LET(x,INDEX(u,r-1),y,INDEX(u,c-1),f,FILTER(A2:A19,(b=x)+(b=y)), IF(r=1,IF(c=1,"",y),IF(c=1,x,IF(r=c,COUNTIF(b,x),COUNT(UNIQUE(FILTER(f,XMATCH(f,f)<XMATCH(f,f,,-1)))))))))),MAKEARRAY(w+1,w+1, LAMBDA(g,h,IF(g=w+1,IF(h=1,t,IF(h=w+1,SUM(m),SUM(INDEX(m,0,h)))),IF(h=w+1,IF(g=1,t,SUM(INDEX(m,g,0))),INDEX(m,g,h))))))
Dynamic array formulas.

 

[djclements]

Just for fun, here's one more option to add to the mix:

=LET(
    id, A2:A1000,
    cr, B2:B1000,
    u, UNIQUE(TOROW(cr,1),1),
    n, COLUMNS(u),
    k, COUNTIFS(id,UNIQUE(TOCOL(id,1)),cr,u),
    m, MAKEARRAY(n,n,LAMBDA(i,j,SUM(CHOOSECOLS(k,i)*CHOOSECOLS(k,j)))),
    t, MMULT(m,EXPAND(1,n,,1)),
    VSTACK(HSTACK("Course",u,"Total"),HSTACK(TOCOL(u),m,t),HSTACK("Total",TOROW(t),SUM(t)))
)

It's also entirely possible to use MAP by broadcasting the unique course list both down and across in two separate arrays. As a demonstration, consider Fluff's solution:

=LET(
    data, FILTER(A2:B1000,A2:A1000<>""),
    id, INDEX(data,,1),
    cr, INDEX(data,,2),
    i, UNIQUE(cr),
    j, TOROW(i),
    m, MAP(IF({1},i,j),IF({1},j,i),LAMBDA(a,b,LET(f,FILTER(id,(cr=a)+(cr=b)),IF(a=b,ROWS(f),(ROWS(f)-ROWS(UNIQUE(f,,1)))/2)))),
    t, MMULT(m,IF({1},1,i)),
    VSTACK(HSTACK("Course",j,"Total"),HSTACK(i,m,t),HSTACK("Total",TOROW(t),SUM(t)))
)

Cheers!

 

[Phantom1]

Thank you all very much.
Quick responses and accurately.
All of your answers worked perfectly.
Still can decide which solution is more elegant and straight forward though. All of them are definitely above my skills.

 

[Peter_SSs]

You are welcome. Glad we could help.
(Good luck choosing but the first one in post 7 looks pretty good to me)

 

[Phantom1]

Because i have my data in a table that has more than 100 000 rows and 30 columns the functions above take a lot time to calculate. Is there any workthrough?
Also can we add an optional argument in lambda to filter by criteria on values of ids or other columns before counting? This will be very handy as it minimizes the data to process.
Thanks again