Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Automatically Sorting as You Enter Information.
Written by Allen Wyatt (last updated August 14, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
Pat wonders if there is a way to automatically sort every time she adds new data to a worksheet. Pat thinks it would be great, for instance, that when she adds a new name to a list of names that the names are automatically sorted to always be in order.
The only way that this can be done is by using a macro that is triggered whenever something new is entered in the worksheet. You can, for instance, add a macro to the code for a worksheet that is triggered when something in the worksheet changes. (You can view the code window by right-clicking the worksheet tab and choosing View Code from the resulting Context menu.) The following is an example of one such simple macro:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Range("A1").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End Sub
The macro assumes that you want to sort on the data in column A and that there is a header in cell A1. If the names are in a different column, just change the cell A2 reference to a different column, such as B2, C2, etc.
Of course, sorting anytime that any change is made can be bothersome. You might want to limit when the sorting is done so that it only occurs when changes are made to a specific portion of your data. The following version of the macro sorts the data only when a change is made in column A.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("A:A")) Is Nothing Then Range("A1").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End Sub
There are some drawbacks to using a macro to automatically sort your data. First, since you are using a macro to sort, the operation is essentially "final." In other words, after the sorting you can't use Ctrl+Z to undo the operation.
A second drawback is that data entry might become a bit disconcerting. For instance, if you use any of the above macros and you start to put names into the worksheet, they will be sorted as soon as you finish what is in column A. If your data uses five columns and you start your entry in row 15, as soon as you get done entering the name into column A (and before you enter data into columns B through E), your data is sorted into the proper order. This means that you will need to find where it was moved in the sort, select the proper cell in column B, and then enter the rest of the data for the record. Of course, the way around this is to add your data in an unnatural order—simply make sure that the name in column A is the very last thing you enter for the record.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9005) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Sorting as You Enter Information.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Information in a cell can be entered using line feeds, which results in multiple lines of data in the same cell. If you ...
Discover MoreWhen you sort data that contains both numbers and text, you may not get exactly the result that you expected. To know ...
Discover MoreWant to sort addresses by even and odd numbers? By using a formula and doing a little sorting, Excel can return the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-10-23 13:14:59
Michael
Matilda,
You need to use a PivotTable. Hopefully this long YouTube vid link works for you, otherwise just do a new search:-
https://www.google.com/search?q=pivot+table+different+sorts+in+same+column&oq=pivot+table+different+sorts+in+same+column+&gs_lcrp=EgZjaHJvbWUyBggAEEUYOTIHCAEQIRigATIHCAIQIRigATIHCAMQIRigATIHCAQQIRigATIHCAUQIRifBTIHCAYQIRifBTIHCAcQIRifBTIHCAgQIRifBTIHCAkQIRifBdIBCjMwOTg3ajBqMTWoAgiwAgE&sourceid=chrome&ie=UTF-8#fpstate=ive&vld=cid:badbed3f,vid:sDhjmB8a-x0,st:9
Michael
2024-10-16 11:49:06
Matilda
Hi,
I have used the above code to auto-sort all of column C but I need it so it sorts within the 4 'groupings' in column B rather than the whole colunm.
So column B has the types, BP from B8 - B82, SPON from B83 - B90, TP from B91 - B165 and TPAD from B166 - B175. We will be entering the company names into column C which will need sorting. So I need a code or codes that it only auto-sorts within their ranges.
I hope I'm making sense.
Matilda
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments