sorting columns
 

Hello,
I am writing a program that needs to get data. I will be combining a bunch
of tables into one table. It might have about 8 columns. Programmatically
I need to sort the columns. Not just a a single column. But by all the
columns. I looked at this manual sort soln:
http://support.microsoft.com/kb/268007

Is a pivot table the way to go?
Or just run a sort with lots of Key<num=<location
Range("A40:D48").Select
Selection.Sort Key1:=Range("C40"), Order1:=xlAscending,
Key2:=Range("B40" _
), Order2:=xlAscending, Key3:=Range("A40"), Key3:=Range("D40"),
Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
thanks for any input

sorting columns
 

When sorting within VBA, you can do all 8 columns, though a bit tricky as it
won't be all done in one go. The way to do it is to first have 7th field as
the Key1 and 8th field as Key2. Next round of sort, have 4th field as Key1,
5th field as Key2, and 6th field as Key3, then on the last sort, have 1st
field as Key1, 2nd field as Key2, and 3rd field as Key3.

Yes, you are in a sense going backwards, but it's about the only way to be
able to do it without having to create your own custom code to handle it.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"greg" wrote in message
...
Hello,
I am writing a program that needs to get data. I will be combining a
bunch of tables into one table. It might have about 8 columns.
Programmatically I need to sort the columns. Not just a a single column.
But by all the columns. I looked at this manual sort soln:
http://support.microsoft.com/kb/268007

Is a pivot table the way to go?
Or just run a sort with lots of Key<num=<location
Range("A40:D48").Select
Selection.Sort Key1:=Range("C40"), Order1:=xlAscending,
Key2:=Range("B40" _
), Order2:=xlAscending, Key3:=Range("A40"), Key3:=Range("D40"),
Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
thanks for any input

sorting columns
 

thanks,
So it is not possible or better to use a pivot table?


"Ronald R. Dodge, Jr." wrote in message
...
When sorting within VBA, you can do all 8 columns, though a bit tricky as
it won't be all done in one go. The way to do it is to first have 7th
field as the Key1 and 8th field as Key2. Next round of sort, have 4th
field as Key1, 5th field as Key2, and 6th field as Key3, then on the last
sort, have 1st field as Key1, 2nd field as Key2, and 3rd field as Key3.

Yes, you are in a sense going backwards, but it's about the only way to be
able to do it without having to create your own custom code to handle it.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"greg" wrote in message
...
Hello,
I am writing a program that needs to get data. I will be combining a
bunch of tables into one table. It might have about 8 columns.
Programmatically I need to sort the columns. Not just a a single column.
But by all the columns. I looked at this manual sort soln:
http://support.microsoft.com/kb/268007

Is a pivot table the way to go?
Or just run a sort with lots of Key<num=<location
Range("A40:D48").Select
Selection.Sort Key1:=Range("C40"), Order1:=xlAscending,
Key2:=Range("B40" _
), Order2:=xlAscending, Key3:=Range("A40"), Key3:=Range("D40"),
Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
thanks for any input

sorting columns
 

Sorting and pivottables do different stuff.

Pivottables are used for summaries (totals, averages, counts, ...)

Sorting will just put the data in some order--but you'll still see the details.

Maybe what you really want is Autofilter.

In xl2003 menus:
Select your data (all the data and the single header row)
Data|filter|autofilter

Then you can use the dropdown arrows to look at any value in any of those
columns.

greg wrote:

Hello,
I am writing a program that needs to get data. I will be combining a bunch
of tables into one table. It might have about 8 columns. Programmatically
I need to sort the columns. Not just a a single column. But by all the
columns. I looked at this manual sort soln:
http://support.microsoft.com/kb/268007

Is a pivot table the way to go?
Or just run a sort with lots of Key<num=<location
Range("A40:D48").Select
Selection.Sort Key1:=Range("C40"), Order1:=xlAscending,
Key2:=Range("B40" _
), Order2:=xlAscending, Key3:=Range("A40"), Key3:=Range("D40"),
Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
thanks for any input

--

Dave Peterson

sorting columns
 

As Dave Peterson has said, Pivot Tables is a whole another animal as it does
things entirely different from sorting. Pivot tables are more like Cross
Tables done within a query program using SQL, but only in this case done
within Excel. Not only that, while pivot tables are quite useful for some
small scale stuff (which I tend to think of pivot tables and other similar
stuff to be a quick and dirty type tools), there are some major limitations
with pivot tables too. There's even some undesired behavior of pivot tables
that I have seen too, hence why I tend to use either formulas or VBA codes
instead of pivot tables to do a lot of it's functions.

If you plan on creating your custom sort code, you many want to think about
the following:

First, if you ever had a course on programming dealing with sorting, there's
basically 3 different methods and the one method that stood out as working
most efficient when dealing with larger lists is the merge sort method. So
you will want to set up code to use the merge sort method (For smaller
lists, not going to make that much of a difference time wise anyhow, so no
sense on using either of the other 2 sort methods).

Next, you may want to use collections and/or arrays to help in that process
as a way of being able to store at least references to those different rows
of data. Of course, if you use the key part of collection objects, the
sorting can be done automatically as each item within a collection must have
a different key value. To do the multiple layers of sorts, you will need to
use class modules to create a such sort feature so as you can create new
sort objects within sort objects to create you different layers of sort
orders.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"greg" wrote in message
...
thanks,
So it is not possible or better to use a pivot table?


"Ronald R. Dodge, Jr." wrote in message
...
When sorting within VBA, you can do all 8 columns, though a bit tricky as
it won't be all done in one go. The way to do it is to first have 7th
field as the Key1 and 8th field as Key2. Next round of sort, have 4th
field as Key1, 5th field as Key2, and 6th field as Key3, then on the last
sort, have 1st field as Key1, 2nd field as Key2, and 3rd field as Key3.

Yes, you are in a sense going backwards, but it's about the only way to
be able to do it without having to create your own custom code to handle
it.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"greg" wrote in message
...
Hello,
I am writing a program that needs to get data. I will be combining a
bunch of tables into one table. It might have about 8 columns.
Programmatically I need to sort the columns. Not just a a single
column. But by all the columns. I looked at this manual sort soln:
http://support.microsoft.com/kb/268007

Is a pivot table the way to go?
Or just run a sort with lots of Key<num=<location
Range("A40:D48").Select
Selection.Sort Key1:=Range("C40"), Order1:=xlAscending,
Key2:=Range("B40" _
), Order2:=xlAscending, Key3:=Range("A40"), Key3:=Range("D40"),
Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
thanks for any input

sorting columns
 

thanks for the help.
I wanted to make sure i was not missing some internal functions to help me
out.


"Ronald R. Dodge, Jr." wrote in message
...
As Dave Peterson has said, Pivot Tables is a whole another animal as it
does things entirely different from sorting. Pivot tables are more like
Cross Tables done within a query program using SQL, but only in this case
done within Excel. Not only that, while pivot tables are quite useful for
some small scale stuff (which I tend to think of pivot tables and other
similar stuff to be a quick and dirty type tools), there are some major
limitations with pivot tables too. There's even some undesired behavior
of pivot tables that I have seen too, hence why I tend to use either
formulas or VBA codes instead of pivot tables to do a lot of it's
functions.

If you plan on creating your custom sort code, you many want to think
about the following:

First, if you ever had a course on programming dealing with sorting,
there's basically 3 different methods and the one method that stood out as
working most efficient when dealing with larger lists is the merge sort
method. So you will want to set up code to use the merge sort method (For
smaller lists, not going to make that much of a difference time wise
anyhow, so no sense on using either of the other 2 sort methods).

Next, you may want to use collections and/or arrays to help in that
process as a way of being able to store at least references to those
different rows of data. Of course, if you use the key part of collection
objects, the sorting can be done automatically as each item within a
collection must have a different key value. To do the multiple layers of
sorts, you will need to use class modules to create a such sort feature so
as you can create new sort objects within sort objects to create you
different layers of sort orders.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"greg" wrote in message
...
thanks,
So it is not possible or better to use a pivot table?


"Ronald R. Dodge, Jr." wrote in message
...
When sorting within VBA, you can do all 8 columns, though a bit tricky
as it won't be all done in one go. The way to do it is to first have
7th field as the Key1 and 8th field as Key2. Next round of sort, have
4th field as Key1, 5th field as Key2, and 6th field as Key3, then on the
last sort, have 1st field as Key1, 2nd field as Key2, and 3rd field as
Key3.

Yes, you are in a sense going backwards, but it's about the only way to
be able to do it without having to create your own custom code to handle
it.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"greg" wrote in message
...
Hello,
I am writing a program that needs to get data. I will be combining a
bunch of tables into one table. It might have about 8 columns.
Programmatically I need to sort the columns. Not just a a single
column. But by all the columns. I looked at this manual sort soln:
http://support.microsoft.com/kb/268007

Is a pivot table the way to go?
Or just run a sort with lots of Key<num=<location
Range("A40:D48").Select
Selection.Sort Key1:=Range("C40"), Order1:=xlAscending,
Key2:=Range("B40" _
), Order2:=xlAscending, Key3:=Range("A40"), Key3:=Range("D40"),
Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
thanks for any input