Needing a macro for a count/correlation matrix

[maroonwhite2011]

I will start out by saying I am not a programmer. I am a researcher needing to create a 6000x6000 count matrix that would be set up similar to a correlation matrix. The current project I am working on is looking at the professional associations between roughly 6000 researchers that have published roughly 2000 articles in my field. The ultimate goal is a network analysis study, but in order to progress I have to build a matrix that is readable in that software.

Each article has its own row in Excel. Starting with column B, each author associated with that article is listed out--one per cell. The macro would need to be able to read each row, left to right, and record (in a separate sheet) the frequency count of each time one researcher has worked with another. Each article has a variable amount of authors, and therefore will need to recognize the need to jump to the next row when it reaches in empty cell. The macro would also need to update the numbers each time a particular association shows up again. Is there a relatively simple way for this do be done, or where I might be able to find instructions on writing this type of macro? Again, I have no experience and I have roughly one month to complete this project for a conference. Any assistance will be greatly appreciated.

[walruseggman]

This was kinda fun to write. I only tested it on 5 rows with 2-6 names per row, but it should work fine. Assuming the names are formatted the same way every time the appear. What I mean is that "John Smith" and "Smith, John" would be seen as two different people.

Please Login or Register  to view this content.