Author: Wolfe Stephen S GS-11 6 MDSS/SGSI
Posted: 2005-03-03 10:56:14 Link
For those who are Excel gurus ...
I have a user that has a simple 7 column spreadsheet, nothing fancy and
that individual
Wants to have the spreadsheet maintain a two level sort. The first
level on the 'organization'
Column and the second level on the 'grade' column.
How do you do that?
TIA
v/r
//SIGNED//
Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
(813) 827-9972 DSN 651-9972
Tools / Sort / by column ?, and then by column ?
Or programmatically :-
o.Range("a1:c13").Sort(o.Range("a2"), 1)
(Easiest way to find out is to record a macro in excel and do what you
want - then look at the macro)
HTH,
Robbo.
-----Original Message-----
From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]
On Behalf Of Wolfe Stephen S GS-11 6 MDSS/SGSI
Sent: 03 March 2005 15:56
To: profoxtech@leafe.com
Subject: [NF] -- Excel sorting question
For those who are Excel gurus ...
I have a user that has a simple 7 column spreadsheet, nothing fancy and
that individual Wants to have the spreadsheet maintain a two level sort.
The first level on the 'organization' Column and the second level on the
'grade' column.
How do you do that?
TIA
v/r
//SIGNED//
Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
(813) 827-9972 DSN 651-9972
OT-free version of this list:
http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of
the author, and do not constitute legal or medical advice. This
statement is added to the messages for those lawyers who are too stupid
to see the obvious.
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
Rob Anderson wrote:
>Tools / Sort / by column ?, and then by column ?
>
>Or programmatically :-
>
>o.Range("a1:c13").Sort(o.Range("a2"), 1)
>
>(Easiest way to find out is to record a macro in excel and do what you
>want - then look at the macro)
>
>HTH,
>Robbo.
>
>
Is this known in the trade as Robohelp?
Peter :-)
Stephen,
Main Menu Data/Sort
Sort by <columnA>
Then by <columnB>
Select header row or no header row as appropriate.
Dave Crozier
DaveC@Replacement-Software.co.uk
"The difference between theory and practice is greater in practice than it
is in theory."
-----Original Message-----
From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf
Of Wolfe Stephen S GS-11 6 MDSS/SGSI
Sent: 03 March 2005 15:56
To: ProFox Email List
Subject: [NF] -- Excel sorting question
For those who are Excel gurus ...
I have a user that has a simple 7 column spreadsheet, nothing fancy and
that individual
Wants to have the spreadsheet maintain a two level sort. The first
level on the 'organization'
Column and the second level on the 'grade' column.
How do you do that?
TIA
v/r
//SIGNED//
Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
(813) 827-9972 DSN 651-9972
[excessive quoting removed by server]
Position active cell in upper left corner of the data block.
Menu, Data, Sort, set first key to 'organization' column, set second
key to 'grade' column. Click OK.
If you want the specific VBA for this, you can record the process as a
macro and get the specifices of the sorting method.
I hope this was what you meant! <g>
Ray <><
------------------------------
This email is intended for the use of the individual or entity to which
it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the
reader of this email message is not the intended recipient, or the
employee or agent responsible for delivery of the message to the
intended recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is prohibited. If you
have received this email in error, please notify us immediately by
telephone at (502) 695-6870 or (888) 749-5799 and also indicate the
sender's name. Thank you.
>>> Stephen.Wolfe@macdill.af.mil 3/3/2005 10:56:14 AM >>>
For those who are Excel gurus ...
I have a user that has a simple 7 column spreadsheet, nothing fancy
and
that individual
Wants to have the spreadsheet maintain a two level sort. The first
level on the 'organization'
Column and the second level on the 'grade' column.
How do you do that?
TIA
v/r
//SIGNED//
Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
(813) 827-9972 DSN 651-9972
[excessive quoting removed by server]
Author: Wolfe Stephen S GS-11 6 MDSS/SGSI
Posted: 2005-03-03 14:22:37 Link
Thanks to all, it seemed pretty simple and I did the solution you all
suggested, showed the user how easy it was and then I got this question.
Now he wants to sort by the social security number (SSN column) but only
the last four. The menu driven way doesn't seem to have a substring
option -- I was hoping 'advanced options' would uncover something but it
didn't.
So, how do you do a partial column sort?
v/r
//SIGNED//
Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
(813) 827-9972 DSN 651-9972
> -----Original Message-----
> From: Ray Heilman [mailto:RHEILMAN@mslc.com]
> Sent: Thursday, March 03, 2005 11:34 AM
> To: profox@leafe.com
> Subject: Re: [NF] -- Excel sorting question
>
> Position active cell in upper left corner of the data block.
>
> Menu, Data, Sort, set first key to 'organization' column, set
> second key to 'grade' column. Click OK.
>
> If you want the specific VBA for this, you can record the
> process as a macro and get the specifices of the sorting method.
>
> I hope this was what you meant! <g>
>
> Ray <><
>
> ------------------------------
> This email is intended for the use of the individual or
> entity to which it is addressed and may contain information
> that is privileged, confidential and exempt from disclosure
> under applicable law. If the reader of this email message is
> not the intended recipient, or the employee or agent
> responsible for delivery of the message to the intended
> recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is prohibited.
> If you have received this email in error, please notify us
> immediately by telephone at (502) 695-6870 or (888) 749-5799
> and also indicate the sender's name. Thank you.
>
>
> >>> Stephen.Wolfe@macdill.af.mil 3/3/2005 10:56:14 AM >>>
> For those who are Excel gurus ...
>
> I have a user that has a simple 7 column spreadsheet, nothing
> fancy and that individual Wants to have the spreadsheet
> maintain a two level sort. The first level on the 'organization'
> Column and the second level on the 'grade' column.
>
> How do you do that?
>
> TIA
>
> v/r
>
> //SIGNED//
> Stephen S. Wolfe, GS-11, DAFC
> Data Services Manager
> (813) 827-9972 DSN 651-9972
>
>
>
>
[excessive quoting removed by server]
You could try parsing the SSN into 2 columns.
The last four numbers in the second columns.
Then sort on that new column.
-----Original Message-----
From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On
Behalf Of Wolfe Stephen S GS-11 6 MDSS/SGSI
Sent: Thursday, March 03, 2005 1:23 PM
To: profoxtech@leafe.com
Subject: RE: [NF] -- Excel sorting question
Thanks to all, it seemed pretty simple and I did the solution you all
suggested, showed the user how easy it was and then I got this question.
Now he wants to sort by the social security number (SSN column) but only
the last four. The menu driven way doesn't seem to have a substring
option -- I was hoping 'advanced options' would uncover something but it
didn't.
So, how do you do a partial column sort?
v/r
//SIGNED//
Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
(813) 827-9972 DSN 651-9972
> -----Original Message-----
> From: Ray Heilman [mailto:RHEILMAN@mslc.com]
> Sent: Thursday, March 03, 2005 11:34 AM
> To: profox@leafe.com
> Subject: Re: [NF] -- Excel sorting question
>
> Position active cell in upper left corner of the data block.
>
> Menu, Data, Sort, set first key to 'organization' column, set
> second key to 'grade' column. Click OK.
>
> If you want the specific VBA for this, you can record the
> process as a macro and get the specifices of the sorting method.
>
> I hope this was what you meant! <g>
>
> Ray <><
>
> ------------------------------
> This email is intended for the use of the individual or
> entity to which it is addressed and may contain information
> that is privileged, confidential and exempt from disclosure
> under applicable law. If the reader of this email message is
> not the intended recipient, or the employee or agent
> responsible for delivery of the message to the intended
> recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is prohibited.
> If you have received this email in error, please notify us
> immediately by telephone at (502) 695-6870 or (888) 749-5799
> and also indicate the sender's name. Thank you.
>
>
> >>> Stephen.Wolfe@macdill.af.mil 3/3/2005 10:56:14 AM >>>
> For those who are Excel gurus ...
>
> I have a user that has a simple 7 column spreadsheet, nothing
> fancy and that individual Wants to have the spreadsheet
> maintain a two level sort. The first level on the 'organization'
> Column and the second level on the 'grade' column.
>
> How do you do that?
>
> TIA
>
> v/r
>
> //SIGNED//
> Stephen S. Wolfe, GS-11, DAFC
> Data Services Manager
> (813) 827-9972 DSN 651-9972
>
>
>
>
[excessive quoting removed by server]
Stephen,
> So, how do you do a partial column sort?
The way I'd do it is to make a new column that is a substring of the SSN,
then sort on that new column.
Kris
Chance favors the prepared mind -- Louis Pasteur
Wolfe Stephen S GS-11 6 MDSS/SGSI wrote:
>Thanks to all, it seemed pretty simple and I did the solution you all
>suggested, showed the user how easy it was and then I got this question.
>
>Now he wants to sort by the social security number (SSN column) but only
>the last four. The menu driven way doesn't seem to have a substring
>option -- I was hoping 'advanced options' would uncover something but it
>didn't.
>
>So, how do you do a partial column sort?
>
>v/r
>
>
Sounds like you either need to have a temporary column containing only
the last four digits and sort on that, or use VBA.
Stuart
Use string functions light RIGHT(<cell referenc>, 4) to produce a
derived column at the end of the block, and include it in the sort.
Remove when no longer needed.
Ray <><
------------------------------
This email is intended for the use of the individual or entity to which
it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the
reader of this email message is not the intended recipient, or the
employee or agent responsible for delivery of the message to the
intended recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is prohibited. If you
have received this email in error, please notify us immediately by
telephone at (502) 695-6870 or (888) 749-5799 and also indicate the
sender's name. Thank you.
>>> Stephen.Wolfe@macdill.af.mil 3/3/2005 2:22:37 PM >>>
Thanks to all, it seemed pretty simple and I did the solution you all
suggested, showed the user how easy it was and then I got this
question.
Now he wants to sort by the social security number (SSN column) but
only
the last four. The menu driven way doesn't seem to have a substring
option -- I was hoping 'advanced options' would uncover something but
it
didn't.
So, how do you do a partial column sort?
v/r
//SIGNED//
Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
(813) 827-9972 DSN 651-9972
> -----Original Message-----
> From: Ray Heilman [mailto:RHEILMAN@mslc.com]
> Sent: Thursday, March 03, 2005 11:34 AM
> To: profox@leafe.com
> Subject: Re: [NF] -- Excel sorting question
>
> Position active cell in upper left corner of the data block.
>
> Menu, Data, Sort, set first key to 'organization' column, set
> second key to 'grade' column. Click OK.
>
> If you want the specific VBA for this, you can record the
> process as a macro and get the specifices of the sorting method.
>
> I hope this was what you meant! <g>
>
> Ray <><
>
> ------------------------------
> This email is intended for the use of the individual or
> entity to which it is addressed and may contain information
> that is privileged, confidential and exempt from disclosure
> under applicable law. If the reader of this email message is
> not the intended recipient, or the employee or agent
> responsible for delivery of the message to the intended
> recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is prohibited.
> If you have received this email in error, please notify us
> immediately by telephone at (502) 695-6870 or (888) 749-5799
> and also indicate the sender's name. Thank you.
>
>
> >>> Stephen.Wolfe@macdill.af.mil 3/3/2005 10:56:14 AM >>>
> For those who are Excel gurus ...
>
> I have a user that has a simple 7 column spreadsheet, nothing
> fancy and that individual Wants to have the spreadsheet
> maintain a two level sort. The first level on the 'organization'
> Column and the second level on the 'grade' column.
>
> How do you do that?
>
> TIA
>
> v/r
>
> //SIGNED//
> Stephen S. Wolfe, GS-11, DAFC
> Data Services Manager
> (813) 827-9972 DSN 651-9972
>
>
>
>
[excessive quoting removed by server]