Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Data Sort?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Data Sort?
Easy if there are no duplicates:
=SMALL(A$1:A$144,ROW()) and copy down -- Gary''s Student - gsnu200852 "Scott" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Data Sort?
You can use a worksheet_change event macro in the sheet module but do you really want to sort with EACH entry? -- Don Guillett Microsoft MVP Excel SalesAid Software "Scott" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Data Sort?
What about if there are duplicates?
"Gary''s Student" wrote: Easy if there are no duplicates: =SMALL(A$1:A$144,ROW()) and copy down -- Gary''s Student - gsnu200852 "Scott" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Data Sort?
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. "Don Guillett" wrote: You can use a worksheet_change event macro in the sheet module but do you really want to sort with EACH entry? -- Don Guillett Microsoft MVP Excel SalesAid Software "Scott" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Data Sort?
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 On Tue, 12 May 2009 14:02:02 -0700, Scott wrote: 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. "Don Guillett" wrote: You can use a worksheet_change event macro in the sheet module but do you really want to sort with EACH entry? -- Don Guillett Microsoft MVP Excel SalesAid Software "Scott" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Data Sort?
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 - gsnu200852 "Scott" wrote: What about if there are duplicates? "Gary''s Student" wrote: Easy if there are no duplicates: =SMALL(A$1:A$144,ROW()) and copy down -- Gary''s Student - gsnu200852 "Scott" wrote: 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Data Sort?
Hello Scott,
I can offer two approaches: http://www.sulprobil.com/html/sort_vba.html http://www.sulprobil.com/html/sorting.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic Data Sort? | Excel Discussion (Misc queries) | |||
Workbook - automatic sort of data | Excel Discussion (Misc queries) | |||
Automatic sort | Excel Discussion (Misc queries) | |||
Automatic Sort | Excel Discussion (Misc queries) | |||
automatic sort | Excel Worksheet Functions |