Creating a Cross Reference table/Matrix

[Guest]

I have data on individuals who worked on a project. The table looks like:

Bob Mary Jon Steve Susan Tyler
Project1 1 1 0 0 0 0
Project2 0 0 1 1 1 1
Project3 1 0 1 0 1 1

I want to create a matrix that tells me how many times each individual has
worked with each other. So from the list above I would get

Bob Mary Jon Steve Susan Tyler
Bob X 1 1 0 1 1
Mary X X 0 0 0 0
Jon X X X 1 2 2
Steve X X X X 1 1
Susan X X X X X 2
Tyler X X X X X X


The X's are actually a mirror image of the upper triangle.

Is there an way to do this?

 

[T. Valko]

Here's a small sample file.

matrix.xls 15kb

http://cjoint.com/?fjjdAApqYQ

The formula in B10 was copied across to G10 then down to row 15.

Biff

 

[T. Valko]

I was just thinking......

All those Xs make it harder to read. I think I'd use conditional formatting
to hide the Xs and then use a light gray fill.

Biff