Sorting a column based on data in another column? (RANK?)

[Mr Frost]

Hi

I'm trying to create an attendance timetable for the school I work in and was hoping for some guidance on whether a final sort is possible.

So far I have managed to set it up so that the points each class receive auto updates (through columns J&K) now I'm wondering if it is possible for the class to move into the correct league position based on their points total?

I need to keep the class name tied to their percentage score when it is sorted but I can't seem to get it to work. I Would really appreciate any guidance on how I might approach this.

I've attached a copy of what I've managed to do so far to provide more context

Cheers

Rob

[DannyJ]

This will sort it for you with a button. To change the range just edit it in VBA. If you aren't sure how to do this I can try and help.

[Mr Frost]

ah that is fantastic!, thank you for such a quick response. This will make our lives a lot easier when we do the weekly update

[Glenn Kennedy]

is there a lot of data underlying all of this? How are the data in column H derived? What does your expected result look like????

[Mr Frost]

The attendance % in column H is pulled from a report from our school's management system (SIMS). We want to provide a visual representation for the parents to view the performance of their child's class over the year and also inspire a bit a competitive spirit in the children.

[José Augusto]

Hi
Try this
Copy Sheet1 to Sheet2
Clear the values of table in Sheet2
In Sheet 2 set Position to 1, 2, 3 ... From F5 to F35
In Sheet 2 column P from P5 use the following formula and copy down

=MOD(AGGREGATE(14,6,Sheet1!$L$5:$L$35+$F$5:$F$35/10000,$F$5:$F$35),1)*10000

In Sheet2!G5 use the following formula and copy down and forward (G5:L35)

=INDEX(Sheet1!$G$5:$L$35,$P5,COLUMNS($A$1:A$1))

When you update Sheet1, Sheet2 are ordered with the news values.

See the file

[Glenn Kennedy]

So. I tired to open the macro solution. Excel didn't like it and tried to "repair" it. On what opened, Lavender now has a score of 1. is that what you see on your version???

[DannyJ]

Glenn, I changed Lavender to a score of one so that I could make sure the sort was working properly. Haven't had to repair it form my end. Any idea what the issue was?

[José Augusto]

Hi
I update the file on post #6 and table are ordered.