automatic sort

R

Robert

Hello,
I have a column of numbers (column C) with some blank spaces at the
bottom leaving room to add additional numbers. I want the numbers to re-sort
automatically whenever I change, add or delete a number. I saw some posts on
using the LARGE and RANK functions, but was not able to see how to apply them
in my example. Is there a simple way?

thanks in advance for any tips...
Robert
 
B

Biff

Hi!

Why not just use the sort feature from either the standard toolbar or the
Menu Data>Sort?

If you really want a formula:

=IF(ISERROR(LARGE(A$1:A$100,COUNT(A$1:A$100)-(ROW(1:1)-1))),"",LARGE(A$1:A$100,COUNT(A$1:A$100)-(ROW(1:1)-1)))

Will sort ascending. Change LARGE to SMALL to sort descending.

Copy down far enough to allow for additional values being added to column A.

Biff
 
B

Bob Wall

You could also use a worksheet function:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("A1:F31").Select 'Change the range to suit your needs
Range("F1").Activate
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("A2")
_
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom
Range("F2").Select

End Sub


'Change Order2:=xlAscending to Order2:=xlDescending if necessary
 
M

malik641

Biff said:
Hi!

Why not just use the sort feature from either the standard toolbar or
the
Menu Data>Sort?

If you really want a formula:

=IF(ISERROR(LARGE(A$1:A$100,COUNT(A$1:A$100)-(ROW(1:1)-1))),"",LARGE(A$1:A$100,COUNT(A$1:A$100)-(ROW(1:1)-1)))

Will sort ascending. Change LARGE to SMALL to sort descending.

Copy down far enough to allow for additional values being added to
column A.

Biff
Click to expand...
Just curious, how would you make the formula to sort text?
 
B

Biff

Hi!

Here's one way but it will only sort based on the first character of the
text entry.

Assume the text entries are in the range A1:A20. To extract those entries
sorted in ascending order, enter this formula as an array with the key combo
of CTRL,SHIFT,ENTER:

=INDEX(A$1:A$20,MATCH(SMALL(CODE(UPPER(LEFT(A$1:A$20,1))),ROW(1:1)),CODE(UPPER(LEFT(A$1:A$20,1))),0))

Copy down as needed.

To sort in descending order, in the formula change SMALL to LARGE.

Biff
 
M

malik641

Hey Biff,
The formula only gave me a #Value! error as an array and a #N/A! erro
for a non-array. I'll try to tweak it, but I don't know if I'll get i
to work. I'll keep you posted.
 
Top