Macro for counting interactions between individuals

BioPA

New Member
Joined
Oct 26, 2013
Messages
30
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:
FromTo
AB
CA
DA
CA
EB
......

<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)

FromToSum
AB1
AC0
AD0
AE0
BA0
BC0
BD0
BE0
CA2
CB0
CD0
.........

<tbody>
</tbody>

BioPaMr.Excel Members1000:):)

<tbody>
</tbody>
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
5CAD1000
6EBE0100
Sheet2
 
Upvote 0
Thank you JoeMo,

I have already created a matrix. Is it possible to have the sums on a 3rd column?
 
Upvote 0
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
4DAC2000AD0
5CAD1000AE0
6EBE0100BA0
7BC0
8BD0
9BE0
10CA2
11CB0
12CD0
13CE0
14DA1
15DB0
16DC0
17DE0
Sheet2
 
Upvote 0
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.
 
Upvote 0
Hi,

Here are two formulas to compute all combinations:


Excel 2010
ADE
1AAA
2BAB
3CAC
4DAD
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.
 
Upvote 0
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
4DAD
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))
 
Upvote 0
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
4DAD
5BA
6BB
7BC
8BD
9CA
10CB
11CC
12CD
13DA
14DB
15DC
16DD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top