Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Auto Sort A List by Values in Excel

This is a discussion on Auto Sort A List by Values in Excel within the Excel Questions forums, part of the Question Forums category; Hi fom Sydney! Is there a way in Excel to have a list of names with values automatically re-sort whenever ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Location
    Sydney, Australia
    Posts
    15

    Default Auto Sort A List by Values in Excel

    Hi fom Sydney!

    Is there a way in Excel to have a list of names with values automatically re-sort whenever any of the values change.

    The default sort I have in mind is ascending by values.

    eg.

    CITY VALUE
    Sydney 100
    Boston 200
    London 300
    Moscow 400
    Houston 500

    The values change to:

    Sydney 5000
    Boston 250
    London 8000
    Moscow 300
    Houston 50

    I now want Houston 1st, Boston 2nd etc - automatically - without me having to do a sort again by Value.

    Any help with this highly apreciated.

    Andrew Lawless
    Sydney - Australia

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: Auto Sort A List by Values in Excel

    Assuming "City" and "Value" are headers for separate columns, maybe A and B on row 1, then try this:

    Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Or Target.Cells.count > 1 Then Exit Sub
    Dim SortRange As Range
    Set SortRange = Range(("A1"), Cells(Rows.count, 2).End(xlUp))
    SortRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
    End Sub


    When you enter a new number in colum B, the range will sort itself.

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Auto Sort A List by Values in Excel

    Hi Andrew,

    Using Excel XP

    The following macro assumes that your data is in A1:B12, with the headings “CITY” and “VALUE” in cells A1 and B1 respectively (change to suit).

    Put this macro in the appropriate worksheet module.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Column = 2 Then
          Range("A2:B12").Sort _
           Key1:=Range("B2"), Order1:=xlAscending, _
             Key2:=Range("A2"), Order2:=xlAscending, _
             Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
             Orientation:=xlTopToBottom
         End If
       
    End Sub
    The macro will sort values first, then by city i.e. if two cities have the same value, the macro will sort the cities alphabetically.

    HTH

    Mike

  4. #4
    New Member
    Join Date
    Dec 2002
    Location
    Sydney, Australia
    Posts
    15

    Default Re: Auto Sort A List by Values in Excel

    Thanks heaps!

    If my City column was 'B' and my Value column 'L', how would I modify the VB to account for this?

    Andrew

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: Auto Sort A List by Values in Excel

    You should specify the entire column range you want sorted. Is column A involved? Column DG? What is the actual column involvement that you want included?

  6. #6
    New Member
    Join Date
    Dec 2002
    Location
    Sydney, Australia
    Posts
    15

    Default Re: Auto Sort A List by Values in Excel

    Columns A through to L.
    The value data to sort by is in L.
    The data goes down to row 8 in this example.
    There is a merged cell in row 9 that extends all the way to column L (not sure if this presents a problem).
    Thanks again
    Andrew

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: Auto Sort A List by Values in Excel

    I hate merged cells.

    See if this does what you want, in place of the first one I sent:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 12 Or Target.Cells.count > 1 Then Exit Sub
    Dim SortRange As Range
    Set SortRange = Range(("A1"), Cells(Rows.count, 12).End(xlUp))
    SortRange.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes
    End Sub

  8. #8
    New Member
    Join Date
    Dec 2002
    Location
    Sydney, Australia
    Posts
    15

    Default Re: Auto Sort A List by Values in Excel

    Beautifully Tom!

    One more twist....

    what if I wanted the range to sort 1st by column L and then by column K, both ascending!

    Andrew

  9. #9
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

    Default Re: Auto Sort A List by Values in Excel

    What should trigger the sort? An entry in K? An entry in L? An entry anywhere? When the sheet activates? Details please.

  10. #10
    New Member
    Join Date
    Dec 2002
    Location
    Sydney, Australia
    Posts
    15

    Default Re: Auto Sort A List by Values in Excel

    Any changes in L.

    Column L cells contain VLOOKUPs that reference to another sheet in the workbook. Is this an issue? Manual changes are made to the cells in the other worksheet, which then reflect in changes to L.

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com