Google sheets calculation to determine frequency of matching value for each pair of items in the same cloumn

Question

My data has two columns, A and B. Value A is a simple number going from 1,2,3 etc. Each value repeats 6 times so 1,1,1,1,1,1,2,2,2,2,2,2, etc. These represent sport teams of six players that were on the same team on a given day (So basically team 1, team 2 etc.) New teams are added every week, so the data is dynamic.

Column B has string values, (names of players, let's say 20 of them) that vary randomly, however each value only shows up (maximum) once with the same "A" Value. (As one player can not be on the same team on the same day in two instances.) Take the following example:

I'd like to determine (in a matrix style) for each pairs of players in column B, how many times they had been on the same team, so when they had the same "A" value.

A	B
(Team)	(Players)
1	John
1	Jack
1	Loue
1	Daniel
1	Mark
1	Lewis
2	Henry
2	Francis
2	George
2	Martin
2	David
2	Peter
3	John
3	Jeff
3	Andrew
3	David
3	Peter
3	Lewis
4	Henry
4	Francis
4	George
4	Martin
4	Daniel
4	Mark

I would like an outpoot table that calculates the above.

     John        Jack     David       Peter   etc.
John   x           2        1           0
Jack   2           X        3           4
David  1           3        X           2
Peter  0           4        2           X
etc.

Seems like basic countif or counta type of functions are limited to fixed ranges and even then, matching for values in the same column with strings was not doable. And still, I would need to iterate those lookups for each pair for each team, and also make it dynamic as new team setups are added.

Answer 1

Here you have an option to create the table. It generates both headers and goes through them with MAKEARRAY, filtering and counting both values:

=LET(names,TOCOL(UNIQUE(B2:B),1),amount,COUNTA(names),
{"TABLE",TRANSPOSE(names);names,MAKEARRAY(amount,amount,LAMBDA(r,c,IF(INDEX(names,c)=INDEX(names,r),"x",
COUNTA(IFNA(FILTER(A:A,B:B=INDEX(names,r),IFNA(XMATCH(A:A,IFNA(FILTER(A:A,B:B=INDEX(names,c)))))))))))})