Create matrix of correlated value based on combinations

    [FONT=&quot]Hi,


    I'm doing a PhD on music preference. The questionnaire, whereby people were asked to what artists they listened to last month, resulted in the data structure (with missing values '0') as shown in table 1 (attachment).


    What I want to do next, is convert this to an artist x artist matrix. This matrix should count how often (by how many respondents) an artist is mentioned in combination with another artist; see table 2 (attachment).[/FONT]

    [FONT=&quot]So we can read in the second table that Bach is mentioned together with Brahms a single time, that Mozart is mentioned together with U2 by two respondents, etc.


    Is there anyone here who can think of a formula, macro,... for achieving this?


    Thanks in advance :smile:


    Pieter[/FONT]

    Re: Create matrix of correlated value based on combinations


    Hello Pieter,


    Welcome to Ozgrid.


    Please note the change to your thread title as it more accurately describes your objective.


    As this is an Excel Forum, there is little value in uploading a Word documents.


    To get the most precise answer, it is best to upload a sample workbook (sensitive data scrubbed/removed) that contains a few manually created examples of the desired results and explicit details of all requirements. The sample worbook should exactly duplicate the structure of the real workbook and contain representative data of the same type.

    Re: Create matrix of correlated value based on combinations


    Thank you very very much! Will try to fathom the formula now, because copying the original data and pasting it in your sheet doesn't seem very fruitful.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login