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>

 

[JoeMo]

You don't need a macro for that. Here's one way to do it using worksheet formulas. Copy the formula in E2 across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	From	To		From/To	A	B	C	D	E
2	A	B		A		1	0	0	0
3	C	A		B	0		0	0	0
4	D	A		C	2	0		0	0
5	C	A		D	1	0	0		0
6	E	B		E	0	1	0	0
Sheet2

 

[BioPA]

Thank you JoeMo,

I have already created a matrix. Is it possible to have the sums on a 3rd column?

 

[JoeMo]

Thank you JoeMo,

I have already created a matrix. Is it possible to have the sums on a 3rd column?

Sure (below), but that seems pointless if you have the matrix.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	From	To		From/To	A	B	C	D	E		From	To	Sum
2	A	B		A		1	0	0	0		A	B	1
3	C	A		B	0		0	0	0		A	C	0
4	D	A		C	2	0		0	0		A	D	0
5	C	A		D	1	0	0		0		A	E	0
6	E	B		E	0	1	0	0			B	A	0
7											B	C	0
8											B	D	0
9											B	E	0
10											C	A	2
11											C	B	0
12											C	D	0
13											C	E	0
14											D	A	1
15											D	B	0
16											D	C	0
17											D	E	0
Sheet2

 

[V_Malkoti]

ADVERTISEMENT

Won't a pivot table do the same thing?

 

[BioPA]

Is there any automatic solution for this?

I mean that i don't know a priori neither how many interactions i will have (Rows of From & To) nor their names. So, i have to spend time on manual entry of the combinations of columns K & L. So what i am asking is the columns K, L & M to be automatically calculated /updated based on the columns A & B.

PS. I would like to mention that i contacted with MVPs and they encouraged me to ask further for what i need to do, despite my offer to pay them for their job. I assume that this is a strong proof how much the staff of this website is highly motivated and willing to support -newbie users like me- at no cost! I am really surprised and grateful to you.

 

[J.Ty.]

ADVERTISEMENT

Hi,

Here are two formulas to compute all combinations:

Excel 2010
	A	D	E
1	A	A	A
2	B	A	B
3	C	A	C
4	D	A	D
5		B	A
6		B	B
7		B	C
8		B	D
9		C	A
10		C	B
11		C	C
12		C	D
13		D	A
14		D	B
15		D	C
16		D	D
RelAlg result

Cell Formulas
Range	Formula
D1	=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))

J.Ty.

 

[BioPA]

#value

 

[J.Ty.]

Sorry!
This was an automatically generated spreadsheet and I forgot that it had hidden columns.
Here is a correction:

Excel 2010
	A	B	C	D	E
1	A	4	4	A	A
2	B			A	B
3	C			A	C
4	D			A	D
5				B	A
6				B	B
7				B	C
8				B	D
9				C	A
10				C	B
11				C	C
12				C	D
13				D	A
14				D	B
15				D	C
16				D	D
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))
E1	=IF(ISNA($D1),NA(),INDEX(A:A,MOD(ROW()-1,$B$1)+1))

 

[shobolos]

Sorry!
This was an automatically generated spreadsheet and I forgot that it had hidden columns.
Here is a correction:

Excel 2010

	A	B	C	D	E
1	A	4	4	A	A
2	B			A	B
3	C			A	C
4	D			A	D
5				B	A
6				B	B
7				B	C
8				B	D
9				C	A
10				C	B
11				C	C
12				C	D
13				D	A
14				D	B
15				D	C
16				D	D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

RelAlg result​

Worksheet Formulas Cell 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)) E1 =IF(ISNA($D1),NA(),INDEX(A:A,MOD(ROW()-1,$B$1)+1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Is it possible to formulate this with 3 combinations instead of just 2?