Go Back   TechArena Community > Technical Support > Computer Help > Office Help > Office Setup
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to enable Auto Sort in Excel

Office Setup


Reply
 
Thread Tools Search this Thread
  #1  
Old 08-12-2010
Member
 
Join Date: Oct 2010
Posts: 70
How to enable Auto Sort in Excel

I have installed the Microsoft Excel 2007 on my Windows XP computer system and I am having a list of data in three sheets. Actually it is a schedule that has be used in my next project which is a football match so you must be having some ideas about the two columns for teams and third one for winning team. I want to have a standings auto sorted whenever people enter their predictions on the sheet.
  • Worksheet 1 has a number of predictions and right of the same is blank where team will come.
  • Worksheet 2 would be just having formulas.
  • worksheet 3 contains some standings which are not sorted in the table.
Now I want to sort them by points initially if suppose there is a tie between teams then it should be sorted by Goal Difference.

Reply With Quote
  #2  
Old 08-12-2010
Member
 
Join Date: May 2008
Posts: 979
Re: How to enable Auto Sort in Excel

It is quite possible with a VBA macro. The table that you want to sort, you name it "newsorted". The cell as the first sort column named "column1", the cell under the second sort column is named "column2" and so on...All these names are examples used in the macro course! you put this macro in the sheet in question and whenever a cell changes value table is sorted.

Code:
Private Sub Worksheet_Change (ByVal adrcel As Range) 
Range ("Information"). Sort Key1: = Range ("column1") Order1: = xlAscending, _ 
Key2: = Range ("column2") ORDER2: = xlAscending, _ 
Key3: = Range ("column3") ORDER3: = xlAscending, _ 
Header: = xlYes, OrderCustom: = 1, MatchCase: = False, _ 
Orientation: = xlTopToBottom 
End Sub
It is obvious that this is a working example but it can be improved by ensuring that the modified cell is actually part of the table for example.
Reply With Quote
  #3  
Old 08-12-2010
Member
 
Join Date: May 2008
Posts: 913
Re: How to enable Auto Sort in Excel

Sorting can be done through 3 criteria, free to choose. The first question is Line Title (yes or no). This is important because if q'en title line, the first line will not be sorted. Generally, Excel provides by default, but better check if applicable. Instead, if you have not created a header, you must tick No. The first screening will serve as a sort of beginning. The sorting will be by the code of the company. The button option in some cases to sort on the days or months. It remains for us to accept the sort.
Reply With Quote
  #4  
Old 08-12-2010
Member
 
Join Date: Mar 2009
Posts: 1,360
Re: How to enable Auto Sort in Excel

Filtering records will allow us to display only a few lines of our client list. On the Data menu, select the command "Filter", followed by the "AutoFilter". This command will change the presentation of our Excel. For each column of our list, we can now select all or the first 10 or 1 value. We can even use more complex criteria on the value of the cell. You can even use two different criteria using the controls AND (all conditions must be met) or OR (at least 1 of the test must be completed). If filtering is performed on a column, the arrow was put in blue. Filters are cumulative. To remove the filter, use the command again AutoFilter on the Data menu. Order Filter: View all of the menu to delete the filters made, but retains the possibility of automatic filter. With this feature, you can also sort the records. Filter Excel displays only the filtered records but sorts the entire range of data.
Reply With Quote
  #5  
Old 08-12-2010
Member
 
Join Date: Nov 2008
Posts: 1,259
Re: How to enable Auto Sort in Excel

Simply use the following array formula:
  • = LARGE (range, ROW (INDIRECT ("A1: A" & (ROWS (range) * COLUMNS (range)
  • ))))

To use this formula, we must, first, select a matrix of cells whose size is equal to that of the matrix named "Beach" and confirm this formula using the key combination + + <CTRL> <SHIFT> <ENTER>.
NOTES: For a list of values in ascending order, simply use the SMALL function instead of function LARGE then formula then becomes:
  • = SMALL (range, ROW (INDIRECT ("A1: A" & (ROWS (range) * COLUMNS (range))
  • )))
Reply With Quote
  #6  
Old 08-12-2010
Member
 
Join Date: Nov 2008
Posts: 1,185
Re: How to enable Auto Sort in Excel

Here's a formula that may help:
  • = LARGE ($ A $ 4: $ A $ 103, 1)
  • then
  • = LARGE ($ A $ 4: $ A $ 103, 2)
  • etc. ...
You can thus identify the best 10, and from there find matching names with VLOOKUP or INDEX-MATCH. That would be pretty simple if there were ever Exaequo! To remedy this, you must create an additional column that will contain, for example, the note multiplied by 1000 plus the line number. This will eliminate duplication and you'll get a top 10 reliable.
Reply With Quote
  #7  
Old 10-12-2010
Member
 
Join Date: Nov 2008
Posts: 1,054
Re: How to enable Auto Sort in Excel

  1. Sort rows based on several criteria
  2. It can sometimes be useful to classify them according to several criteria: first by borrowing date, then alphabetically by surname and then by name. In this case, go to the menu Data> Sort. Select the first criterion: 'Date of borrowing' (default Excel uses the column headings). You can choose to present the most recent on top. To do this, choose 'Descending'. Select the name (ascending order), then first name (idem).
  3. Note: By default, Excel assumes the first row as column heading. If this was not the case, you can select No for My data range has a title line. The first line will be sorted as the others. Click OK, then lines are automatically organized with the most recent dates at the top of the list.
  4. Sort columns rather than rows
  5. Excel also gives you the ability to sort your columns in a certain order rather than your lines. To do this, go to the menu Data> Sort. Click the Options button. Under Orientation, choose from left to right. Select your sort criterion, for example line # 2 in our case to sort the column alphabetically. Click OK. The columns were displaced to be arranged in alphabetical order.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > Office Setup
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to enable Auto Sort in Excel"
Thread Thread Starter Forum Replies Last Post
Is it possible to set auto-refesh for column which has a auto filter in Excel RopeME MS Office Support 2 17-02-2012 07:05 PM
How to sort the name in Excel Sanju Baba Windows Software 1 06-01-2012 09:53 PM
How to Sort Excel Worksheets Alphabetically Capers Windows Software 2 19-10-2011 12:14 PM
Sort a row or column in MS Excel Itronix Tips & Tweaks 1 15-06-2011 09:52 AM
Cannot sort icons in Windows 7 using auto arrange? Harigopal Windows Software 3 02-12-2010 05:23 AM


All times are GMT +5.5. The time now is 11:49 AM.