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

ea2146

New Member
Joined
Apr 9, 2012
Messages
30
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:

DATESPEAKERRECIPIENTSUM_SPEAKING_TO_SAME_RECIPIENTSUM_SAME_PAIRING
6/20/2003JohnJessica00
6/21/2003JohnBill00
6/22/2003JohnSusan00
6/23/2003JohnBill11
6/24/2003BillSusan00
6/25/2003BillJohn02
6/26/2003BillSusan11
6/27/2003JessicaJohn01
6/28/2003JessicaJohn12
6/29/2003JessicaSusan00
6/30/2003JessicaBill00
7/1/2003JohnSusan11
7/2/2003SusanJessica01
7/3/2003SusanJessica12
7/4/2003SusanBill02
7/5/2003BillSusan23
7/6/2003JohnJessica13
7/7/2003SusanJohn02
7/8/2003BillJohn13
7/9/2003BillJohn24
7/10/2003JessicaBill11

<tbody>
</tbody>
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Last edited:
Upvote 0
This is brilliant!!! It works perfectly. Thanks so much for taking the time to help me figure this out. I really appreciate it, jmacleary!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
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