automatic sort

[Guest]

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

 

[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

 

[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

 

[malik641]

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

Just curious, how would you make the formula to sort text?

 

[malik641]

Anybody??

 

[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

 

[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.