+ Reply to Thread
Results 1 to 17 of 17

how to make an AxA matrix

  1. #1
    Registered User
    Join Date
    04-10-2017
    Location
    Budapest
    MS-Off Ver
    excel 2010
    Posts
    6

    how to make an AxA matrix

    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

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: how to make an AxA matrix

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to make an AxA matrix

    Not sure why you want a matrix that shows courses across the top and side, without somehow pulling in/showing the student number?

    I took this approach...
    D
    E
    F
    G
    H
    1
    ARCH 101 ARCH 102 ARCH 201 ARCH 202
    2
    176428
    1
    0
    0
    0
    3
    176598
    1
    0
    0
    0
    4
    178720
    1
    0
    0
    0
    5
    183431
    1
    0
    0
    0
    6
    184823
    1
    0
    0
    0

    E2=COUNTIFS($A:$A,$D2,$B:$B,E$1)
    copied down and across
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-10-2017
    Location
    Budapest
    MS-Off Ver
    excel 2010
    Posts
    6

    Re: how to make an AxA matrix

    Here's a sample workbook.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-10-2017
    Location
    Budapest
    MS-Off Ver
    excel 2010
    Posts
    6

    Re: how to make an AxA matrix

    Dear Fdibbins,

    Thank you very much for the approach. This helps to think differently but does not actually solve my problem.

    The idea is to see conflicts between courses. This will help scheduling exams. Exam dates cannot overlap if a student is registered to overlapping courses. There are around 200 courses and 2000 students so i need an intersection table to check if the scheduled exams generate no conflicts.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to make an AxA matrix

    You need your data in a different format to do this. You need the student IDs (unique list - no duplicates) in column A and then the options across the top (course codes). This can be done with a pivot table. Once you have it in this format, you can create a clash grid.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: how to make an AxA matrix

    Refer attach file. i think this is you requird. If no then input mannualy required output.
    Here i am using simple countif formula.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to make an AxA matrix

    @AVK - this is not the output required. See the foot of the OP's opening post for sample output. Essentially, this is a clashing table:

    Excel 2016 (Windows) 32 bit
    F
    G
    H
    I
    J
    9
    ARCH 101 ARCH 102 ARCH 201 ARCH 202
    10
    ARCH 101
    0
    1
    3
    6
    11
    ARCH 102
    1
    0
    4
    7
    12
    ARCH 201
    3
    4
    0
    2
    13
    ARCH 202
    6
    7
    2
    0
    Sheet: Sheet2

    In this example, only 1 student is doing both ARCH 101 and ARCH 102, 3 are doing 201 and 101, etc.

  9. #9
    Registered User
    Join Date
    04-10-2017
    Location
    Budapest
    MS-Off Ver
    excel 2010
    Posts
    6

    Re: how to make an AxA matrix

    Thanks for the clarification. Please note that the table in the opining note contains numbers which were improvised just to show how the table should looks like except the diagonal which is either 0, NA or number of students registered to that specific course.

    I am attaching an improved sample with a new sheet including unique student ids in the rows and courses in the columns as you have recommended. But i dont know how to proceed from here to make a clashing table.

    I take another chance to describe the problem:

    Consider a pivot table where i want to drag and drop course code to 'column labels' and 'row labels' at the same time and count studentid in the 'values' section. However pivot table does not let me to drag and drop course codes to rows and columns at the same time.

    Kind regards
    Attached Files Attached Files

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to make an AxA matrix

    OK, so I've tweaked your new table thus (showing top few lines only):

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    studentid
    2
    101111
    ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    3
    102611
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    4
    102760
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121
    5
    103121
    ARCH 121
    6
    104130
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    7
    105221
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    8
    105341
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    9
    105761
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    10
    106663
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    11
    107040
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 131 ARCH 201 ARCH 202 ARCH 222
    12
    111220
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    13
    111222
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121
    14
    111550
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    15
    116291
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121
    Sheet: exhibit1-data.with.unique.stid

    Once you have done the above (you can use find and replace on each column in turn), try this in cell B3 of your results sheet, then copy across and down:

    =SUMPRODUCT((MMULT(('exhibit1-data.with.unique.stid'!$B$2:$K$195=B$2)+('exhibit1-data.with.unique.stid'!$B$2:$K$195=$A3),{1;1;1;1;1;1;1;1;1;1})=2)+0)

    I have not checked all results, but it seems to work.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    2
    ARCH 101 ARCH 102 ARCH 111 ARCH 112 ARCH 121 ARCH 131 ARCH 201 ARCH 202 ARCH 221 ARCH 222
    3
    ARCH 101
    180
    171
    173
    176
    153
    7
    123
    119
    115
    118
    4
    ARCH 102
    171
    172
    166
    172
    146
    6
    120
    116
    113
    116
    5
    ARCH 111
    173
    166
    174
    171
    149
    7
    119
    115
    111
    114
    6
    ARCH 112
    176
    172
    171
    177
    150
    7
    124
    120
    116
    119
    7
    ARCH 121
    153
    146
    149
    150
    156
    0
    97
    94
    94
    93
    8
    ARCH 131
    7
    6
    7
    7
    0
    18
    7
    6
    3
    6
    9
    ARCH 201
    123
    120
    119
    124
    97
    7
    124
    120
    116
    119
    10
    ARCH 202
    119
    116
    115
    120
    94
    6
    120
    120
    112
    117
    11
    ARCH 221
    115
    113
    111
    116
    94
    3
    116
    112
    116
    111
    12
    ARCH 222
    118
    116
    114
    119
    93
    6
    119
    117
    111
    119
    Sheet: exhibit2-expected result

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to make an AxA matrix

    Consider a pivot table where i want to drag and drop course code to 'column labels' and 'row labels' at the same time and count studentid in the 'values' section. However pivot table does not let me to drag and drop course codes to rows and columns at the same time.
    You can do that if you duplicate the course column, actually, but I still couldn't get your desired results using a pivot table. Instead, see the post above this one.

  12. #12
    Registered User
    Join Date
    04-10-2017
    Location
    Budapest
    MS-Off Ver
    excel 2010
    Posts
    6

    Re: how to make an AxA matrix

    @AliGW thank you very much. this is exactly what i want. However i have #VALUE! error in the real dataset. Could you upload your workbook so that i can check with mine?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to make an AxA matrix

    It's your workbook that I am using. However, here it is.

    It will be the MMULT function, I suspect - that will translate into your local translation when you open the workbook.

    I should also say that this isn't my work - I learnt it from another forum member a couple of months ago, and he learnt it from someone else when trying to sort out golf pairings!!! I have just adapted it to suit your requirements.
    Attached Files Attached Files
    Last edited by AliGW; 04-11-2017 at 11:47 AM.

  14. #14
    Registered User
    Join Date
    04-10-2017
    Location
    Budapest
    MS-Off Ver
    excel 2010
    Posts
    6
    Quote Originally Posted by AliGW View Post
    It's your workbook that I am using. However, here it is.

    It will be the MMULT function, I suspect - that will translate into your local translation when you open the workbook.

    I should also say that this isn't my work - I learnt it from another forum member a couple of months ago, and he learnt it from someone else when trying to sort out golf pairings!!! I have just adapted it to suit your requirements.
    thank you and everyone in the chain!

    i sorted out the error by the way. for the reference of others,in the formula one shall repeat 1s in {1,1,1....} part as the number of rows or columns i.e. the size of matrix. the formula here is for 10x10 matrix.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to make an AxA matrix

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: how to make an AxA matrix

    Hugely impressed! I had spent some time at this and had got as far as generating the raw data in (what turned out to be) the correct format (using formulae). however, I was flummoxed completely taking it to the firnal result. For what it's worth, i integrated my formula into Ali's solution to provide a complete picture.
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: how to make an AxA matrix

    Nice touch, Glenn!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to make a matrix table from a data list
    By yllew in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2016, 04:29 AM
  2. Replies: 2
    Last Post: 11-05-2014, 03:41 AM
  3. Replies: 2
    Last Post: 02-11-2014, 05:05 AM
  4. how to make a matrix formula automatically update?
    By Celeste84 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-14-2013, 03:58 AM
  5. Make cell red when value is X in a matrix
    By IKZOUHETNIETWETEN in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2013, 02:49 PM
  6. how do i make a co-ocurrence matrix?
    By niekvdbogert in forum Excel General
    Replies: 1
    Last Post: 09-21-2011, 11:22 AM
  7. How can I make a matrix style list?
    By dkersten in forum Excel General
    Replies: 1
    Last Post: 02-11-2005, 08:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1