+ Reply to Thread
Results 1 to 7 of 7

Find Intersection Values

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    6

    Find Intersection Values

    I am trying to get the following Input and Output.

    Input:


    Student Class_Code
    A C101
    A C102
    B C101
    B C102
    B C103
    C C101

    Output: Trying to find how many students appear for the combination of classes.


    C101 C102 C103
    C101 3 2 1
    C102 2 2 0
    C103 1 0 1
    Attached Files Attached Files
    Last edited by aasulman; 09-20-2020 at 11:00 PM. Reason: attach file

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    641

    Re: Find Intersection Values

    Clarification needed. Since student B appears to have class codes C102 and C103, why does your 2nd table show 0 for row C102/column C103 and row C103/column C102?

    Clearly the main diagonal of the 2nd table should be 3, 2, 1, but what should the logic be for the off-diagonal entries? There are 2 students (A and B) taking both C101 and C102, so those 2s make sense. Likewise, there's one student (B) taking C101 and C103, so those 1s make sense. But there's one student (B again) taking C102 and C103, so those should presumably also be 1s.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,450

    Re: Find Intersection Values

    I came up with:

    G3:
    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    Drag down andd accross

    P/S: C102 & C103 intersection: B6=C102 then "B" in A6; "B" in A7 then C103 in B7=> I got 1 while your goal is 0. What is your logic?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-02-2013
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Find Intersection Values

    Quote Originally Posted by hrlngrv View Post
    Clarification needed. Since student B appears to have class codes C102 and C103, why does your 2nd table show 0 for row C102/column C103 and row C103/column C102?

    Clearly the main diagonal of the 2nd table should be 3, 2, 1, but what should the logic be for the off-diagonal entries? There are 2 students (A and B) taking both C101 and C102, so those 2s make sense. Likewise, there's one student (B) taking C101 and C103, so those 1s make sense. But there's one student (B again) taking C102 and C103, so those should presumably also be 1s.

    Hello, yes you are correct, 102 and 103 intersection should be 1. It was an error on the output.

  5. #5
    Registered User
    Join Date
    04-02-2013
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Find Intersection Values

    Quote Originally Posted by bebo021999 View Post
    I came up with:

    G3:
    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    Drag down andd accross

    P/S: C102 & C103 intersection: B6=C102 then "B" in A6; "B" in A7 then C103 in B7=> I got 1 while your goal is 0. What is your logic?
    Thanks! excellent, it worked.

  6. #6
    Registered User
    Join Date
    04-02-2013
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Find Intersection Values

    Hello bebo021999, can this be done using a Pivot Table?

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,450

    Re: Find Intersection Values

    I have no idea about whether PT works, I am not familiar with it. Hope someone jump in to put and end with PT.

+ 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. Replies: 2
    Last Post: 01-11-2019, 12:51 AM
  2. [SOLVED] Find the intersection using arrays
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-23-2017, 03:42 PM
  3. find intersection and delimit the values
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-10-2016, 10:23 AM
  4. find intersection and delimit the values
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2016, 11:17 AM
  5. find intersection and delimit the values
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2016, 11:17 AM
  6. VBA to find the range at the intersection of two values.
    By Benisato in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-31-2013, 06:10 PM
  7. [SOLVED] find a value at an intersection?
    By Ellen G. in forum Excel General
    Replies: 4
    Last Post: 06-29-2006, 03:40 AM

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