Need two different formulas to sum previous number of dyadic interactions across large dataset

[ea2146]

Hello,

I need help creating two different formulas:

1) First, I need a formula that calculates the values shown in column D below (i.e., "SUM_SPEAKING_TO_SAME_RECIPIENT") which is the sum of the previous exact same speaker-recipient pairings. So in row 2, the value is 0 because John had not previously been the speaker to Jessica. However, in row 5, the value is 1 because this is the second time that John has been the speaker to Bill.

2) Second, I need a formula that calculates the values shown in column E below (i.e., "SUM_SAME_PAIRING") which is the sum of the previous times the same two people have interacted regardless of who was the speaker and who was the recipient. So in row 2, the value is 0 because John and Jessica had not previously been interacted (in any capacity). However, in row 7, the value is 2 because Bill and John had previously had two interactions together (both with John as the speaker and Bill as the recipient).

I would be extremely grateful to anyone who can help. Thanks so much!!



Desired output is displayed below:

<tbody>
[TD="class: xl65"]DATE[/TD]
[TD="class: xl66, width: 92"]SPEAKER[/TD]
[TD="class: xl66, width: 92"]RECIPIENT[/TD]
[TD="class: xl66, width: 316"]SUM_SPEAKING_TO_SAME_RECIPIENT[/TD]
[TD="class: xl66, width: 138"]SUM_SAME_PAIRING[/TD]

[TD="class: xl65"]6/20/2003[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl65"]6/21/2003[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl65"]6/22/2003[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl65"]6/23/2003[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl65"]6/24/2003[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl65"]6/25/2003[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]2[/TD]

[TD="class: xl65"]6/26/2003[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl65"]6/27/2003[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl65"]6/28/2003[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]2[/TD]

[TD="class: xl65"]6/29/2003[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl65"]6/30/2003[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]0[/TD]

[TD="class: xl65"]7/1/2003[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl65"]7/2/2003[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl65"]7/3/2003[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]2[/TD]

[TD="class: xl65"]7/4/2003[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]2[/TD]

[TD="class: xl65"]7/5/2003[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]3[/TD]

[TD="class: xl65"]7/6/2003[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]3[/TD]

[TD="class: xl65"]7/7/2003[/TD]
[TD="class: xl66"]Susan[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]2[/TD]

[TD="class: xl65"]7/8/2003[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]3[/TD]

[TD="class: xl65"]7/9/2003[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]4[/TD]

[TD="class: xl65"]7/10/2003[/TD]
[TD="class: xl66"]Jessica[/TD]
[TD="class: xl66"]Bill[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]

</tbody>

 

[jmacleary]

Hi there. If you can create 2 'composite' columns, then this approach works. I am assuming your data starts at A1.
In F2, place this formula: =B2&C2
In G2, place this formula: =C2&B2
In H2, place this formula: =COUNTIF(F$1:F1,F2)
In I2, place this formula: =COUNTIF(F$1:G1,G2)

Column H has your SUM_SPEAKING_TO_SAME_RECIPIENT and column I has your SUM_SAME_PAIRING

I left the data 'as is' , once you add these columns, drag the formulae down. You can then compare the results to make sure you are happy. If you then want to tidy it up, delete the D and E columns and hide the new D and E columns. The display will then be as you presented it, with 4 extra columns to achieve the result.

If you can't create extra columns, then this will obviously not work and you will need someone better qualified than me to help.

 

[ea2146]

This is brilliant!!! It works perfectly. Thanks so much for taking the time to help me figure this out. I really appreciate it, jmacleary!