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="]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: