Table to pick out most common entries and count occurences of each

[Neil Goldwasser]

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

 

[Duke Carey]

You could do what you want with a pivot table - which gives you the
opportunity to limit the presentation to the top n of whatever you are
summarizing.

Select a cell anywhere in your data, go to the Data menu and choose Pivot
table..., make sure Excel has selected the correct range, and click on
Finish. You can then drag fields from the pop-up box to the pivot table and
adjust settings. Experiment a little and you should get what you want

 

[Neil Goldwasser]

Thanks for your help!
Unfortunately I have never used PivotTables. I just had a quick look, but
I'm baffled.
I guess I'll need to consult an guidebook!
Thanks, Neil Goldwasser

 

[Roger Govier]

Hi Neil
It is definitely worth learning about Pivot Tables and here is a great place
to start
http://peltiertech.com/Excel/Pivots/pivotstart.htm
and also look at Debra's site
http://www.contextures.com/xlPivot05.html

 

[Aladin Akyurek]

What follows takes up your "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." The pivot table approach, as suggested, will
also produce the results the formula approach below generates.

Let A3:A15 house the following course sample:

{"Course";"Math";"AI";"Math";"Math";"Lisp";"Lisp";"AI";"DB";"DB";#N/A;"Lisp";"Lisp"}

where A3 is a header, while A4, A5, and so on are courses students have
taken.

The problem: What are the Top N courses students take most often?

A1: N

which is a label.

B1: 3

which is N of Top N (Replace this with 10 in your own sheet.).

A2: Ties of the Nth

which is a label.

B2:

=MAX(IF(INDEX(B4:B15,MATCH(B1,C4:C15,0))=B4:B15,C4:C15))-B1

which must be confirmed with control+shift+enter, not just with enter.

B3: Count

which is a label.

B4, copied down:

=IF(ISNUMBER(MATCH(A4,$A$3:A3,0)),"",COUNTIF($A$4:$A$15,A4))

C3: Rank

which is a label.

C4, copied down:

=IF(N(B4),RANK(B4,$B$4:$B$15)+COUNTIF($B$4:B4,B4)-1,"")

D3: Most Freq

which is just a label.

D4, copied down:

=IF(ROW()-ROW($D$4)+1<=$B$1+$B$2,INDEX($A$4:$A$15,MATCH(ROW()-ROW($D$4)+1,$C$4:$C$15,0)),"")

The result that you get in column D is a list, consisting one or more
courses...

{"Most Freq";"Lisp";"Math";"AI";"DB"}