+ Reply to Thread
Results 1 to 6 of 6

displaying data in descending order (automatically)

  1. #1
    Registered User
    Join Date
    12-15-2006
    Posts
    18

    displaying data in descending order (automatically)

    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.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,659
    You could use a combination of LARGE and INDEX/MATCH, but it gets messy when there are tie scores. Why not just sort?

  3. #3
    Registered User
    Join Date
    12-15-2006
    Posts
    18
    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!

  4. #4
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169
    ^ 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

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,659
    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?

  6. #6
    Forum Contributor
    Join Date
    10-14-2004
    Location
    San Diego, CA
    Posts
    213
    Hello mrmiddleman:

    Assuming your data starts in row 2.

    Paste this formula in cell F2 and fill down to row 50, hide this column.
    Please Login or Register  to view this content.
    Paste this formula in column E.
    Please Login or Register  to view this content.
    Paste this one in column D.
    Please Login or Register  to view this content.


    Matt

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1