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

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" wrote:

> 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

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



"Duke Carey" wrote:

> 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" wrote:
>
> > 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

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

--
Regards

Roger Govier


"Neil Goldwasser" <[email protected]> wrote in
message news:[email protected]...
> 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
>
>
>
> "Duke Carey" wrote:
>
>> 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" wrote:
>>
>> > 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

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"}

Neil Goldwasser wrote:
> 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

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" wrote:

> 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

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



"Duke Carey" wrote:

> 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" wrote:
>
> > 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

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

--
Regards

Roger Govier


"Neil Goldwasser" <[email protected]> wrote in
message news:[email protected]...
> 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
>
>
>
> "Duke Carey" wrote:
>
>> 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" wrote:
>>
>> > 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

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"}

Neil Goldwasser wrote:
> 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

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

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" wrote:

> 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

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



"Duke Carey" wrote:

> 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" wrote:
>
> > 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

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

--
Regards

Roger Govier


"Neil Goldwasser" <[email protected]> wrote in
message news:[email protected]...
> 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
>
>
>
> "Duke Carey" wrote:
>
>> 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" wrote:
>>
>> > 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

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"}

Neil Goldwasser wrote:
> 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