how do i make a co-ocurrence matrix?

[niekvdbogert]

Hello can anyone please help me with the following?

I want to make a co-occurrence matrix:
i have 100 lists of 10 strings long. what I want to know is how many times duo's of strings co-occur on a list.
so the list of person 1 may look like:
-aardvark
-bee
-cicade
-deer
-earthworm
-flatworm
-giraffe
etc

and the list of person 2 may look like:
-cicade
-giraffe
-horse
-karibo
-lungfish
etc.

In this example cicade and giraffe have a co-occurrence of 2 (they are BOTH present in 2 lists).

What functions/ formulae does one use to make a co-occurrence matrix where all co-occurrences are calculated for all the strings in the 100 lists?

I thought a sum-product, or an IF statement with an AND function, might do the trick. But I haven't been successful yet.

Many thanks i advance

Niek

[stanleydgromjr]

niekvdbogert,

Welcome to the Excel Forum.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, click on the New Post button, then scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.