Company network analysis in Excel :

[Artifax]

Hello everyone,

I have this million dollar question I can’t seem to figure out. I have to do some data analyzing for my boss but I am no Excel guru at all. So I was wondering if some smart guys here can help me out.

The problem: I have 2 rows in my excel file. Row 1 is a project number. Row 2 is a company linked to that project number. For every project number there are about (give or take) 4 companies who work within that project. So I have about 1000 different project numbers linked to about 4000 companies. Some companies have multiple projects going because they are bigger players in their field.

Here comes the tricky part: my boss asked me for find out which companies work together with whom the most. The goal is to find a top 100 or 150 of the companies that have a high degree of working with certain companies. We need to find out who the big players are, and who they tend to work with.
It’s basically an analysis of their network, but I can’t figure this out. I know there is a formula for that but I have no clue.

I have attached the file, if there is anyone who can give me advice on how to tackle this, I owe you big time!

Kind regards!

[alansidman]

Perhaps sorting them on Column B might give you what you are looking for?

[MarvinP]

Hi Artifax and welcome to the forum,

I've added two columns to your work to count the number of times the project appears and the number of times the company appears.

I then did a Pivot Table that is filtered and sorted so the most would come to the top left.

This pivot table is filtered and sorted and you should play with it a bit to see if it helps answer your question.

I'm thinking of another plan to map every time a company works with another company and tally those numbers. I think the result will be similar to the Pivot Table....

See the attached...

[MarvinP]

Hi Artifax,
I have a better answer (I think)

Some of the data was repeated on your sheet. That is a company was listed working on the same project twice or more times. I deleted all duplicates so these wouldn't be counted in the interaction of two companies working together.

Some Companies were only listed once so they weren't working with other companies and I deleted those records also.

Some projects were only listed once so I deleted those records also.

I was left with companies who were listed more than once and projects that were listed more than once. I think this better reflects which company was working with which other company.

I've done a similar Pivot Table on Blad2 (Sheet2) and I like this answer much better than the one on sheet1.

The most interaction between companies and projects is sorted to the top and left of the pivot table.

[Artifax]

Thank a lot guys, this has put me in the right direction! The things you have done are very helpful, it is almost what I had in mind. The only challenge I still have is to see who worked with who. So for instance if I click on a company name, I can see who exactly they worked with, company A+B+C+D etc...

But knowing how many project counts and the number of companies they work with is super good to have! Thanks for your effort and help! I really appreciate it. I have also asked a friend of mine to help me out, he told me he was going to help me with this issue but proposed to do this in Matlab because of the complicated formula. I will post the answer today so you guys can see the last piece of the puzzle too.

Kind regards!

[MarvinP]

Hi Artifax,

I decided to write some VBA on your problem after you suggested you needed some heavy math help. Here is what I do on Sheet2 now.
The macro builds all combinations of companies that work together by project number. Then I count the number of times they have worked together. The hard part is to build all pairs of company names. Simply run the macro by clicking the button and see if it all makes sense. I wonder what your helper will arrive at for the answer?

[Artifax]

What he did to tackle this was to make a matrix with top to bottom all the company names and did the same with the rows left ro right. After that he used the formula: =TRANSPOSE($A$3:$A$2403) etc. to calculate the different times they worked together. Eventually he got this result:

So I guess the question has been answered, thanks again everyone for helping me out!

Capture.jpg

BTW: I couldnt attach the excel file since it was about 16mb big. If you would like to have it, send me a pm!

[MarvinP]

Hi,

I think I got the answer in a little different way. I wrote some VBA to put all the companies side by side who worked together based on the project number. The company on the left is lower in the alphabet. Then I simply counted the number of times the two companies were on the same row. My spreadsheet in about 600k. Did you look at it to see if we agree??