I am looking to automatically sort a column of numbers in descending order without touching the sort button, or VBA.

Unfortunately I am trying to achieve this in a work environment where I have no access to VBA and excel is not one of the latest versions that contains the new SORT function in 365.

It is quite literally a column of numbers, and the numbers are automatically updated as they are totals of rows of smaller numbers elsewhere, and these change based on something else - so this specific column will always change numbers, but I need the column to automatically keep on top of sorting numbers by descending order.

I tried using rank.eq and some other bits with adding a 1 to each row to avoid duplicates, but this buggered it up if more than two duplicates was found. Is there anything I can do at all? Even if it involves going a very long way round it and building extra tables and things etc.

Grateful for any help.

CodePudding user response：

It'd be easier to see your data and without being able to use a spill range, it's impossible to know how many rows. I also think you're intending to use LARGE function rather than RANK.

If you had your numbers in column A, you could drag the following formula down the appropriate number of rows to get the numbers sorted... (starting in cell B1)

=LARGE(A$1:INDEX(A:A,COUNTA(A:A),1),ROW())

If you can get your numbers in a table, you could use a similar formula but the table would ensure the appropriate rows exist (assume table name is Table1 and note the column names of RawNumbers and Sorted). Put this in Sorted Column:

=LARGE([RawNumbers],ROW()-ROW(Table1[[#Headers],[Sorted]]))

I presume using a pivot table is not a viable option... but these are how you could accomplish your objective of sorting by formula.