Automatic Data Sort?

[Scott]

Is there a way to automatically sort a series of data?

For instance, cells A1:A144 have a numeric value. In Column B1:B144, I'd
like to sort those numeric values in ascending order. If I update one of the
numeric values in A1:A144, is there a way that B1:B144 will automatically
update and sort without me having to put a manual ascending sort order to it?

Thanks!

 

[Gary''s Student]

Easy if there are no duplicates:

=SMALL(A$1:A$144,ROW()) and copy down

 

[Don Guillett]

You can use a worksheet_change event macro in the sheet module but do you
really want to sort with EACH entry?

 

[Scott]

What about if there are duplicates?

Easy if there are no duplicates:

=SMALL(A$1:A$144,ROW()) and copy down

 

[Scott]

Yes. The A Column has numeric data. Some may be duplicates. Not sure how
many duplicates I will have as they are rounded to one decimal. But if one
of the values in Column A changes, that'll change that value's ranking in
Column B. Hopefully there won't be any duplicates, but with 144 values,
that's not a guarantee.

 

[Gord Dibben]

I'm not sure, but I think Don may be cautioning you about auto-sorting which
is easily done using event code.

The problem with automatic sorting is error checking bad data entries.

Once you hit the Enter key the sort is done and makes it more difficult to
find an error.


Gord Dibben MS Excel MVP

 

[Gary''s Student]

If there are duplicates, then instert the following event macro in the
worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r1 = Range("A1:A144")
Set r2 = Range("B1:B144")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r1.Copy r2
r2.Sort Key1:=Range("B1")
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Gary''s Student]

If there are duplicates, then instert the following event macro in the
worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r1 = Range("A1:A144")
Set r2 = Range("B1:B144")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r1.Copy r2
r2.Sort Key1:=Range("B1")
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Bernd P]

Hello Scott,

I can offer two approaches:
http://www.sulprobil.com/html/sort_vba.html

http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

 

[Bernd P]

Hello Scott,

I can offer two approaches:
http://www.sulprobil.com/html/sort_vba.html

http://www.sulprobil.com/html/sorting.html

Regards,
Bernd