I have a recurring task in Excel that I have solved in a quite complicated manner where sevaral steps have to be redone if there are changes to the data. I feel there should be an easier and more robust way. I have a table with two columns, x (students) and y (classes). Each value of x and y usually occurs several times in the table, but each combination (x, y) usually only once. (I.e., each student follows a number of classes, and each class has several students.) I want to create a table with a column AND a row for each y-value, where the entry at (y1, y2) shows how many x'es are paired with both y1 and y2, i.e., for how many x-values does the rows (x, y1) and (x, y2) both exist in the original table. (I.e., how many students are in both classes. At the main diagonal (y, y), we will have the number of students in each class.) My solution is to create a table of all y-values, and a table of all x-values where I add a column for each y-value with "true" or "false" indicating whether (x, y) is in the original table or not, and finally use COUNTIFS to create the table I want. Any ideas? (I could automate my steps with VBA, but I'd rather avoid macors here.)
