# Macro for counting interactions between individuals

#### BioPA

##### New Member
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>

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### JoeMo

##### MrExcel MVP
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
ABCDEFGHI
1FromToFrom/ToABCDE
2ABA 1000
3CAB0000
4DAC2000
6EBE0100
Sheet2

#### BioPA

##### New Member
Thank you JoeMo,

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

#### JoeMo

##### MrExcel MVP
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
ABCDEFGHIJKLM
1FromToFrom/ToABCDEFromToSum
2ABA 1000AB1
3CAB0000AC0
6EBE0100BA0
7BC0
8BD0
9BE0
10CA2
11CB0
12CD0
13CE0
14DA1
15DB0
16DC0
17DE0
Sheet2

#### V_Malkoti

##### Well-known Member
Won't a pivot table do the same thing?

#### BioPA

##### New Member
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.

##### Well-known Member
Hi,

Here are two formulas to compute all combinations:

Excel 2010
1AAA
2BAB
3CAC
5BA
6BB
7BC
8BD
9CA
10CB
11CC
12CD
13DA
14DB
15DC
16DD
RelAlg result
Cell Formulas
RangeFormula
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.

#value

#### J.Ty.

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

Excel 2010
ABCDE
1A44AA
2BAB
3CAC
5BA
6BB
7BC
8BD
9CA
10CB
11CC
12CD
13DA
14DB
15DC
16DD
RelAlg result
Cell Formulas
RangeFormula
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

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

Excel 2010
ABCDE
1A44AA
2BAB
3CAC
5BA
6BB
7BC
8BD
9CA
10CB
11CC
12CD
13DA
14DB
15DC
16DD

</tbody>
RelAlg result

Worksheet Formulas
CellFormula
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))

</tbody>

<tbody>
</tbody>

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

Replies
17
Views
472
Replies
2
Views
183
Replies
7
Views
194
Replies
1
Views
110
Replies
6
Views
162

1,195,650
Messages
6,010,920
Members
441,572
Latest member
keobongda8812

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back