Dear gurus,
Here's my problem
I want to make an AxA matrix out of a data sheet.
My data is listing student ids and courses they are registered (Exhibit 1)
I want to generate a matrix where in rows and columns there are course codes and in cells there are number of students registered to course pairs (Exhibit 2). Ideally I'd like to list the students registered to course pairs but # of students will also serve the purpose.
It's similar to the correlation table in data analysis add-in but i do not want correlations but occurences in cells.
I searched the forum but could not find something similar. I'll be happy if you can link to an existing solution on the page.
Thank you very much in advance
Exhibit 1
student id Course code
176428 ARCH 101
176598 ARCH 101
178720 ARCH 101
183431 ARCH 101
184823 ARCH 101
185246 ARCH 101
185440 ARCH 101
186656 ARCH 101
188980 ARCH 101
190212 ARCH 101
190690 ARCH 101
201075 ARCH 101
208064 ARCH 101
209052 ARCH 101
212997 ARCH 101
214518 ARCH 101
218146 ARCH 101
227264 ARCH 101
234318 ARCH 101
234342 ARCH 101
235826 ARCH 101
239695 ARCH 101
240175 ARCH 101
241930 ARCH 101
242449 ARCH 101
252160 ARCH 101
254751 ARCH 101
256945 ARCH 101
258138 ARCH 101
259374 ARCH 101
259434 ARCH 101
266060 ARCH 101
266155 ARCH 101
266630 ARCH 101
269479 ARCH 101
274252 ARCH 101
278593 ARCH 101
280431 ARCH 101
305596 ARCH 102
306347 ARCH 102
310393 ARCH 102
310782 ARCH 102
318281 ARCH 102
321581 ARCH 102
325186 ARCH 102
328091 ARCH 102
328699 ARCH 102
330318 ARCH 102
343093 ARCH 102
357559 ARCH 102
357863 ARCH 102
358274 ARCH 102
358571 ARCH 102
359924 ARCH 102
364722 ARCH 102
369582 ARCH 102
371204 ARCH 102
372439 ARCH 102
375466 ARCH 102
376640 ARCH 102
377894 ARCH 102
380688 ARCH 102
387761 ARCH 102
398421 ARCH 102
402106 ARCH 102
402377 ARCH 102
407626 ARCH 102
408827 ARCH 102
412069 ARCH 102
413470 ARCH 102
417170 ARCH 102
420700 ARCH 102
422053 ARCH 102
426705 ARCH 102
427636 ARCH 102
430392 ARCH 102
431410 ARCH 102
441645 ARCH 102
443951 ARCH 102
455085 ARCH 102
412069 ARCH 201
413470 ARCH 201
417170 ARCH 201
420700 ARCH 201
422053 ARCH 201
426705 ARCH 201
427636 ARCH 201
430392 ARCH 201
431410 ARCH 201
441645 ARCH 201
443951 ARCH 201
455085 ARCH 201
121570 ARCH 202
124006 ARCH 202
126800 ARCH 202
126880 ARCH 202
126972 ARCH 202
127780 ARCH 202
132671 ARCH 202
134454 ARCH 202
136463 ARCH 202
137219 ARCH 202
137922 ARCH 202
138430 ARCH 202
138641 ARCH 202
139570 ARCH 202
139819 ARCH 202
139900 ARCH 202
143080 ARCH 202
143717 ARCH 202
148004 ARCH 202
149090 ARCH 202
Exhibit 2 (numbers in the cells were improvised
ARCH 101 ARCH 102 ARCH 201 ARCH 202
ARCH 101 0 1 3 6
ARCH 102 1 0 4 7
ARCH 202 3 4 0 2
ARCH 221 6 7 2 0
Bookmarks