sorting columns

[greg]

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("A4048").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

 

[Ronald R. Dodge, Jr.]

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]

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

 

[Dave Peterson]

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.

 

[Ronald R. Dodge, Jr.]

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]

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