[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...
Re: Excel -- Sort Multiple columns
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)
Re: Excel -- Sort Multiple columns
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.
Re: Excel -- Sort Multiple columns
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. )
Re: Excel -- Sort Multiple columns
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...
Re: Excel -- Sort Multiple columns
Did you change all the key ranges to 2?
VB Code:
xlApp.Selection.Sort Key1:=xlSheet.Range("A2")...
Re: Excel -- Sort Multiple columns
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
Re: Excel -- Sort Multiple columns
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.
Re: Excel -- Sort Multiple columns
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?