Macro for counting interactions between individuals

[BioPA]

Hi!

Needless to say how grateful i am to you for replying to my questions 6 months ago.

Could you give your lights again?

Assuming that i want to count how many times an individual says ''thanks'' to his colleagues. So, we have the table below:

From	To
A	B
C	A
D	A
C	A
E	B
...	...

<tbody>
</tbody>

Is it possible to have a macro that it will search the number of interactions from the two columns and will give us all the possible combinations with a new table such as below? (including the ''rude'' zero interactions)

From	To	Sum
A	B	1
A	C	0
A	D	0
A	E	0
B	A	0
B	C	0
B	D	0
B	E	0
C	A	2
C	B	0
C	D	0
...	...	...

<tbody>
</tbody>

BioPa

Mr.Excel Members

1000

<tbody>
</tbody>

 

[J.Ty.]

Yes, sure. Here it is for 3 combinations (well, being created automatically, the spreadsheet displays a certain degree of redundancy, repeating the same formulas several times).

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	A	3	3	A	A	3	9	A	A	A	27	1	1	A	A	A
2	B			A	B			A	A	B		10	4	A	B	A
3	C			A	C			A	A	C		19	7	A	C	A
4				B	A			A	B	A		2	10	B	A	A
5				B	B			A	B	B		11	13	B	B	A
6				B	C			A	B	C		20	16	B	C	A
7				C	A			A	C	A		3	19	C	A	A
8				C	B			A	C	B		12	22	C	B	A
9				C	C			A	C	C		21	25	C	C	A
10				#N/A	#N/A			B	A	A		4	2	A	A	B
11				#N/A	#N/A			B	A	B		13	5	A	B	B
12				#N/A	#N/A			B	A	C		22	8	A	C	B
13				#N/A	#N/A			B	B	A		5	11	B	A	B
14				#N/A	#N/A			B	B	B		14	14	B	B	B
15				#N/A	#N/A			B	B	C		23	17	B	C	B
16				#N/A	#N/A			B	C	A		6	20	C	A	B
17				#N/A	#N/A			B	C	B		15	23	C	B	B
18				#N/A	#N/A			B	C	C		24	26	C	C	B
19				#N/A	#N/A			C	A	A		7	3	A	A	C
20				#N/A	#N/A			C	A	B		16	6	A	B	C
21				#N/A	#N/A			C	A	C		25	9	A	C	C
22				#N/A	#N/A			C	B	A		8	12	B	A	C
23				#N/A	#N/A			C	B	B		17	15	B	B	C
24				#N/A	#N/A			C	B	C		26	18	B	C	C
25				#N/A	#N/A			C	C	A		9	21	C	A	C
26				#N/A	#N/A			C	C	B		18	24	C	B	C
27				#N/A	#N/A			C	C	C		27	27	C	C	C
RelAlg result

Cell Formulas
Range	Formula
B1	=COUNTA(A:A)-COUNTIFS(A:A,NA())
C1	=COUNTA(A:A)-COUNTIFS(A:A,NA())
D1	=IF(ROW()>$B$1*$C$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$B$1)+1))
D2	=IF(ROW()>$B$1*$C$1,NA(),INDEX(A:A,QUOTIENT(ROW()-1,$B$1)+1))
E1	=IF(ISNA($D1),NA(),INDEX(A:A,MOD(ROW()-1,$B$1)+1))
E2	=IF(ISNA($D2),NA(),INDEX(A:A,MOD(ROW()-1,$B$1)+1))
F1	=COUNTA(A:A)-COUNTIFS(A:A,NA())
G1	=COUNTA(D:D)-COUNTIFS(D:D,NA())
H1	=IF(ROW()>$F$1*$G$1,NA(),INDEX(D:D,QUOTIENT(ROW()-1,$F$1)+1))
H2	=IF(ROW()>$F$1*$G$1,NA(),INDEX(D:D,QUOTIENT(ROW()-1,$F$1)+1))
I1	=IF(ROW()>$F$1*$G$1,NA(),INDEX(E:E,QUOTIENT(ROW()-1,$F$1)+1))
I2	=IF(ROW()>$F$1*$G$1,NA(),INDEX(E:E,QUOTIENT(ROW()-1,$F$1)+1))
J1	=IF(ISNA($H1),NA(),INDEX(A:A,MOD(ROW()-1,$F$1)+1))
J2	=IF(ISNA($H2),NA(),INDEX(A:A,MOD(ROW()-1,$F$1)+1))
K1	=COUNTA($J:$J)-COUNTIFS($J:$J,NA())
L1	=IF(ISNA($J1),$K$1+1,IF(ISERR($J1),1+$K$1-COUNTIFS($J:$J,$J1),COUNTIFS($J:$J,"<"&$J1)+COUNTIFS($J$1:$J1,$J1)))
L2	=IF(ISNA($J2),$K$1+1,IF(ISERR($J2),1+$K$1-COUNTIFS($J:$J,$J2),COUNTIFS($J:$J,"<"&$J2)+COUNTIFS($J$1:$J2,$J2)))
M1	=MATCH(ROW(),$L:$L,0)
M2	=MATCH(ROW(),$L:$L,0)
N1	=INDEX(H:H,$M1)
N2	=INDEX(H:H,$M2)
O1	=INDEX(I:I,$M1)
O2	=INDEX(I:I,$M2)
P1	=INDEX(J:J,$M1)
P2	=INDEX(J:J,$M2)

 

[shobolos]

Thank you.

That's an amazing configuration of formulas.