Data Sort Problem

[andyp161]

Hi there,

Is there a function that will sort data in descending orde
automatically rather than having to do it via 'sort, data' etc. i.e
the moment a value in a cell is changed the table will automaticall
readjust so that the highest value is at the top and the lowest valu
is at the bottom (with the entire row also)??

Many thanks

And

 

[Max]

Maybe try something along these lines ..

In Sheet1
------------
Assume col headers in row1, data in row2 down
and the sort col is col A,
with values are listed in row2 down
(inclusive possibility of ties), e.g.:

319
303
319
917
160
etc

(Other cols of data are in cols B, C, D, etc)

Use an empty col to the right of all your other cols of data,
say in col X,

Put in X2: =IF(A2="","",A2+ROW()/10^10)
Copy X2 down as many rows as you have data in col A

Col X will serve as an arbitrary tie-breaker,
to enable full extract in Sheet2

In Sheet2
------------
Assuming row1 contains identical col headers as for Sheet1

Put in A2:

=IF(ISERROR(MATCH(LARGE(Sheet1!$X:$X,ROW(A1)),Sheet1!$X:$X,0)),"",OFFSET(She
et1!$A$1,MATCH(LARGE(Sheet1!$X:$X,ROW(A1)),Sheet1!$X:$X,0)-1,COLUMN(A1)-1))

Copy A2 down as many rows as there is data in col A in Sheet1
Fill right to cover as many cols of data that you have in Sheet1

The above will return the desired auto-descending sort
of the table in Sheet1 by the sorted values in col A

For an auto-ascending sort, just change
LARGE to SMALL in the formula in A2 of Sheet2

 

[Guest]

Try

http://www.pulrisk.de/Excel-
Beispiel/efficiency_analysis.xls

Use at your own risk.

 

[NH]

Use Index/large/small/vlookup functions to get the desired result