Hi Guys,
I have a table with exam results in it, Column A has the name of the student, Column B has their overall score:
I.e.
A B
John 94%
Billy 72%
Frank 100%
Mary 82%
Is there a way to have an area of the sheet/a new sheet automatically display the list of results with column B in descending order? (and update when the scores change?)
Thanks,
Last edited by VBA Noob; 10-29-2007 at 11:36 AM.
You could use a combination of LARGE and INDEX/MATCH, but it gets messy when there are tie scores. Why not just sort?
i would, and do...
However the excel sheet is being sent up the hierarchy to people who really wouldnt know how to sort. and even if i taught them, they would forget!
But thanks anyway!
^ Yes, when you SORT, there is an option you can check on the 3 fields you can select to sort either ascending or descending
It's invoked through DATA >>> SORT
You could add a column to the right that increases each grade by a fractional amount based on the number of prior ties (e.g., =B7 + COUNTIF(B$1:B7, B7)/1000), reference that column with the LARGE function, and INDEX/MATCH to retrieve the names and (unaltered) grades. How's that sound?
Hello mrmiddleman:
Assuming your data starts in row 2.
Paste this formula in cell F2 and fill down to row 50, hide this column.
Paste this formula in column E.
=IF(B2="","",RANK(B2,B$2:B$50,1)+ROW()/100000)
Paste this one in column D.=IF(ISERROR(SMALL(F:F,ROW(1:1))),"",OFFSET(B$1,MID(SMALL(F:F,ROW(1:1)),FIND(".",SMALL(F:F,ROW(1:1))),6)*100000-1,0))
=IF(ISERROR(SMALL(F:F,ROW(1:1))),"",OFFSET(A$1,MID(SMALL(F:F,ROW(1:1)),FIND(".",SMALL(F:F,ROW(1:1))),6)*100000-1,0))
Matt
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks