I've attached a demo packaged workbook to play with this problem.
I and trying to figure out how many students are enrolled at different schools. We have 7 schools including four "primary" schools which I've labeled A-D and three "secondary" schools which I've labeled X-Z. We have 100 students, each of which is enrolled at exactly one of the four primary schools. In addition to those enrollments, a subset of those 100 students has an additional enrollment at one of the secondary schools. So the underlying data has 130 records in it, one for each enrollment instance, some of which are secondary enrollments at schools X, Y, or Z in addition to the student's primary enrollment.
I need to be able to depict the number of students enrolled at each school, BUT... if a student is double enrolled at both a primary and a secondary school, then I need to show that student at the SECONDARY school instead of the primary school. Ie. the enrollment count for schools A-D need to depict ONLY the students that enrolled solely at that school and do not have a secondary enrollment. Then the enrollments for the three secondary schools X-Y need to depict how many students have that school as a secondary enrollment, but also ideally would depict for each of those students which primary school the students are coming from.
For a viz I was thinking of a table with primary schools A-D in the first column down the left and then secondary schools X-Z in the first row across the top, accompanied by a NULL choice too. The intersecting cells would contain the number of students enrolled in school A-D and where they have a secondary enrollment. For most students they would be counted in the column with NULL as a header indicating that they have only a primary enrollment.
Does this make sense?