Hello. If anybody could please help me here I'd be really, really grateful!

I have a worksheet that monitors which courses people are attending. For
each person, the course code is entered into one column and the department
that runs that course is entered in a different column. The course codes will
come up more than once, and the number of times will vary, depending on how
many people are on that course. What I need to do is find a way of getting
excel to work out the ten most popular course codes for each department, and
then count how many times that code comes up in the worksheet. Unfortunately
there are several hundreds of courses run across all departments, so it needs
something better than what I can currently do.

There will not be any blank cells mixed in with the data, but there might be
a lot of "N/A" entries where a course is not known for any particular person
(I would rather it didn't count these as a single course, as it is highly
likely that the "unknown" entry would make the top ten.)

If possible I would also like it to count how many "other" courses there are
that did not make it into the top ten, and how many entries these courses
have combined (this time counting any N/A cells). Hopefully a table would
then be possible for each department separately, e.g.

POSITION DEPT. COURSE # Entries
1 ACE AB123 15
2 ACE CD456 14
3 ACE EF789 11
.... etc€¦ ... etc€¦ ... etc€¦ ... etc€¦
10 ACE ZY987 4
OTHER ACE 45 other courses 89 (Between all 45)

and so on...

Ideally, it would be great to be also able to pick out the ten most popular
courses across all departments, and put this in another table, e.g.

POSITION DEPT. COURSE # Entries
1 ACE AB123 15
2 BUC CC261 14
3 ACE CD456 14
.... etc€¦ ... etc€¦ ... etc€¦ ... etc€¦
10 CET AA910 8
OTHER ALL 247 other courses 352 (Between all 247)


Does anybody know a way of doing such a thing? It would help me out so much
if you did!

If this looks a bit too complicated, are there other more simple versions
possible, e.g. just counting across all departments, and maybe extending it
to the top 30; printing the names of courses that come up more than 8 times;
counting the number of unique course codes, and listing each code that comes
up; counting the number of unique course codes for each department; and so
on...


It is for a good cause if anybody is willing to help.
Many thanks in advance, Neil Goldwasser