Pivot Tables Totals

[Ian60284]

Hello, I’m struggling with a pivot table question that I hope you can help me with.

I have a sheet. In column A is the name of supervising lecturer. In column B is the name of an undergraduate. Columns C onwards have information about projects and dates and locations and aren’t important to the pivot. In total there are approximately 27,000 lines of data, each line representing a single project.

I’m trying to find out who has supervised the most undergraduates – not the most projects. Sometimes the same supervisor and student will work together on several different projects – I want that to count only once. I’m trying to work out which supervisor has worked with the most undergraduates.

I pivot columns A and B. I’m left with a table showing the supervisors on the left, the name of the student in the next column and then the number of times they’ve worked together. Such as this……

Alf----Anne 2
------Clare 1
------Janet 1
------Maureen 1
------Ursula 1
Alf Total 6
Ben----Bernie 2
-------Kimbers 1
-------Norma 1
-------Vera 1
Ben Total 5

And so on and so forth.

But, what I want it to show is

Alf 5
Ben 4

Where the number after the name is the number of students the supervisor has worked with not the number of projects they've worked on.

Any ideas?