Correlation Matrix keying on one variable

[Reboshua]

I have a dataset - see below - that shows the quiz grades of 30 students in 6 different classrooms over 5 days in February.​

I'd like to create a correlation matrix to show the relationship between these students and their grades, but I am ONLY interested in seeing correlations between kids in the same classroom. I am trying to examine whether seating arrangements in classrooms have an effect on student grades, either because kids cheat off of one another or because kids of similar abilities tend to cluster around one another, etc.

Without breaking the data table up by Teacher and then doing individual matrices for each classroom, is there a way to create a single correlation table that only correlates kids with the same teacher to one another?

Thanks!Excel 2016 (Windows) 64 bit

	A​	B​	C​	D​
1​	Name	Teacher	Date	Quiz Grade
2​	Sally	Jones	2/1/2018​	61​
3​	Jane	Anderson	2/1/2018​	69​
4​	Billy	Smith	2/1/2018​	88​
5​	Bobby	Hill	2/1/2018​	93​
6​	Alice	Barnes	2/1/2018​	99​
7​	Joe	Johnson	2/1/2018​	91​
8​	Gary	Jones	2/1/2018​	66​
9​	Carol	Anderson	2/1/2018​	62​
10​	Ann	Smith	2/1/2018​	81​
11​	Michael	Hill	2/1/2018​	69​
12​	Jessica	Barnes	2/1/2018​	68​
13​	Tim	Johnson	2/1/2018​	82​
14​	Peter	Jones	2/1/2018​	75​
15​	Cindy	Anderson	2/1/2018​	75​
16​	Ellen	Smith	2/1/2018​	96​
17​	Martha	Hill	2/1/2018​	89​
18​	David	Barnes	2/1/2018​	91​
19​	Matthew	Johnson	2/1/2018​	91​
20​	Joshua	Jones	2/1/2018​	77​
21​	Amy	Anderson	2/1/2018​	68​
22​	Tom	Smith	2/1/2018​	96​
23​	Ben	Hill	2/1/2018​	83​
24​	Alie	Barnes	2/1/2018​	66​
25​	Rachael	Johnson	2/1/2018​	85​
26​	Kaylie	Jones	2/1/2018​	61​
27​	Kim	Anderson	2/1/2018​	64​
28​	Mary	Smith	2/1/2018​	88​
29​	Val	Hill	2/1/2018​	69​
30​	Shawn	Barnes	2/1/2018​	99​
31​	Vicky	Johnson	2/1/2018​	68​
32​	Sally	Jones	2/2/2018​	91​
33​	Jane	Anderson	2/2/2018​	85​
34​	Billy	Smith	2/2/2018​	80​
35​	Bobby	Hill	2/2/2018​	78​
36​	Alice	Barnes	2/2/2018​	85​
37​	Joe	Johnson	2/2/2018​	89​
38​	Gary	Jones	2/2/2018​	92​
39​	Carol	Anderson	2/2/2018​	63​
40​	Ann	Smith	2/2/2018​	71​
41​	Michael	Hill	2/2/2018​	63​
42​	Jessica	Barnes	2/2/2018​	72​
43​	Tim	Johnson	2/2/2018​	80​
44​	Peter	Jones	2/2/2018​	77​
45​	Cindy	Anderson	2/2/2018​	62​
46​	Ellen	Smith	2/2/2018​	60​
47​	Martha	Hill	2/2/2018​	98​
48​	David	Barnes	2/2/2018​	73​
49​	Matthew	Johnson	2/2/2018​	78​
50​	Joshua	Jones	2/2/2018​	70​
51​	Amy	Anderson	2/2/2018​	72​
52​	Tom	Smith	2/2/2018​	96​
53​	Ben	Hill	2/2/2018​	90​
54​	Alie	Barnes	2/2/2018​	87​
55​	Rachael	Johnson	2/2/2018​	75​
56​	Kaylie	Jones	2/2/2018​	77​
57​	Kim	Anderson	2/2/2018​	61​
58​	Mary	Smith	2/2/2018​	99​
59​	Val	Hill	2/2/2018​	90​
60​	Shawn	Barnes	2/2/2018​	92​
61​	Vicky	Johnson	2/2/2018​	70​
62​	Sally	Jones	2/3/2018​	90​
63​	Jane	Anderson	2/3/2018​	85​
64​	Billy	Smith	2/3/2018​	77​
65​	Bobby	Hill	2/3/2018​	97​
66​	Alice	Barnes	2/3/2018​	94​
67​	Joe	Johnson	2/3/2018​	96​
68​	Gary	Jones	2/3/2018​	84​
69​	Carol	Anderson	2/3/2018​	88​
70​	Ann	Smith	2/3/2018​	95​
71​	Michael	Hill	2/3/2018​	80​
72​	Jessica	Barnes	2/3/2018​	64​
73​	Tim	Johnson	2/3/2018​	87​
74​	Peter	Jones	2/3/2018​	64​
75​	Cindy	Anderson	2/3/2018​	67​
76​	Ellen	Smith	2/3/2018​	66​
77​	Martha	Hill	2/3/2018​	81​
78​	David	Barnes	2/3/2018​	69​
79​	Matthew	Johnson	2/3/2018​	81​
80​	Joshua	Jones	2/3/2018​	76​
81​	Amy	Anderson	2/3/2018​	91​
82​	Tom	Smith	2/3/2018​	78​
83​	Ben	Hill	2/3/2018​	71​
84​	Alie	Barnes	2/3/2018​	62​
85​	Rachael	Johnson	2/3/2018​	100​
86​	Kaylie	Jones	2/3/2018​	92​
87​	Kim	Anderson	2/3/2018​	82​
88​	Mary	Smith	2/3/2018​	65​
89​	Val	Hill	2/3/2018​	64​
90​	Shawn	Barnes	2/3/2018​	73​
91​	Vicky	Johnson	2/3/2018​	91​
92​	Sally	Jones	2/4/2018​	68​
93​	Jane	Anderson	2/4/2018​	72​
94​	Billy	Smith	2/4/2018​	97​
95​	Bobby	Hill	2/4/2018​	95​
96​	Alice	Barnes	2/4/2018​	78​
97​	Joe	Johnson	2/4/2018​	76​
98​	Gary	Jones	2/4/2018​	63​
99​	Carol	Anderson	2/4/2018​	60​
100​	Ann	Smith	2/4/2018​	73​
101​	Michael	Hill	2/4/2018​	71​
102​	Jessica	Barnes	2/4/2018​	80​
103​	Tim	Johnson	2/4/2018​	66​
104​	Peter	Jones	2/4/2018​	75​
105​	Cindy	Anderson	2/4/2018​	73​
106​	Ellen	Smith	2/4/2018​	73​
107​	Martha	Hill	2/4/2018​	86​
108​	David	Barnes	2/4/2018​	83​
109​	Matthew	Johnson	2/4/2018​	91​
110​	Joshua	Jones	2/4/2018​	86​
111​	Amy	Anderson	2/4/2018​	98​
112​	Tom	Smith	2/4/2018​	95​
113​	Ben	Hill	2/4/2018​	65​
114​	Alie	Barnes	2/4/2018​	66​
115​	Rachael	Johnson	2/4/2018​	75​
116​	Kaylie	Jones	2/4/2018​	60​
117​	Kim	Anderson	2/4/2018​	89​
118​	Mary	Smith	2/4/2018​	93​
119​	Val	Hill	2/4/2018​	64​
120​	Shawn	Barnes	2/4/2018​	83​
121​	Vicky	Johnson	2/4/2018​	78​
122​	Sally	Jones	2/5/2018​	99​
123​	Jane	Anderson	2/5/2018​	75​
124​	Billy	Smith	2/5/2018​	64​
125​	Bobby	Hill	2/5/2018​	95​
126​	Alice	Barnes	2/5/2018​	79​
127​	Joe	Johnson	2/5/2018​	81​
128​	Gary	Jones	2/5/2018​	66​
129​	Carol	Anderson	2/5/2018​	62​
130​	Ann	Smith	2/5/2018​	98​
131​	Michael	Hill	2/5/2018​	60​
132​	Jessica	Barnes	2/5/2018​	79​
133​	Tim	Johnson	2/5/2018​	65​
134​	Peter	Jones	2/5/2018​	73​
135​	Cindy	Anderson	2/5/2018​	63​
136​	Ellen	Smith	2/5/2018​	84​
137​	Martha	Hill	2/5/2018​	96​
138​	David	Barnes	2/5/2018​	98​
139​	Matthew	Johnson	2/5/2018​	71​
140​	Joshua	Jones	2/5/2018​	65​
141​	Amy	Anderson	2/5/2018​	67​
142​	Tom	Smith	2/5/2018​	100​
143​	Ben	Hill	2/5/2018​	93​
144​	Alie	Barnes	2/5/2018​	98​
145​	Rachael	Johnson	2/5/2018​	63​
146​	Kaylie	Jones	2/5/2018​	98​
147​	Kim	Anderson	2/5/2018​	99​
148​	Mary	Smith	2/5/2018​	97​
149​	Val	Hill	2/5/2018​	60​
150​	Shawn	Barnes	2/5/2018​	97​
151​	Vicky	Johnson	2/5/2018​	95​

Sheet: Sheet20