automatic sort
 

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

Hi!

Why not just use the sort feature from either the standard toolbar or the
Menu DataSort?

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

"Robert" wrote in message
...
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

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


"Robert" wrote in message
...
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 Wrote:
Hi!

Why not just use the sort feature from either the standard toolbar or
the
Menu DataSort?

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

"Robert" wrote in message
...
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

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


--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=381859

Anybody???


--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=381859

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" wrote in
message ...

Anybody???


--
malik641
------------------------------------------------------------------------
malik641's Profile:
http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=381859

Hey Biff,
The formula only gave me a #Value! error as an array and a #N/A! error
for a non-array. I'll try to tweak it, but I don't know if I'll get it
to work. I'll keep you posted.
Biff Wrote:
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"
wrote in
message ...

Anybody???


--
malik641

------------------------------------------------------------------------
malik641's Profile:
http://www.excelforum.com/member.php...o&userid=24190
View this thread:
http://www.excelforum.com/showthread...hreadid=381859



--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=381859