Find Intersection Values

[aasulman]

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

[hrlngrv]

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.

[bebo021999]

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?

[aasulman]

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.

[aasulman]

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.

[aasulman]

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

[bebo021999]

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