[RESOLVED] NEW QUESTION Excel -- Sort Multiple columns

[kfcSmitty]

See post #9





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


IE)

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

[si_the_geek]

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 ):

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)

[kfcSmitty]

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.

[si_the_geek]

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

[kfcSmitty]

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

[RobDog888]

Did you change all the key ranges to 2?

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

[kfcSmitty]

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

[si_the_geek]

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.

[kfcSmitty]

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?