+ Reply to Thread
Results 1 to 5 of 5

Sort table in descending order based on a column

  1. #1
    Forum Contributor
    Join Date
    05-16-2013
    Location
    Sydney,Australia
    MS-Off Ver
    Excel 2010
    Posts
    100

    Sort table in descending order based on a column

    need a formula to sort the table in descending order with some duplicated in the column.

    I have this formula but it doesn't work due to duplicates. {INDEX($A$3:$A$60,MATCH(ROWS($H$3:H3),COUNTIF($H$3:$H$60,">="&$H$3:$H$60),0))}

    Original Table

    id Project Name traffic light FY 2017/18
    31 Project A Red $104,378
    16 Project B Red $-
    53 Project C Red $18,767
    28 Project D Red $-
    20 Project E Red $5,000,000
    69 Project F Red $5,000,000
    85 Project G Red $323,753
    0 0 0 $-
    103 Project U Amber $52,064
    6 Project J Amber $5,000,000
    49 Project K Amber $52,644
    59 Project L Amber $-
    82 Project M Amber $35
    2 Project N Amber $-
    32 Project O Amber $1,671
    9 Project P Amber $-
    0 0 0 $-

    Table with Formula
    Id Project Name traffic light FY 2017/18
    20 Project E Red $5,000,000
    69 Project F Red $5,000,000
    6 Project J Amber $5,000,000
    85 Project G Red $323,753
    31 Project A Red $104,378
    49 Project K Amber $52,644
    103 Project U Amber $52,064
    53 Project C Red $18,767
    32 Project O Amber $1,671
    82 Project M Amber $35
    16 Project B Red $-
    28 Project D Red $-
    0 0 0 $-
    59 Project L Amber $-
    2 Project N Amber $-
    9 Project P Amber $-
    0 0 0 $-
    Attached Files Attached Files

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Sort table in descending order based on a column

    I don't understand why you need a formula, just sort the table using Data -> Sort.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Sort table in descending order based on a column

    J4=LARGE(E$4:E$20,ROWS(J$4:J4)) TRY THIS AND COPY TOWARDS DOWN
    Then
    G4=INDEX(B:B,SMALL(INDEX(($E$4:$E$20<>$J4)*10^10+ROW($E$4:$E$20),0),COUNTIF($J$4:$J4,$J4)))
    Copy this and paste across G4:I20
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Contributor
    Join Date
    05-16-2013
    Location
    Sydney,Australia
    MS-Off Ver
    Excel 2010
    Posts
    100

    Re: Sort table in descending order based on a column

    sorting manually is not an option.

  5. #5
    Forum Contributor
    Join Date
    05-16-2013
    Location
    Sydney,Australia
    MS-Off Ver
    Excel 2010
    Posts
    100

    Re: Sort table in descending order based on a column

    thank you very much nflsales. its perfect.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to Sort the Data in Descending Order
    By Ankit_Kumar in forum Excel General
    Replies: 8
    Last Post: 04-27-2015, 07:48 AM
  2. Pivot Table descending sort order of (Blanks) + VBA
    By bezwlosy in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 09-09-2014, 07:38 AM
  3. Automatic sort by descending order
    By anapaulacasseta in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2014, 10:49 PM
  4. macro to sort in ascending/descending order
    By apio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2012, 02:46 AM
  5. Sort Cells In Descending Order Based on Text String
    By kmccabe in forum Excel General
    Replies: 7
    Last Post: 08-01-2012, 05:58 PM
  6. Pivot table to show certain column in descending order
    By rushdenx1 in forum Excel General
    Replies: 1
    Last Post: 01-04-2012, 05:32 AM
  7. Sort in descending order
    By shahcu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2008, 03:01 AM

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