Results 1 to 7 of 7

Thread: How to enable Auto Sort in Excel

  1. #1
    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.

  2. #2
    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.

  3. #3
    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.

  4. #4
    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.

  5. #5
    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))
    • )))

  6. #6
    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.

  7. #7
    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.

Similar Threads

  1. Replies: 2
    Last Post: 17-02-2012, 08:05 PM
  2. How to sort the name in Excel
    By Sanju Baba in forum Windows Software
    Replies: 1
    Last Post: 06-01-2012, 10:53 PM
  3. How to Sort Excel Worksheets Alphabetically
    By Capers in forum Windows Software
    Replies: 2
    Last Post: 19-10-2011, 12:14 PM
  4. Sort a row or column in MS Excel
    By Itronix in forum Tips & Tweaks
    Replies: 1
    Last Post: 15-06-2011, 09:52 AM
  5. Cannot sort icons in Windows 7 using auto arrange?
    By Harigopal in forum Windows Software
    Replies: 3
    Last Post: 02-12-2010, 06:23 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,724,346.91723 seconds with 17 queries