Can someone help me Auto Sort in Excel?

progpeps · 05-22-2008, 06:26 PM

Hi everyone,

I was wondering if someone can help me autosort a list of data.

What I have is the schedule for the upcoming European Cup of Football.

so I would like to have the standings auto sorted when people enter their predictions on the sheet...

Worksheet 1 has the predictions and on the right it has a blank table for the teams to go there... (Where I want the sorted data to go)

Worksheet 2 is just a bunch of formulas

worksheet 3 has the standings but not sorted in the table...

I would like it sorted by points first, and if their is a tie then sorted by GD (goal differential)

any help would be great :)

Thank a lot guys

David M58 · 05-22-2008, 08:45 PM

Here are some tips that may help you get started. If, for example, you wanted to sort the "Group A" table on the third worksheet in your Excel file, you would select that worksheet, then select the appropriate range (E3:M7 in this example), go to the Excel menu and select Data and then Sort, select the Header row option, sort by Pts descending (or ascending), then by GD descending (or ascending).

If you wanted to perform that same operation using a macro, here's some sample code:

Code:

Sub DoSort()
    Dim ws As Worksheet
    Set ws = Worksheets(3)
    ws.Range("E3:M7").Sort Key1:=ws.Range("M3"), Order1:=xlDescending, _
        Key2:=ws.Range("L3"), Order2:=xlDescending, Header:=xlYes
End Sub

Modify as necessary.

progpeps · 05-22-2008, 09:53 PM

thank you...

is there any way to get it to update without hitting the pay button on the macro?

unless i'm doing something wrong here...

like how do I get it to autmatically sort without running the macro again?

David M58 · 05-23-2008, 12:34 AM

Excel gives you several ways to automatically perform tasks. You could, for example, set it up to do that sorting subroutine every time someone enters data on the first worksheet. Or you can have it automatically perform the sorting right before the workbook is saved. Or you can have it do the sorting when the workbook is first opened. And, of course, you can have the sorting done when someone clicks on a command button, or when someone presses a keystroke combination, such as Ctrl-e.

First, you should decide how you want to handle the sorting. Do you want it to sort when someone types in a number? Or when someone saves the workbook? You have a number of choices.

progpeps · 05-23-2008, 06:58 AM

I would like it to sort when you enter a number in any of the cells E9:F32

say you enter

E F
1(it sorts) 5(it sorts)

Thanks

David M58 · 05-23-2008, 09:46 AM

Here's one way to set it up. In Excel, press Alt-F11 to get to the Visual Basic Editor, then Ctrl-R to display the Project Explorer. In the Project Explorer, double-click the object Sheet1 (EC Fixtures) to open the code window. Put in the following code.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Worksheets(1).Range("E9:F32"), Target) Is Nothing) Then
        DoSort
    End If
End Sub

Private Sub DoSort()
    Worksheets(3).Range("E3:M7").Sort Key1:=Worksheets(3).Range("M3"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L3"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E10:M14").Sort Key1:=Worksheets(3).Range("M10"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L10"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E17:M21").Sort Key1:=Worksheets(3).Range("M17"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L17"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E24:M28").Sort Key1:=Worksheets(3).Range("M24"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L24"), Order2:=xlDescending, Header:=xlYes
End Sub

Now, whenever you make a new entry in any cell in the range E9:F32 on the first worksheet, it will perform that sorting subroutine on the third worksheet. Modify as necessary.

progpeps · 05-23-2008, 10:13 AM

thank you so much Dave...

I was trying that other function as well but I was missing the If Not, so it wasn't calling to DoSort function

Thank for you help d00d

:)!!!!!!

David M58 · 05-23-2008, 10:41 AM

Glad to help!

KirKanu · 08-29-2009, 07:44 AM

Great! This thread was of huge help

Mine works too!

Thank you big time!

PS: Hmm... Now I have to do all those matches and groups

sschreib · 09-10-2009, 11:36 AM

I tried the code above but got this error: "Run-time error '1004: Application-defined or object-defined error". Any suggestions? Here is the code that worked for me in excel 2003, it will not work either:
Private Sub Worksheet_Calculate()
Range("A1:C141").Sort _
Key1:=Range("B2"), _
Order1:=xlAscending, _
Key2:=Range("A2"), _
Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

05-22-2008, 08:45 PM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Can someone help me Auto Sort in Excel? Here are some tips that may help you get started. If, for example, you wanted to sort the "Group A" table on the third worksheet in your Excel file, you would select that worksheet, then select the appropriate range (E3:M7 in this example), go to the Excel menu and select Data and then Sort, select the Header row option, sort by Pts descending (or ascending), then by GD descending (or ascending). If you wanted to perform that same operation using a macro, here's some sample code: Code: Sub DoSort() Dim ws As Worksheet Set ws = Worksheets(3) ws.Range("E3:M7").Sort Key1:=ws.Range("M3"), Order1:=xlDescending, _ Key2:=ws.Range("L3"), Order2:=xlDescending, Header:=xlYes End Sub Modify as necessary.

05-22-2008, 09:53 PM	#3 (permalink)
progpeps Registered User Join Date: May 2008 Posts: 4 OS: XP	Re: Can someone help me Auto Sort in Excel? thank you... is there any way to get it to update without hitting the pay button on the macro? unless i'm doing something wrong here... like how do I get it to autmatically sort without running the macro again? Last edited by progpeps; 05-22-2008 at 10:19 PM.

05-23-2008, 12:34 AM	#4 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Can someone help me Auto Sort in Excel? Excel gives you several ways to automatically perform tasks. You could, for example, set it up to do that sorting subroutine every time someone enters data on the first worksheet. Or you can have it automatically perform the sorting right before the workbook is saved. Or you can have it do the sorting when the workbook is first opened. And, of course, you can have the sorting done when someone clicks on a command button, or when someone presses a keystroke combination, such as Ctrl-e. First, you should decide how you want to handle the sorting. Do you want it to sort when someone types in a number? Or when someone saves the workbook? You have a number of choices.

05-23-2008, 06:58 AM	#5 (permalink)
progpeps Registered User Join Date: May 2008 Posts: 4 OS: XP	Re: Can someone help me Auto Sort in Excel? I would like it to sort when you enter a number in any of the cells E9:F32 say you enter E F 1(it sorts) 5(it sorts) Thanks

05-23-2008, 10:13 AM	#7 (permalink)
progpeps Registered User Join Date: May 2008 Posts: 4 OS: XP	Re: Can someone help me Auto Sort in Excel? thank you so much Dave... I was trying that other function as well but I was missing the If Not, so it wasn't calling to DoSort function Thank for you help d00d :)!!!!!!

05-23-2008, 10:41 AM	#8 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Can someone help me Auto Sort in Excel? Glad to help!

09-10-2009, 11:36 AM	#10 (permalink)
sschreib Registered User Join Date: Sep 2009 Posts: 1 OS: xp	Re: Can someone help me Auto Sort in Excel? I tried the code above but got this error: "Run-time error '1004: Application-defined or object-defined error". Any suggestions? Here is the code that worked for me in excel 2003, it will not work either: Private Sub Worksheet_Calculate() Range("A1:C141").Sort _ Key1:=Range("B2"), _ Order1:=xlAscending, _ Key2:=Range("A2"), _ Order2:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End Sub