Find top 5 most popular

[fwhite]

I'm working on an example spreadsheet that shows which lessons several teachers teach, and how many times they teach each lesson over a few months. I need to find the top few most popular lessons being taught over the months, but I'm not exactly sure the best way to do it.

I've tried adding up the total lessons taught for each lesson, then sorting them, but the sort doesn't change when the values are updated. I'm somewhat new to Excel and really have no idea how I can do this. Any ideas?

[NBVC]

if you have summed up each lesson, then you can use the LARGE() function to get largest quantity, second largest, etc

e.g. =LARGE(A1:A10,1) gets largest number
=LARGE(A1:A10,2) gets second largest
=LARGE(A1:A10,3) gets third largest.

If the actual lessons associated are listed beside each sum, say in B1:B10, then to extract the corresponding lessons...

Please Login or Register  to view this content.

where F1 contains largest number extracted from previous formula. Confirm the formula with CTRL+SHIFT+ENTER not just ENTER and copy down the 5 rows.

or if you don't want to use array formulas, and still assuming your data is in A1:B10, then in C1 enter:

=A1&"_"&COUNTIF($A$1:A1,A1)

copied down.

Then assuming you still have the LARGE() formulas in F1:F5, then in G1 enter:

=INDEX($B$1:$B$10,MATCH(F1&"_"&COUNTIF($F$1:F1,F1),$C$1:$C$10,0))

and copy down 5 rows.

[fwhite]

Thank you! The large function seems to be exactly what I want, the formula works perfectly. However, I could do with a bit more help.

I have a table that shows the total amount taught for each lesson. The lesson name is in the first column, the total taught is in the second column. I'm creating a separate table to show the top few lessons, using the same layout as the previous table. Is there any way to grab the name of the lesson based on the figure shown in the 'Total taught' column?

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[fwhite]

Okay, here's a dummy workbook. Hopefully it helps to explain what I'm trying to do.

[NBVC]

Do you mean in H7:

=INDEX($C$7:$C$18,MATCH(I7,$D$7:$D$18,0))

copied down?

or

=VLOOKUP(I7,CHOOSE({1,2},$D$7:$D$18,$C$7:$C$18),2,0) just recently learned from daddylonglegs

[fwhite]

Well that formula certainly works as intended, it's doing exactly what I needed. But I have no idea what it means or how it works lol, I've never used the INDEX formula before.

[NBVC]

Note though, those only work if there are no duplicates in I7:I11 (ie. no duplicate amounts) or else you will get the same lesson listed multiple times.

To work around that, I have suggested in my initial post the alternatives.

Here:

Please Login or Register  to view this content.

and then hold the CTRL+SHIFT keys down, then press ENTER. You should get { } around the formula... then copy down.

[NBVC]

Well that formula certainly works as intended, it's doing exactly what I needed. But I have no idea what it means or how it works lol, I've never used the INDEX formula before.

It's a kind of Vlookup that allows you to work in either direction as far as the relation between the lookup column and the extracting column....

Have a look at this site for more info:

http://www.contextures.com/xlfunctions03.html

[fwhite]

Brilliant, thanks a lot, you've been a great help!