2

I have a data on the list of directors from different companies. Directors from one company meet at the same board of directors. Moreover, I also have a data how many times these directors were in the same board of directors. I have to create an adjacency matrix consisting from these directors. Nodes represent how many times 2 directors were in the same board of directors (i.e. if A and B are from company 1, and there were 11 meetings in this company, hence it must be 11 on at the intersection of A and B and if A and B from different boards of directors (from different companies), then it must be 0 at the intersection.

I have created this matrix in Excel successfully via command 

=IF(VLOOKUP($E2;$A$1:$C$27;2;0)=(VLOOKUP(F$1;$A$1:$C$27;2;0));$C2;0)

However, the main problem is that two or more directors may meet in more than one board of directors (one company). In this case the total number of meetings must be added together. For example, if A and B meet together in company 1 for 11 times and in company 3 for 4 times, then it must be 15 at the intersection and, unfortunately, I can't understand how to realize it. I've searched for similar problems and I didn't found any cases where the data in original data was repeated. I have no idea, whether it is possible to realize it in Excel or should I apply another software (R or something else)?

enter image description here

zx8754
  • 52,746
  • 12
  • 114
  • 209
Vlad
  • 21
  • 2
  • 1
    Can you paste, `Name, CompanyID, Meetings` table as a text to your post? [How to make a great R reproducible example?](http://stackoverflow.com/questions/5963269) – zx8754 Sep 16 '16 at 09:12

1 Answers1

0

See if this array formula works for you:-

=SUM(ISNUMBER(MATCH(IF($A$2:$A$27=F$1,$B$2:$B$27,"+"),IF($A$2:$A$27=$E2,$B$2:$B$27,"-"),0))*$C$2:$C$27)

Must be entered with CtrlShiftEnter

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37