Top 10 & Below 10 Registrations

[callsidd]

Hello All,

I have a list of around 100 schools in Sheet1 who used to give registrations every month. I am looking for a way to see Top 10 and Below 10 registrations of each month, based on registrations received.

Is there any way other than Pivot table to display Top 10 and Below 10 schools in a dashboard automatically.

The idea is if I have a list of months by using data validation then it automtically changes top 10 and below 10 on the base of each month and display registation numbers.

Top 10 Below 10
A
B
C
D
E
F
G
H
I
J

 

[Swapnil Shah]

Background of data used for calculation :

1) assume your data is on Sheet2 ranging from A1:M101 , where first row has headers for months and column A has headers for school name.

2) In sheet1 , you have a cell Say B2 where you can select a month from Data validation > List

3) Desired list of top 10 Schools will be in Sheet1 ranging from A5:B15 and that of lower 10 in in range A18:B27

Method:

1) You need to give your months columns a name i.e. select Cells B1:M101 on sheet 2 > Press Ctrl+Shift+F3 > Select an option which says "Top Row" This will give range B2:B101 name as Month1 , C2:C101 Name as Month2 so on...

2) In sheet1 cell B6: B15 put formula as =LARGE(INDIRECT($B$2),ROWS($A$6:A6)) , this will give you list of admissions in descending order for top 10 Schools

3) In sheet1 cell A6:A15 put an array formula as =INDEX(Sheet1!$A$2:$A$101,LARGE(IF(INDIRECT($B$2)=B6,ROW(INDIRECT($B$2))-1,""),COUNTIF($B$6:B6,B6))) Press CTRL+Shift+Enter This will give you list of Schools names for top 10

4) Similarly to get list of bottom 10 and their admission count

In sheet1 Cells B18:B27 use formula =SMALL(INDIRECT($B$2),ROWS($B$18:B18))
In sheet1 Cells A18 :A27 use an array formula =INDEX(Sheet1!$A$2:$A$101,SMALL(IF(INDIRECT($B$2)=B18,ROW(INDIRECT($B$2))-1,""),COUNTIF($B$18:B18,B18))) Press CTRL+Shift+Enter ​

You shall have your desired result.

Thanks
Swapnil Shah

 

[callsidd]

Thank you so much. Its brilliant

Background of data used for calculation :

1) assume your data is on Sheet2 ranging from A1:M101 , where first row has headers for months and column A has headers for school name.

2) In sheet1 , you have a cell Say B2 where you can select a month from Data validation > List

3) Desired list of top 10 Schools will be in Sheet1 ranging from A5:B15 and that of lower 10 in in range A18:B27

Method:

1) You need to give your months columns a name i.e. select Cells B1:M101 on sheet 2 > Press Ctrl+Shift+F3 > Select an option which says "Top Row" This will give range B2:B101 name as Month1 , C2:C101 Name as Month2 so on...

2) In sheet1 cell B6: B15 put formula as =LARGE(INDIRECT($B$2),ROWS($A$6:A6)) , this will give you list of admissions in descending order for top 10 Schools

3) In sheet1 cell A6:A15 put an array formula as =INDEX(Sheet1!$A$2:$A$101,LARGE(IF(INDIRECT($B$2)=B6,ROW(INDIRECT($B$2))-1,""),COUNTIF($B$6:B6,B6))) Press CTRL+Shift+Enter This will give you list of Schools names for top 10

4) Similarly to get list of bottom 10 and their admission count

In sheet1 Cells B18:B27 use formula =SMALL(INDIRECT($B$2),ROWS($B$18:B18))
In sheet1 Cells A18 :A27 use an array formula =INDEX(Sheet1!$A$2:$A$101,SMALL(IF(INDIRECT($B$2)=B18,ROW(INDIRECT($B$2))-1,""),COUNTIF($B$18:B18,B18))) Press CTRL+Shift+Enter ​

You shall have your desired result.

Thanks
Swapnil Shah