[RESOLVED] NEW QUESTION Excel -- Sort Multiple columns

See post #9 :)





Is it possible to sort by multiple by multiple columns in excel?


IE)

Code:

---

Oper Unit        Dept        Program Code        Account        Name                Staff ID        Start Date        End Date        Amount                Subtotals
                                                                       
03                05        11003                40100        Doe, John        00000000        4/1/2005        3/31/2006        40,000.00       
03                05        11003                42000        Smith, Leo        00000001        4/1/2005        8/31/2005        40,000.00        80,000.00
03                05        11003                42000        Doe, Jane        00000002        4/1/2005        3/31/2006        40,000.00       
03                05        11003                42000        Smith, Sam        00000003        4/1/2005        3/31/2006        40,000.00        80,000.00
03                05        11006                40100        Wayne, Adam        E8587900        4/1/2005        3/31/2006        40,000.00       
03                05        11006                40100        Welch, Robert        75899079        4/1/2005        3/31/2006        40,000.00        80,000.00
04                02        80000                42000        Doe, Pat        H8KK5970        4/1/2005        3/31/2006        40,000.00        40,000.00
04                05        90400                42000        Smith, Tom        etc                4/1/2005        3/31/2006        40,000.00        40,000.00
04                06        01000                40100        Smith, Alex        etc                4/1/2005        3/31/2006        40,000.00        40,000.00
04                06        01000                42000        Smith, Leo        etc                9/1/2005        3/31/2006        40,000.00        40,000.00
05                13        13003                42500        Allan, Patricia        etc                4/1/2005        3/31/2006        40,000.00        40,000.00
05                14        02004                42000        Adams, John        etc                4/1/2005        3/31/2006        40,000.00        40,000.00

---

I need to sory by Operating unit first.

Then within each Operating Unit I need to have the department, the program code and the account sorted. And each time any of them change, I have to have a subtotal on the far right...


I have NO idea where to start with this since I rarely use Excel.

also, I am trying to print this report from Access...so all Excel options may not be available to me...

How are you working with Excel? Is this using VBA code, with an object variable containing the Excel application?

If so, all functionality is available to you, and you can use code like the following (slight modification of a macro I just recorded ;) ):

VB Code:

xlApp.Selection.Sort Key1:=xlSheet.Range("A1"), Order1:=xlAscending, Key2:=xlSheet.Range("C1") _
        , Order2:=xlAscending, Key3:=xlSheet.Range("D1"), Order3:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

where xlApp is the variable containing the Application object,
and xlSheet is the variable containing the WorkSheet object (if you don't have one, we can work around it)

cool, I'll give it a try. I can record a macro using excel and it'll gimme code? sweet.

and yeah, I am opening the worksheet as an object.

Good stuff :)

When you record a macro, it is written in VBA code for you, so you can almost copy & paste it into your program. You just need to precede all objects with their appropriate parents (in the example above I added xlApp. and xlSheet. )

I've been messing around with this..and it works beautifly, thanks :).

But I can't seem to have it omit the first line. I want it to start sorting on row 2 and down. I tried changing the ranges to 2 instead of 1, but to no avail.

I also tried selecting row 2..but I don't know how to select from row 2 and down...

Did you change all the key ranges to 2?

VB Code:

xlApp.Selection.Sort Key1:=xlSheet.Range("A2")...

yes, that didn't do anything...I don't know what they do..I changed them from 1 to 2, to 3 and it always gave me the same result

It should be ok if you change the Header parameter from xlGuess to xlYes

If it is xlGuess then Excel will try to work out if you have a header or not, and it seems to be making the wrong decision.

hrm..Its working now

I didn't change anything in that line..maybe my code was skipping it...Thanks :)


With the sort working and all..I have been trying to get it so everytime any # changes..it does a subtotal.

If you look at the chart I posted earlier, there are examples.

The only way Ive been able to get even half a result has been through IF statements...

IE

If NewAccount <> OldAccount Then
blah blah blah

But, I also need a range so I know what to add together into the subtotal...


Is there an easy excel formula to do this?